Search code examples
excelvbauserform

"Run-time error 1004, Unable to get Match Property of the WorksheetFunction class"

enter image description hereI am trying to get both of the comboboxes(valuerFirmCB, valuerNameCB) to be dynamic i.e the valuerNameCB will only display the names accordingly to the firm.

It works but however I got an error after adding the entry to the spreadsheet "Run-time error 1004, Unable to get Match Property of the WorksheetFunction class".

Private Sub UserForm_Activate()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Valuer_Details")

Dim i As Integer

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

Next i
End Sub

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

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

Me.valuerNameCB.Clear
For i = 2 To Application.WorksheetFunction.CountA(sh.Cells(1, n).EntireColumn)
    Me.valuerNameCB.AddItem sh.Cells(i, n).Value

Next i

End Sub
Solution

  • Each time you activate your UserForm - you trigger an UserForm_Activate event:

    Private Sub UserForm_Activate()

    Which clears you ComboBox:

    Me.valuerFirmCB.Clear

    This action in its turn triggers a ComboBox_Change event if the form was hidden and a ComboBox has some value selected before activation:

    Private Sub valuerFirmCB_Change()

    And sets the valuerFirmCB.Value to "" (Nothing, as you see on your screenshot).

    As your Me.valuerFirmCB.Clear is a ComboBox_Change event, so event is fired and trying to find the "" value, which generates an error in match function. Also, this error will be thrown if you simply delete the selected value from valuerFirmCB manually.

    The way you can deal this issue is to add a simple check to Private Sub valuerFirmCB_Change() like this:

    If Not valuerFirmCB.Value = "" Then
    n = Application.WorksheetFunction.Match(valuerFirmCB.Value, sh.Range("1:1"), 0)

    Me.valuerNameCB.Clear
    For i = 2 To Application.WorksheetFunction.CountA(sh.Cells(1, n).EntireColumn)
        Me.valuerNameCB.AddItem sh.Cells(i, n).Value

    Next i
End If

    So the search will be performed only when valuerNameCB has some value.

    Added per comment

    I've replicated this situation with additional buttons with code Form.Show 0 on the worksheet and Form.Hide on the form. Switching between form and sheet does nothing, but if you select an item in ComboBox, Hide the form and Show again - the change event on Me.valuerFirmCB.Clear will be triggered. However, this won't happen if I use Unload Me instead of Form.Hide.