ActiveX combobox click triggers unwanted another comboxbox click event

**aprildu** · 12-09-2015, 09:48 PM

I have two (or 3) comboboxes with one controlling the listfillrange of another. It works fine except when I add a line to capture the value of the combobox on a sheet. After the recording the value line, it jumps to the first combobox click event that I don't want:


Private Sub cbo1_click()
Set list2 = New Collection
Me.cbo2.Clear
    With sheet1
        lr = .Cells(Rows.Count, "a").End(xlUp).Row
        On Error Resume Next
        For i = 2 To lr
            If .Cells(i, "m") = Me.cbo1.Value Then
               list2.Add .Cells(i, "n"), CStr(.Cells(i, "n").Value)
            End If
        Next i
        On Error GoTo 0
        
        For Each myValue In list2
            Me.cbo2.AddItem myValue
        Next
    End With
sheet2.Range("A1") = Me.cbo1.value
End Sub

Private Sub cbo2_click()
Set list3 = New Collection
Me.cbo3.Clear
    With sheet1
        lr = .Cells(Rows.Count, "a").End(xlUp).Row
        On Error Resume Next
        For i = 2 To lr
            If .Cells(i, "n") = Me.cbo2.Value Then
               list3.Add .Cells(i, "b"), CStr(.Cells(i, "b").Value)
            End If
        Next i
        On Error GoTo 0
        
        For Each myValue In list3
            Me.cbo3.AddItem myValue
        Next
    End With
sheet2.Range("B1") = Me.cbo2.value
End Sub

When I click combobox2, it triggers the combobox1 event and wipes out the selection in the combobox2 after the line: sheet2.range("b2")=me.cbo2.value. How can I stop this from happening? I tried enableEvents, combobox.list,etc. to no avail. Many Thanks in advance!

**gmr4evr1** · 12-10-2015, 12:14 AM

I think there is a typo in your description or your code...
One says Sheet2.Range("b2")=me.cbo2.value
The other says Sheet2.Range("B1") = Me.cbo2.Value
Not that either one is the problem.
Not seeing anything that would cause ComboBox1 to trigger when clicking/changing ComboBox2, unless 1 is dependent on 2.

**AlphaFrog** · 12-10-2015, 01:50 AM

sheet2.Range("A1") = Me.cbo1.value

If cbo1 is populated by its .ListFillRange property and that list-range includes cell Sheet2.Range("A1") , then writing to a cell in its list-range will trigger cbo1 to re-populate and in turn trigger it's _Click event.

**aprildu** · 12-10-2015, 05:40 PM

AlhpaFrog: Yes cbo1 listfillrange is set in its property to a namerange((it's on sheet1). It doesn't include cell sheet2.range("A1"). Somehow it still triggers cbo1 click...I tried enableEvents but it didn't work

Tom: cbo2 is dependent on cbo1, when cbo1 equals certain value cbo2 populates with corresponding values.

**AlphaFrog** · 12-10-2015, 09:52 PM

Originally Posted by aprildu

AlhpaFrog: Yes cbo1 listfillrange is set in its property to a namerange but it doesn't include cell sheet2.range("A1") (it's on sheet3). Somehow it still triggers cbo1 click...I tried enableEvents but it didn't work

Is the named range for the List on Sheet2 as well? If yes, then as a test, write the cbo values to a different sheet.

You are correct in that EnableEvents has no effect on controls.

**aprildu** · 12-11-2015, 12:34 PM

I just found out that I made an error in my original code. The name range for cbo1 is on sheet1 and cbo2 added items are also on sheet1. But I typed in sheet5 instead (in my original file that didn't work not the sample code above). Still it's strange why it would trigger the cbo1 click event. Thanks AlphaFrog you are always a lifesaver!

**aprildu** · 12-11-2015, 01:44 PM

It's happening again. I tried the code in a clean workbook and it doesn't trigger the cbo1. But it just happens in my original workbook even when I copied the code into it. The combo values are in a totally blank sheet. What could be the reasons?

**gmr4evr1** · 12-11-2015, 01:54 PM

Are you using any RANDOM (RAND) formulas anywhere? Or, do you have a Worksheet_Change event code that you are using?

**aprildu** · 12-11-2015, 04:47 PM

Originally Posted by gmr4evr1

Are you using any RANDOM (RAND) formulas anywhere? Or, do you have a Worksheet_Change event code that you are using?

No there is no rand formula anywhere. I think it's because the cbo1 listfillrange is set in the same sheet as the items that are added to cbo2. Hopefully this time won't happen again

Otherwise I want to pull my hair out

**gmr4evr1** · 12-11-2015, 04:49 PM

Been there, now I'm bald!

**aprildu** · 12-11-2015, 09:13 PM

OK I am going bald too. I changed the cbo1 listfillrange to a dynamic range (offset....) and it is happening again. Is this the reason and why?

**gmr4evr1** · 12-12-2015, 09:09 AM

I'm am not familiar enough with coding a ComboBox in the manner that you have, so any info/suggestions I provide might be level 1 rookie stuff that makes no sense. Such as....List 1 and 2 are being set to = New Collection. Could this be causing the problem as they are both set to the same thing so one triggers the other?

**aprildu** · 12-12-2015, 09:21 AM

I think it's the dynamic range for cbo1 1 listfillrange that's triggering cbo1 click event. I tried it in a clean book (to eliminate other possible errors). It worked fine with a static name range until I changed to dynamic range. Don't know why but excel does 'strange things' once in awhile anyways. I resorted to populate cbo1 the same way (collection) as cbo2 and cbo3. It's working now finger crossed. And I am not touching/changing this block anymore lol.

**gmr4evr1** · 12-12-2015, 10:08 AM

lol, good luck. This might be a good reason for me to stick to naming the range in the Row Source of the properties window. keeps me from having to figure out the code I need (and the issues that go along with it).

ActiveX combobox click triggers unwanted another comboxbox click event

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

ActiveX combobox click triggers unwanted another comboxbox click event

Re: ActiveX combobox click triggers unwanted another comboxbox click event

Re: ActiveX combobox click triggers unwanted another comboxbox click event

Re: ActiveX combobox click triggers unwanted another comboxbox click event

Re: ActiveX combobox click triggers unwanted another comboxbox click event

Re: ActiveX combobox click triggers unwanted another comboxbox click event

Re: ActiveX combobox click triggers unwanted another comboxbox click event

Re: ActiveX combobox click triggers unwanted another comboxbox click event

Re: ActiveX combobox click triggers unwanted another comboxbox click event

Re: ActiveX combobox click triggers unwanted another comboxbox click event

Re: ActiveX combobox click triggers unwanted another comboxbox click event

Re: ActiveX combobox click triggers unwanted another comboxbox click event

Re: ActiveX combobox click triggers unwanted another comboxbox click event

Re: ActiveX combobox click triggers unwanted another comboxbox click event

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Checking ActiveX checkbox triggers another event.

Click v double click event code

[SOLVED] I need a simple code for a click event using a UserForm ComboBox

Prevent ListBox Click Event Until Mouse Click

[SOLVED] Combobox - can't get click event to run for first item in the list

[SOLVED] userform label double-click goes to click event

Re: Combobox Click event triggered when copying worksheet

Bookmarks

Bookmarks

Posting Permissions