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?
Visual Basic Classic

Avatar of undefined
Last Comment

8/22/2022 - Mon

Can't you use the cbx_region 'Click' event instead of 'Change'?

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.

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?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question