Excel VBA: Multi-level dependant lists ComboBoxes. some problem in extracting unique lists

talha_ansari

New Member
Joined
Jul 23, 2021
Messages
1
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
i have a file containing the sample userform of 3 sample combo boxes every next combobox is dependant to the previous.

there is a simple vba behind it, which i think should work correctly.

Option Explicit

Private Sub UserForm_Initialize()
' set worksheet
Dim sh As Worksheet
Set sh = Sheets("Clients")
'declare variable
Dim i As Long
For i = 2 To Application.WorksheetFunction.CountA(sh.Cells(1, 1).EntireColumn)
If Application.WorksheetFunction.CountIf(sh.Range("A2", "A" & i), sh.Cells(i, 1)) = 1 Then
Me.ComboBox1.AddItem sh.Cells(i, 1)
End If
Next i
End Sub
Private Sub ComboBox1_Change()
Me.ComboBox2.Clear

' set worksheet
Dim sh As Worksheet
Set sh = Sheets("Clients")
'declare variable
Dim i As Long
For i = 2 To Application.WorksheetFunction.CountA(sh.Cells(1, 1).EntireColumn)
If sh.Cells(i, 1) = Me.ComboBox1.Value And _
Application.WorksheetFunction.CountIf(sh.Range("B2", "B" & i), sh.Cells(i, 2)) = 1 Then

Me.ComboBox2.AddItem sh.Cells(i, 2)

End If
Next i


End Sub

Private Sub ComboBox2_Change()
Me.ComboBox3.Clear

' set worksheet
Dim sh As Worksheet
Set sh = Sheets("Clients")
'declare variable
Dim i As Long
For i = 2 To sh.Range("A10000").End(xlUp).Row
If sh.Cells(i, 1) = Me.ComboBox1.Value And sh.Cells(i, 2) = Me.ComboBox2.Value And _
Application.WorksheetFunction.CountIf(sh.Range("C2", "C" & i), sh.Cells(i, 3)) = 1 Then

Me.ComboBox3.AddItem sh.Cells(i, 3)

End If
Next i

End Sub


it does work for starting items but not for further. the issue is in
Application.WorksheetFunction.CountIf(sh.Range("B2", "B" & i), sh.Cells(i, 2)) = 1

as well as in
Application.WorksheetFunction.CountIf(sh.Range("C2", "C" & i), sh.Cells(i, 3)) = 1

when it try to get unique items for combobox list.

any solution please.


1627085628024.png


1627085685589.png
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,567
Office Version
  1. 365
Platform
  1. Windows
I tried added it on the end of the new code but get this error:

I want to be able to filter the table based on the selection from a ComboBox individually like "Source" combobox only

or with a combination of multiple selections from the ComboBoxes like "Source", "Name of Project", "Status" and "Client" etc.
That's a different problem, you need to start a new thread.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,567
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help & thanks for the feedback.:)
 

Forum statistics

Threads
1,148,368
Messages
5,746,287
Members
424,006
Latest member
Metal_warrior

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top