Call combobox_change function on exit.

**hubert_ii** · 12-22-2009, 08:08 AM

Hi!

I've got a problem. I'm using an activex combobox in excel. It display 4 posible choices, and one of them is "Other". When someone select "other" there is a textbox named "tb" that should be filled. Otherwise tb should be invisible. Combobox change() function looks like that:

Public Sub ComboBox7_Change()
If IsNull(Sheet1.tb) Then
    'nothing'
Else
    If ComboBox7.ListIndex = 0 Then
        Sheet1.tb.Visible = True
    Else
        Sheet1.tb.Visible = False
    End If
End If

End Sub

Apears when I try to close excel - there is a question if I want to save changes and when I answers "Yes" it - somehow - call combobox7_change() function. In that moment textbox "tb" doesn'y exist, so there is an error message

Compile error:

Method or data member not found

Could anyone help me, please? What I'm doing wrong?

**davesexcel** · 12-22-2009, 09:13 AM

Hi,
Do you have any workbook macros?

**hubert_ii** · 12-22-2009, 09:17 AM

Yeah, but I belive they shouldn't make diference. There is only Open() - initialisations of all comboboxes only.

Precisely:

Dim combo1
Dim combo2
Dim combo3
Dim combo4
Dim combo5
Dim combo6
Dim combo7
Dim combo8

If combo1 = 0 Then
    Arkusz1.ComboBox1.AddItem "rzszdrxcg", 0
    Arkusz1.ComboBox1.AddItem "tfufhgkjh", 1
    Arkusz1.ComboBox1.AddItem "i97yug", 2
    Arkusz1.ComboBox1.AddItem "890oy8978y", 3
    Arkusz1.ComboBox1.AddItem "sdjhksjhd", 4
    Arkusz1.ComboBox1.AddItem "skdgikjsd", 5
    Arkusz1.ComboBox1.Style = fmStyleDropDownList
    Arkusz1.ComboBox1.BoundColumn = 0
    Arkusz1.ComboBox1.ListIndex = 0
    combo1 = 1
End If

If combo2 = 0 Then
    Arkusz1.ComboBox2.AddItem "6y7uio", 0
    Arkusz1.ComboBox2.AddItem "er6dtucigyo", 1
    Arkusz1.ComboBox2.AddItem "auyk", 2
    Arkusz1.ComboBox2.AddItem "tfugvj", 3
    Arkusz1.ComboBox2.AddItem "ougyif˙", 4
    Arkusz1.ComboBox2.AddItem "5edtryc", 5
    Arkusz1.ComboBox2.AddItem "ydrtfuyi", 6
    Arkusz1.ComboBox2.AddItem "bmn,", 7
    Arkusz1.ComboBox2.Style = fmStyleDropDownList
    Arkusz1.ComboBox2.BoundColumn = 0
    Arkusz1.ComboBox2.ListIndex = 0
    combo2 = 1
End If

If combo3 = 0 Then

    Arkusz1.ComboBox3.AddItem "35t6yguj", 0
    Arkusz1.ComboBox3.AddItem "ouihkv", 1
    Arkusz1.ComboBox3.AddItem "rdtfyugvhi", 2
    Arkusz1.ComboBox3.AddItem "7u6gvj", 3
    Arkusz1.ComboBox3.AddItem "ty7u6yh˙", 4
    Arkusz1.ComboBox3.AddItem "sdhjhgj", 5
    Arkusz1.ComboBox3.AddItem "yuyguyhg", 6
    Arkusz1.ComboBox3.AddItem "uhikjh", 7
    Arkusz1.ComboBox3.AddItem "hgggg˙", 8
    Arkusz1.ComboBox3.AddItem "hgjhg", 9
    Arkusz1.ComboBox3.AddItem "nmb", 10
    Arkusz1.ComboBox3.Style = fmStyleDropDownList
    Arkusz1.ComboBox3.BoundColumn = 0
    Arkusz1.ComboBox3.ListIndex = 0
    combo3 = 1
End If

If combo4 = 0 Then
    Arkusz1.ComboBox4.AddItem "trcgfch", 0
    Arkusz1.ComboBox4.AddItem "hsdjh", 1
    Arkusz1.ComboBox4.AddItem "nnn", 2
    Arkusz1.ComboBox4.AddItem "ugugv", 3
    Arkusz1.ComboBox4.AddItem "iuyigb", 4
    Arkusz1.ComboBox4.AddItem "kljh", 5
    Arkusz1.ComboBox4.Style = fmStyleDropDownList
    Arkusz1.ComboBox4.BoundColumn = 0
    Arkusz1.ComboBox4.ListIndex = 0
    combo4 = 1
End If

If combo5 = 0 Then
    Arkusz1.ComboBox5.AddItem "ddd", 0
    Arkusz1.ComboBox5.AddItem "rrr", 1
    Arkusz1.ComboBox5.AddItem "ttt", 2
    Arkusz1.ComboBox5.AddItem "yyy", 3
    Arkusz1.ComboBox5.AddItem "uuu˙", 4
    Arkusz1.ComboBox5.AddItem "iii", 5
    Arkusz1.ComboBox5.AddItem "ooo", 6
    Arkusz1.ComboBox5.AddItem "ppp", 7
    Arkusz1.ComboBox5.Style = fmStyleDropDownList
    Arkusz1.ComboBox5.BoundColumn = 0
    Arkusz1.ComboBox5.ListIndex = 0
    combo5 = 1
End If

If combo6 = 0 Then

    Arkusz1.ComboBox6.AddItem "uuu", 0
    Arkusz1.ComboBox6.AddItem "iii", 1
    Arkusz1.ComboBox6.AddItem "jjj", 2
    Arkusz1.ComboBox6.AddItem "sss", 3
    Arkusz1.ComboBox6.AddItem "mmm˙", 4
    Arkusz1.ComboBox6.AddItem "nnn", 5
    Arkusz1.ComboBox6.AddItem "bbbb", 6
    Arkusz1.ComboBox6.AddItem "vvv", 7
    Arkusz1.ComboBox6.AddItem "ccc˙", 8
    Arkusz1.ComboBox6.AddItem "xxx", 9
    Arkusz1.ComboBox6.AddItem "zzz", 10
    Arkusz1.ComboBox6.Style = fmStyleDropDownList
    Arkusz1.ComboBox6.BoundColumn = 0
    Arkusz1.ComboBox6.ListIndex = 0
    combo6 = 1
End If

If combo7 = 0 Then

    Arkusz1.ComboBox7.AddItem "Other", 0
    Arkusz1.ComboBox7.AddItem "aa", 1
    Arkusz1.ComboBox7.AddItem "bb", 2
    Arkusz1.ComboBox7.AddItem "cc", 3
    Arkusz1.ComboBox7.Style = fmStyleDropDownList
    Arkusz1.ComboBox7.BoundColumn = 0
    Arkusz1.ComboBox7.ListIndex = 0
    
    combo7 = 1

End If

If combo8 = 0 Then

    Arkusz1.ComboBox8.AddItem "y", 0
    Arkusz1.ComboBox8.AddItem "n", 1
    Arkusz1.ComboBox8.Style = fmStyleDropDownList
    Arkusz1.ComboBox8.BoundColumn = 0
    Arkusz1.ComboBox8.ListIndex = 0
    combo8 = 1

End If

End Sub

**davesexcel** · 12-22-2009, 09:23 AM

You may have to supply a sample workbook that shows the error.

**hubert_ii** · 12-22-2009, 09:34 AM

Ok - i've send you an email.

**romperstomper** · 12-22-2009, 11:32 AM

Is your combobox linked directly to cells on a worksheet?

**hubert_ii** · 12-23-2009, 02:54 AM

No - it's created in a workbook section as you can see over that post. It "comunicates" only whith that "TB" textbox in a change() function.

**Andy Pope** · 12-23-2009, 05:53 AM

The attached works for me using your code.

One difference I noticed is that for me the Change event is a private sub.
Yours is Public, did you create that yourself in a standard code model?

**hubert_ii** · 12-23-2009, 07:48 AM

Hi!

Unfortunately I experience same problem with that project, that you have attached. When I open the file, change something and then i try to close file, there very same alert appears...

Below you could find short screencast.
http://www.speedyshare.com/files/199...0170524844.avi

**Andy Pope** · 12-23-2009, 08:04 AM

so is the textbox named tb?

As already requested, post your example file.

**hubert_ii** · 12-23-2009, 08:16 AM

I've send it to you in a private message.

**Andy Pope** · 12-23-2009, 08:28 AM

Your file works as expected.

Open you file and go to the VBE. Try compiling the project.

Menu, debug > Compile project. Do you get error message?

**hubert_ii** · 12-23-2009, 08:40 AM

No - in fact I don't get any message. Compilation didn't solve the problem either...

**Andy Pope** · 12-23-2009, 08:59 AM

Hard to know what the problem is then as both test files work for me.
And for you the file does not report error when compiling but then stops reporting error on close.

**hubert_ii** · 12-23-2009, 09:05 AM

Ok - thanks for trying. It must be something with my environment then...
Probably I work it somehow out.

Thanks a lot and Marry Holly Days!

Call combobox_change function on exit.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Call combobox_change function on exit.

Re: Calling combobox_change function on exit.

Re: Calling combobox_change function on exit.

Re: Calling combobox_change function on exit.

Re: Calling combobox_change function on exit.

Re: Calling combobox_change function on exit.

Re: Calling combobox_change function on exit.

Re: Calling combobox_change function on exit.

Re: Calling combobox_change function on exit.

Re: Calling combobox_change function on exit.

Re: Calling combobox_change function on exit.

Re: Calling combobox_change function on exit.

Re: Calling combobox_change function on exit.

Re: Calling combobox_change function on exit.

Re: Calling combobox_change function on exit.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions