I have 2 ActiveX ComboBoxes on a Userform
ComboBox1 (cboSuppliersList) is the primary and ComboBox2 (cboSuppliersChems) is dependent on ComboBox1.
I am trying to automatically “Drop Down” ComboBox2 ( dependant dropdown list) when a selection has been made in ComboBox1.
What I have so far is this;
Have a Command button on active sheet that calls the UserForm (so not using an Initialise event)
On the UserForm I have pre-set ComboBox1 to a Named Range row source in an external workbook.
I am using Case statements for ComboBox2 (dependant dropdown list) that reference named ranges in the same external workbook.
So all of that works fine.
But run into problems when trying to enhance the existing code.
What I want to do is; when a selection has been made in ComboBox1, that ComboBox2 list automatically drops down (so not using the drop down arrow, just lazy!)
I have added these 2 lines to my working code, 2nd doesn’t operate without the 1st, but 2nd causes the error
It sort of part works but 2nd line is causing an error.
What I mean by “part work” is that ComboBox2 does auto drop down the list BUT then it errors out the Command Button_Click() Sub that shows the UserForm.
Is there a way to fix the code to work in way trying to do, or it just case of me NOT being so lazy!
Slimmed down code.
ComboBox1 (cboSuppliersList) is the primary and ComboBox2 (cboSuppliersChems) is dependent on ComboBox1.
I am trying to automatically “Drop Down” ComboBox2 ( dependant dropdown list) when a selection has been made in ComboBox1.
What I have so far is this;
Have a Command button on active sheet that calls the UserForm (so not using an Initialise event)
On the UserForm I have pre-set ComboBox1 to a Named Range row source in an external workbook.
I am using Case statements for ComboBox2 (dependant dropdown list) that reference named ranges in the same external workbook.
So all of that works fine.
But run into problems when trying to enhance the existing code.
What I want to do is; when a selection has been made in ComboBox1, that ComboBox2 list automatically drops down (so not using the drop down arrow, just lazy!)
I have added these 2 lines to my working code, 2nd doesn’t operate without the 1st, but 2nd causes the error
Rich (BB code):
cboSuppliersChems.SetFocus
cboSuppliersChems.DropDownWhat I mean by “part work” is that ComboBox2 does auto drop down the list BUT then it errors out the Command Button_Click() Sub that shows the UserForm.
Is there a way to fix the code to work in way trying to do, or it just case of me NOT being so lazy!
Slimmed down code.
VBA Code:
Option Explicit
Option Compare Text 'This makes all text case-insensitive'
'This is code in the UserForm module
Private Sub cboSuppliersList_Change()
Application.ScreenUpdating = False
Application.EnableEvents = False
If cboSuppliersList.Value = "" Then
cboSuppliersChems.Value = ""
End If
Select Case cboSuppliersList.Value
Case Is = "List1"
cboSuppliersChems.RowSource = "List1"
cboSuppliersChems.SetFocus 'On its own, no errors
cboSuppliersChems.DropDown 'Causes error
'More Case statements folow
End Select
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
