3 Dependent Combo Box

[Saru]

I have three dependent combo box
I am populating first 'cboMainCat' on form activation. The two remaining (cboCat & cboSubCat) i am trying to populate on change event.
second one works fine but the third i am having trouble seeing what i am doing i can seem to populate the values.

First:
Private Sub UserForm_Activate()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("DropDowns")
Dim i As Integer

Me.cboMainCat.Clear
For i = 2 To sh.Range("A" & Application.Rows.Count).End(xlUp).Row ' becoz 1 is the header
If sh.Range("A" & i).Value = "Main_Cat" Then
Me.cboMainCat.AddItem sh.Range("B" & i)
End If
Next i
End Sub

Second:
Private Sub cboMainCat_Change()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("DropDowns")
Dim i As Integer

Me.cboCat.Clear
For i = 2 To sh.Range("A" & Application.Rows.Count).End(xlUp).Row ' becoz 1 is the header
If sh.Range("A" & i).Value = "Cat" Then
If sh.Range("C" & i).Value = Me.cboMainCat.Value Then
Me.cboCat.AddItem sh.Range("B" & i)
End If
End If
Next i
End Sub

Third:
Private Sub cboCat_Change()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("DropDowns")
Dim i As Integer

Me.cboSubCat.Clear
For i = 2 To sh.Range("A" & Application.Rows.Count).End(xlUp).Row ' becoz 1 is the header
If sh.Range("A" & i).Value = "Sub Category" Then
If sh.Range("C" & i).Value = Me.cboCat.Value Then
Me.cboSubCat.AddItem sh.Range("B" & i)
End If
End If
Next i
End Sub

My Data in an excel sheet:

Drop-Downs	Items	Parent DropDown
Main_Cat	House
Main_Cat	Food
Main_Cat	Vehicle
Main_Cat	Entertainment
Main_Cat	Personal
Main_Cat	Medical
Main_Cat	Dental
Main_Cat	Vision
Cat	Utilities	House
Cat	Household_Items	House
Cat	Maintenance	House
Cat	Payment	House
Cat	Insurace	House
Cat	Taxes	House
Cat	Misc	House
Cat	Groceries	Food
Cat	Dining	Food
Cat	Payment	Vehicle
Cat	Fuel	Vehicle
Cat	Insurance	Vehicle
Cat	Tolls	Vehicle
Cat	Taxes	Vehicle
Cat	Roadside Assistance	Vehicle
Cat	Membership	Vehicle
Cat	RMV	Vehicle
Cat	Misc	Vehicle
Cat	Road Trip	Entertainment
Cat	Vacation	Entertainment
Cat	Local Activities	Entertainment
Cat	Kevin	Personal
Cat	Saru	Personal
Cat	Pet	Personal
Cat	Income Tax	Personal
Cat	Kevin	Medical
Cat	Saru	Medical
Cat	Kevin	Dental
Cat	Saru	Dental
Cat	Kevin	Vision
Cat	Saru	Vision
Sub Category	Electric	Utilities
Sub Category	Water	Utilities
Sub Category	Trash	Utilities
Sub Category	Sewer	Utilities
Sub Category	Fuel	Utilities
Sub Category	Other	Utilities
Sub Category	Laundry	Household_Items
Sub Category	Appliances	Household_Items

 

[DanteAmor]

Try this

Dim sh As Worksheet     'at the beginning of all the code

Private Sub cboMainCat_Change()
  Dim i As Long
  cboCat.Clear
  cboSubCat.Clear
  For i = 2 To sh.Range("A" & Application.Rows.Count).End(xlUp).Row
    If sh.Range("A" & i).Value = "Cat" And sh.Range("C" & i).Value = cboMainCat.Value Then
      cboCat.AddItem sh.Range("B" & i)
    End If
  Next i
End Sub

Private Sub cboCat_Change()
  Dim i As Long
  cboSubCat.Clear
  For i = 2 To sh.Range("A" & Application.Rows.Count).End(xlUp).Row
    If sh.Range("A" & i).Value = "Sub Category" And sh.Range("C" & i).Value = cboCat.Value Then
      cboSubCat.AddItem sh.Range("B" & i)
    End If
  Next i
End Sub

Private Sub UserForm_Activate()
  Set sh = ThisWorkbook.Sheets("DropDowns")
  Dim i As Long
  cboMainCat.Clear
  For i = 2 To sh.Range("A" & Rows.Count).End(xlUp).Row ' becoz 1 is the header
    If sh.Range("A" & i).Value = "Main_Cat" Then
      cboMainCat.AddItem sh.Range("B" & i)
    End If
  Next i
End Sub

 

[Saru]

Try this

Dim sh As Worksheet     'at the beginning of all the code

Private Sub cboMainCat_Change()
  Dim i As Long
  cboCat.Clear
  cboSubCat.Clear
  For i = 2 To sh.Range("A" & Application.Rows.Count).End(xlUp).Row
    If sh.Range("A" & i).Value = "Cat" And sh.Range("C" & i).Value = cboMainCat.Value Then
      cboCat.AddItem sh.Range("B" & i)
    End If
  Next i
End Sub

Private Sub cboCat_Change()
  Dim i As Long
  cboSubCat.Clear
  For i = 2 To sh.Range("A" & Application.Rows.Count).End(xlUp).Row
    If sh.Range("A" & i).Value = "Sub Category" And sh.Range("C" & i).Value = cboCat.Value Then
      cboSubCat.AddItem sh.Range("B" & i)
    End If
  Next i
End Sub

Private Sub UserForm_Activate()
  Set sh = ThisWorkbook.Sheets("DropDowns")
  Dim i As Long
  cboMainCat.Clear
  For i = 2 To sh.Range("A" & Rows.Count).End(xlUp).Row ' becoz 1 is the header
    If sh.Range("A" & i).Value = "Main_Cat" Then
      cboMainCat.AddItem sh.Range("B" & i)
    End If
  Next i
End Sub

 

[Saru]

Hi Dante,

I made changes based on your feedback. I am still not having any luck with the 3rd combo box. This is what i have for vba code;

Option Explicit
Public enableevents As Boolean
Dim sh As Worksheet

Private Sub UserForm_Activate()
Set sh = ThisWorkbook.Sheets("DropDowns")
Dim i As Long

cboMainCat.Clear

For i = 2 To sh.Range("A" & Application.Rows.Count).End(xlUp).Row ' becoz 1 is the header
If sh.Range("A" & i).Value = "Main_Cat" Then
cboMainCat.AddItem sh.Range("B" & i)
End If
Next i
End Sub

Private Sub cboMainCat_Change()
Dim i As Long

cboCat.Clear
cboSubCat.Clear

For i = 2 To sh.Range("A" & Application.Rows.Count).End(xlUp).Row ' becoz 1 is the header
If sh.Range("A" & i).Value = "Cat" And sh.Range("C" & i).Value = cboMainCat.Value Then
cboCat.AddItem sh.Range("B" & i)
End If
Next i
End Sub

Private Sub cboCat_Change()
Dim i As Long

cboSubCat.Clear

For i = 2 To sh.Range("A" & Application.Rows.Count).End(xlUp).Row ' becoz 1 is the header
If sh.Range("A" & i).Value = "Sub Category" And sh.Range("C" & i).Value = cboCat.Value Then
cboSubCat.AddItem sh.Range("B" & i)
End If
Next i
End Sub

 

[DanteAmor]

Did you test my code with your examples before making changes?

 

[Saru]

I updated my code with your code and it didn't work.

 

[DanteAmor]

I updated my code with your code

My code replaces all your code.
Or in what way does it not work?
According to your data that you put in your initial post, my code works well.

Test my macro with this data structure.

varios 15may2020.xlsm
	A	B	C
1	Drop-Downs	Items	Parent DropDown
2	Main_Cat	House
3	Main_Cat	Food
4	Main_Cat	Vehicle
5	Main_Cat	Entertainment
6	Main_Cat	Personal
7	Main_Cat	Medical
8	Main_Cat	Dental
9	Main_Cat	Vision
10	Cat	Utilities	House
11	Cat	Household_Items	House
12	Cat	Maintenance	House
13	Cat	Payment	House
14	Cat	Insurace	House
15	Cat	Taxes	House
16	Cat	Misc	House
17	Cat	Groceries	Food
18	Cat	Dining	Food
19	Cat	Payment	Vehicle
20	Cat	Fuel	Vehicle
21	Cat	Insurance	Vehicle
22	Cat	Tolls	Vehicle
23	Cat	Taxes	Vehicle
24	Cat	Roadside Assistance	Vehicle
25	Cat	Membership	Vehicle
26	Cat	RMV	Vehicle
27	Cat	Misc	Vehicle
28	Cat	Road Trip	Entertainment
29	Cat	Vacation	Entertainment
30	Cat	Local Activities	Entertainment
31	Cat	Kevin	Personal
32	Cat	Saru	Personal
33	Cat	Pet	Personal
34	Cat	Income Tax	Personal
35	Cat	Kevin	Medical
36	Cat	Saru	Medical
37	Cat	Kevin	Dental
38	Cat	Saru	Dental
39	Cat	Kevin	Vision
40	Cat	Saru	Vision
41	Sub Category	Electric	Utilities
42	Sub Category	Water	Utilities
43	Sub Category	Trash	Utilities
44	Sub Category	Sewer	Utilities
45	Sub Category	Fuel	Utilities
46	Sub Category	Other	Utilities
47	Sub Category	Laundry	Household_Items
48	Sub Category	Appliances	Household_Items
DropDowns

 

[Saru]

Hi Dante Amor, I created a new xlsm, used your code and the data. It still didn't work. I am not sure what else I can do other than sharing my file. Let me know i can attach my file. Thank you!

 

[DanteAmor]

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Saru]

Here is the link to the file;

Transfer - Dropbox

Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again!

www.dropbox.com