Userform - Combo box dependent on the entry of another Combobox

[JustHooch]

I have been researching and have gotten close but not exactly what I need. Please help me code with the below in my first Userform.

Combobox #1 - Named "ComboBoxlLocation"
Combobox #2 - Named "ComboBoxCopy"
Combobox #3 - Named "ComboBoxMedia"
Combobox #4 - Named "ComboBoxPricing"

ComboBoxlLocation populates from the named range "Location". It contains 3 options: AUL, DLR, WDW

There are named ranges set-up for comboboxes 2-4 all set-up the same.

This example is for ComboBoxCopy. The "Name" column is the named range called "Copy".

[TABLE="width: 300"]
<tbody>[TR]
[TD]Loc (in column C)[/TD]
[TD]Name (in column D)[/TD]
[/TR]
[TR]
[TD]WDW
[/TD]
[TD]Heather[/TD]
[/TR]
[TR]
[TD]WDW[/TD]
[TD]Mark[/TD]
[/TR]
[TR]
[TD]WDW[/TD]
[TD]Matt[/TD]
[/TR]
[TR]
[TD]DLR[/TD]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]DLR[/TD]
[TD]Rick[/TD]
[/TR]
[TR]
[TD]DLR[/TD]
[TD]Shavana[/TD]
[/TR]
[TR]
[TD]AUL[/TD]
[TD]Paul[/TD]
[/TR]
[TR]
[TD]AUL[/TD]
[TD]Katie[/TD]
[/TR]
</tbody>[/TABLE]

After a selection is made in ComboBoxlLocation, ComboBoxCopy should match that selection to column C then display the correct values from the named range in column D.

• If ComboBoxlLocation =WDW then show Heather, Mark & Matt
• If ComboBoxlLocation =DLR then show Bob, Rick & Shavana
• If ComboBoxlLocation =AUL then show Paul & Katie

This same process will repeat for "ComboBoxMedia" & Combobox but with a different Named ranges for each. I should be able to update/modify the code from ComboBoxCopy to work for the other 2.

 

[Fluff]

How about

Option Explicit
Private Dic As Object

Private Sub ComboBoxlLocation_Click()
   Me.ComboBoxCopy.Clear
   Me.ComboBoxCopy.List = Dic(Me.ComboBoxlLocation.Value).keys
End Sub

Private Sub UserForm_Initialize()
   Dim v1 As String, v2 As String
   Dim Cl As Range
   Set Dic = CreateObject("scripting.dictionary")
   Dic.CompareMode = vbTextCompare
   With Sheets("[COLOR=#ff0000]pcode[/COLOR]")
      For Each Cl In .Range("C2", .Range("C" & Rows.Count).End(xlUp))
         v1 = Cl.Value: v2 = Cl.Offset(, 1).Value
         If Not Dic.exists(v1) Then
            Dic.Add v1, CreateObject("scripting.dictionary")
            Dic(v1).Add v2, Nothing
         ElseIf Not Dic(v1).exists(v2) Then
            Dic(v1).Add v2, Nothing
         End If
      Next Cl
   End With
   Me.ComboBoxlLocation.List = Dic.keys
End Sub

The first 2 lines need to be at the very top of the module, before any other code.
Change sheet name in red to suit

 

[JustHooch]

I am getting a "Run-time error '91' error when trying to run the code. Object variable or With Block variable not set.

Here is the code I have, the issue seems to be with the lines in red

Option Explicit
Private Dic As Object


Private Sub UserForm_Initialize()
   'Empty Offer ID Text box and Set the Cursor
   TextBoxOfferName.Value = ""
   TextBoxOfferName.SetFocus
   
   'Empty all other text box fields
   TextBoxMarketingID.Value = ""
   TextBoxPortfolioNmbr.Value = ""
End Sub


Private Sub ComboBoxlLocation_Click()
   Me.ComboBoxCopy.Clear
   Me.ComboBoxCopy.List = Dic(Me.ComboBoxlLocation.Value).keys
End Sub


Private Sub Lodging_Initialize()
   Dim v1 As String, v2 As String
   Dim Cl As Range
   Set Dic = CreateObject("scripting.dictionary")
   Dic.CompareMode = vbTextCompare
   With Sheets("Dropdown Values")
      For Each Cl In .Range("C2", .Range("C" & Rows.Count).End(xlUp))
         v1 = Cl.Value: v2 = Cl.Offset(, 1).Value
         If Not Dic.exists(v1) Then
            Dic.Add v1, CreateObject("scripting.dictionary")
            Dic(v1).Add v2, Nothing
         ElseIf Not Dic(v1).exists(v2) Then
            Dic(v1).Add v2, Nothing
         End If
      Next Cl
   End With
   Me.ComboBoxlLocation.List = Dic.keys
End Sub

 

[JustHooch]

I figured it out. Thank you very much