Clear Dependent ComboBox

[VJOSHI]

Hi,

I have created a userform with 3 dependant combobox and they work fine except when user changes value of the box. They pop up with error "Invalid Input Value”.

"TeamComboBox" is 1st selection Box that determines value for "ThreadComboBox" and similarly "ThreadComboBox" determine value for "SubThreadComboBox". Not sure what I am missing to get values blank/reset if user changes the initial value of any of combobox.Need combobox contents to go blank or reset once value changes.

Below is the code I am using for same.</SPAN>

 Dim Worksht As Worksheet
Dim Worksht2 As Worksheet
Dim CellAddr As Range
Set Worksht = Worksheets("Threadatabase")
Set Worksht2 = Worksheets("AUXDatabase")
 
TeamComboBox.Clear
ComboBox6.Clear
With CreateObject("Scripting.Dictionary")
    For Each CellAddr In Worksht.Range("A2", Worksht.Cells(Rows.Count, "A").End(xlUp))
        If Not .exists(CellAddr.Value) Then
            .Add CellAddr.Value, Nothing
        End If
    Next CellAddr
    TeamComboBox.List = .KEYS
End With
With CreateObject("Scripting.Dictionary")
    For Each CellAddr In Worksht2.Range("A2", Worksht2.Cells(Rows.Count, "A").End(xlUp))
        If Not .exists(CellAddr.Value) Then
            .Add CellAddr.Value, Nothing
        End If
    Next CellAddr
    ComboBox6.List = .KEYS
End With
End Sub
Private Sub TeamComboBox_AfterUpdate()
Dim Worksht As Worksheet
Dim CellAddr As Range
Dim Thrd As Variant
Set Worksht = Worksheets("Threadatabase")
ThreadComboBox.Clear
Thrd = 2
 
With CreateObject("Scripting.Dictionary")
    For Each CellAddr In Worksht.Range("B2", Worksht.Cells(Rows.Count, "B").End(xlUp))
        
        If Not .exists(CellAddr.Value) Then
            
            If TeamComboBox.Value = Worksheets("Threadatabase").Range("A" & Thrd).Value Then
            .Add CellAddr.Value, Nothing
            End If
        End If
        Thrd = Thrd + 1
    Next CellAddr
    ThreadComboBox.List = .KEYS
End With
End Sub

 

[Worf]

Hi

- Did you post the whole code?
-The following worked for me:

Private Sub TeamComboBox_Change()
Dim Worksht As Worksheet, CellAddr As Range, Thrd
Set Worksht = Worksheets("Threadatabase")
ThreadComboBox.Value = ""   ' blank
Thrd = 2
With CreateObject("Scripting.Dictionary")
    For Each CellAddr In Worksht.Range("B2", Worksht.Cells(Rows.Count, "B").End(xlUp))
        If Not .exists(CellAddr.Value) Then
            If TeamComboBox.Value = Worksht.Range("A" & Thrd).Value Then
                .Add CellAddr.Value, Nothing
            End If
        End If
        Thrd = Thrd + 1
    Next CellAddr
    ThreadComboBox.List = .KEYS
End With
End Sub


Private Sub UserForm_Click()
Dim Worksht As Worksheet, Worksht2 As Worksheet, CellAddr As Range
Set Worksht = Worksheets("Threadatabase")
Set Worksht2 = Worksheets("AUXDatabase")
TeamComboBox.Clear  ' first box
ComboBox6.Clear     ' third box
ThreadComboBox.Clear    ' second box
With CreateObject("Scripting.Dictionary")
    For Each CellAddr In Worksht.Range("A2", Worksht.Cells(Rows.Count, "A").End(xlUp))
        If Not .exists(CellAddr.Value) Then
            .Add CellAddr.Value, Nothing
        End If
    Next CellAddr
    TeamComboBox.List = .KEYS
End With
With CreateObject("Scripting.Dictionary")
    For Each CellAddr In Worksht2.Range("A2", Worksht2.Cells(Rows.Count, "A").End(xlUp))
        If Not .exists(CellAddr.Value) Then
            .Add CellAddr.Value, Nothing
        End If
    Next CellAddr
    ComboBox6.List = .KEYS
End With
End Sub