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.
it does work for starting items but not for further. the issue is inOption 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
as well as inApplication.WorksheetFunction.CountIf(sh.Range("B2", "B" & i), sh.Cells(i, 2)) = 1
when it try to get unique items for combobox list.Application.WorksheetFunction.CountIf(sh.Range("C2", "C" & i), sh.Cells(i, 3)) = 1
any solution please.
Comment: The query is also posted by me in MrExcel forum.
Bookmarks