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?
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?
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.
ASKER
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:
VBAProject.mod_Update.upda
VBAProject.Sheet2.cbx_regi
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(ws
cbx.ListFillRange = "lists!b2:b" & (getVariable("number of reps") + 1)
If selectFirst Then cbx.ListIndex = 0
Err_ShapeDoesNotExist:
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?
The call stack shows:
VBAProject.mod_Update.upda
VBAProject.Sheet2.cbx_regi
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(ws
cbx.ListFillRange = "lists!b2:b" & (getVariable("number of reps") + 1)
If selectFirst Then cbx.ListIndex = 0
Err_ShapeDoesNotExist:
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.