Link to home
Start Free TrialLog in
Avatar of iderskater

asked on

dropdown triggering Change event of another drop down

Hey everyone...

I'm working with VBA within Excel and I have a worksheet with two dropdowns/combo boxes on it.  One lists the sales regions (cbx_region) and another that gets populated with sales reps depending on which region you select (cbx_rep).  So I have a Sub call in the cbx_region Change event that updates the list in cbx_rep, and selects the first item in the list.  My problem is that it works fine when I change the sales region, but for some reason the cbx_region_Change() event gets called when I select a sales rep from the cbx_rep combobox.  And this is screwing up my selection because no matter what I choose from cbx_rep it selects the first rep in the list.  Any clues to why this would be?
Avatar of vinnyd79

Can't you use the cbx_region 'Click' event instead of 'Change'?
Avatar of GrahamSkan
Put a breakpoint (F8) on the first executable line of the procedure that gets called unexpectedly. When it breaks on the line, you can do View/Call Stack (or Ctrl+L) to see what called it.
Avatar of iderskater


I've done the step by step and I can see that the Region combobox is calling it, even though I click on the Rep combobox.

The call stack shows:


The code is:

Private Sub cbx_region_Click()
    Call updateRepList(Me.Name, True)
End Sub

Private Sub cbx_rep_Click()

End Sub

Public Sub updateRepList(wsName As String, Optional selectFirst As Boolean)
On Error GoTo Err_ShapeDoesNotExist
    Dim cbx As ComboBox
    Set cbx = ThisWorkbook.Worksheets(wsName).Shapes("cbx_Rep").DrawingObject.Object
    cbx.ListFillRange = "lists!b2:b" & (getVariable("number of reps") + 1)
    If selectFirst Then cbx.ListIndex = 0
End Sub

This wouldn't be a problem if I didn't have the "selectFirst" part.  But I want it to select the first Rep from the Rep list automatically when you select a new region.

Any ideas?
Avatar of GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial