Dependent Combobox to Reference Worksheet Range Using Offset

[reberryjr]

I'm at a loss as to how I can populate particular values in a combobox based on the User's selection in a different combobox. I have 4 comboboxes (cobo_Exec, cobo_Leader, cobo_Mgr, cobo_Assoc). I have a worksheet called Names, where there are 4 columns that correspond to each combobox. What I would like to happen, is if the User selects "Tom G." from cobo_Exec, then only those people that report to Tom G. appear in the cobo_Leader list.

Everything I see on the web is using Select Case with Arrays, but I have too many variables to do this with, and values can change as people move around the organization.

I was trying this code, but I get an error of "Expected function or variable" at the c in red font.

Private Sub cobo_Leader_Change()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim m As Workbook
Dim mN As Worksheet
Dim i As Integer
Dim r As Range, c As Range
Dim mNLR As Long

Set m = ThisWorkbook
Set mN = m.Sheets("Names")

mNLR = mN.Range("A" & Rows.Count).End(xlUp).Row

With Me.cobo_Mgr
    Set r = mN.Range("C2:C" & mNLR)
    For Each c In r
        If [COLOR=rgb(184, 49, 47)]c[/COLOR] = Me.cobo_Leader.value Then Me.cobo_Mgr.ListIndex = .AddItem.Offset(, -1)
    Next c
End With
         
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Akuini]

Here's an example of 3 dependent combobox in a userform:

3 combobox, dependent, userform, simple, dictionary, table - 1

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

 

[reberryjr]

Here's an example of 3 dependent combobox in a userform:

3 combobox, dependent, userform, simple, dictionary, table - 1

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

@Akuini thank you for replying. Unfortunately, that site is blocked by my work laptop so I'm unable to see the file.

 

[Akuini]

@Akuini thank you for replying. Unfortunately, that site is blocked by my work laptop so I'm unable to see the file.

Ok.
There are 3 combobox, i.e combobox1, combobox2, & combobox3 in the Userform.
The code:

Dim vList
Dim d As Object

'====YOU MAY NEED TO ADJUST THE CODE IN THIS PART:====
'sheet's name where the list  is located.
Private Const sList As String = "Sheet1"
'Table name where the list  is located
Private Const tbl As String = "Table1"


Private Sub UserForm_Initialize()
vList = Sheets(sList).ListObjects("Table1").DataBodyRange.Columns("A:C")
    Set d = CreateObject("scripting.dictionary")
    d.CompareMode = vbTextCompare
End Sub

Private Sub ComboBox1_Change()
       ComboBox2.Value = ""
       ComboBox3.Value = ""

End Sub

Private Sub ComboBox2_Change()
       ComboBox3.Value = ""

End Sub

Private Sub ComboBox1_DropButtonClick()
Dim i As Long
    d.RemoveAll
    For i = LBound(vList) To UBound(vList)
          d(vList(i, 1)) = Empty
    Next
       ComboBox1.List = d.keys
End Sub


Private Sub ComboBox2_DropButtonClick()
Dim i As Long
    d.RemoveAll
    For i = LBound(vList) To UBound(vList)
    If UCase(vList(i, 1)) = UCase(ComboBox1.Value) Then d(vList(i, 2)) = Empty
    Next
       ComboBox2.List = d.keys
End Sub

Private Sub ComboBox3_DropButtonClick()
Dim i As Long
    d.RemoveAll
    For i = LBound(vList) To UBound(vList)
        If UCase(vList(i, 1)) = UCase(ComboBox1.Value) And UCase(vList(i, 2)) = UCase(ComboBox2.Value) Then
            d(vList(i, 3)) = Empty
        End If
    Next
       ComboBox3.List = d.keys
End Sub

Data is in "Table1":

3 combobox, dependent, userform, simple, dictionary, table - 1.xlsm
	A	B	C
1	STATE	CITY	REP
2	Arizona	Marana	Cory
3	Arizona	Marana	Jayce
4	Arizona	Tucson	Mario
5	Arizona	Tucson	Mark
6	Arizona	Tucson	Patrick
7	Arkansas	Conway	Bode
8	Arkansas	Conway	Drew
9	Arkansas	Conway	Quincy
10	Arkansas	Jonesboro	Lochlan
11	Arkansas	Jonesboro	Makai
12	Arkansas	Jonesboro	Ruben
13	Arkansas	Springdale	Truman
14	Arkansas	Springdale	Zaiden
15	Arkansas	Springdale	Zayn
16	California	Chula Vista	Zachariah
17	California	Chula Vista	Zander
18	California	Santa Clara	Dominik
Sheet1

 

[reberryjr]

Dim vList
Dim d As Object
'====YOU MAY NEED TO ADJUST THE CODE IN THIS PART:====
'sheet's name where the list  is located.
Private Const sList As String = "Sheet1"
'Table name where the list  is located
Private Const tbl As String = "Table1"

Should this section go in a regular module?

 

[Akuini]

Should this section go in a regular module?

No, it goes to Userform1 module.