talha_ansari
New Member
- Joined
- Jul 23, 2021
- Messages
- 1
- Office Version
- 2019
- 2016
- Platform
- 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.
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.