Clear userform sub giving 1004 error due to dynamic combobox

Question

I am working on a customer complaint database. I want to implement a userform for data entry.

I have a button to clear the userform. It clears the entire form except for ComboBox2 that has results which are dependent on the selection from ComboBox1.

I get a 1004 run-time error based which calls back to the Match function I've used for ComboBox2.

Private Sub CommandButton2_Click()
    Clear_Form
End Sub

Sub Clear_Form()
    Me.ComboBox2.Clear
    For Each ctl In Me.Controls
        Select Case TypeName(ctl)
            Case "TextBox"
                ctl.Text = ""
            Case "ComboBox"
                ctl.ListIndex = -1

        End Select
    Next ctl
End Sub

Private Sub UserForm_Activate()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Customer")

    Dim i As Integer
    Me.ComboBox1.Clear
    For i = 1 To Application.WorksheetFunction.CountA(sh.Range("1:1"))
        Me.ComboBox1.AddItem sh.Cells(1, i).Value

    Next i
End Sub

Private Sub ComboBox1_Change()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Customer")
    Dim i As Integer
    Dim n As Integer

    n = Application.WorksheetFunction.Match(Me.ComboBox1.Value, sh.Range("1:1"), 0)
    Me.ComboBox2.Clear
    For i = 2 To Application.WorksheetFunction.CountA(sh.Cells(1, n).EntireColumn)
        Me.ComboBox2.AddItem sh.Cells(i, n).Value
    Next i
End Sub

Answer 1

You will get an error because Clear_Form clears the combobox which in turn calls the _Change event and the Match fails because there is no value in combobox. Simply add one line to the _Change event.

Private Sub ComboBox1_Change()
    If ComboBox1.ListIndex = -1 Then Exit Sub '<~~ ADD THIS

    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Customer")
    Dim i As Integer
    Dim n As Integer

    n = Application.WorksheetFunction.Match(Me.ComboBox1.Value, sh.Range("1:1"), 0)

    Me.ComboBox2.Clear

    For i = 2 To Application.WorksheetFunction.CountA(sh.Cells(1, n).EntireColumn)
        Me.ComboBox2.AddItem sh.Cells(i, n).Value
    Next i
End Sub