Hello All,
I am fairly new to VBA programming. I've made a userform with multiple comboBoxes. The value selected in the first ComboBox (combtype) is supposed to dictate what the second ComboBox (combloc) is populated with. I know I can do this by adding items individually, however, there are over 500 options (~150 per option in combtype) available in the second combobox (combloc) and I may need to add or adjust those values in the future. I have been unable to get my combloc to populate based on a range of values in the worksheet.
Here is the part of my userform in question: y.PNG
I have defined the ranges in the worksheet using Define name in the formulas tab.
When the userform initializes I want it to set those ranges to be called upon after combtype is selected. Here is the code:
Private Sub UserForm_Initialize()
Me.combloc.Enabled = False
'SINGLE, DOUBLE, FLAT, and OTHER are the options in the first comboBox (combtype)'
Dim rngSINGLE As Range
Dim rngDOUBLE As Range
Dim rngFLAT As Range
Dim rngOTHER As Range
Dim wk As Worksheet
Set wk = Worksheets("Inventory")
For Each rngSINGLE In wk.Range("SINGLEList")
Next rngSINGLE
For Each rngDOUBLE In wk.Range("DOUBLEList")
Next rngDOUBLE
For Each rngFLAT In wk.Range("FLATList")
Next rngFLAT
For Each rngOTHER In wk.Range("OTHERList")
Next rngOTHER
End Sub
When the option is selected in combtype here is what happens:
Private Sub combtype_change()
'These are to prevent the combloc from adding duplicate items if the combtype is changed'
Me.combloc.Clear
Me.combloc.Enabled = True
If Me.combtype.value = "SINGLE" Then
'Here is where the object required error occurs'
Me.combloc.AddItem rngSINGLE.value
ElseIf Me.combtype.value = "DOUBLE" Then
Me.combloc.AddItem rngDOUBLE.value
ElseIf Me.combtype.value = "FLAT" Then
Me.combloc.AddItem rngFLAT.value
ElseIf Me.combtype.value = "OTHER" Then
Me.combloc.AddItem rngOTHER.value
Else
Me.combloc.Enabled = False
End If
End Sub
I feel like I am close but would appreciate some guidance. Most other threads suggest just populating the comboboxes when the form initializes but that is not possible on my form until combtype is chosen.
Thanks in advance.
Bookmarks