How do I avoid Userform Combobox runtime error?

November 10, 2021

Hopefully a simple question. I have some simple code for a combo box that runs during Combobox_Change().

Private Sub ComboBox1_Change()
If ComboBox1.Value = "" Then
Label3.Caption = ""

Else

Label3.Caption = Worksheets("Currency").Cells.Find(UserForm1.ComboBox1.Value).Offset(0, -1).Value



End If

End Sub

Private Sub UserForm_Initialize()
ComboBox1.List = [Currency!C2:C168].Value
Label3.Caption = ""
End Sub

But when you enter something that isn’t part of the declared Combobox range it throws up a runtime error ‘Object variable not set’. How do I fool proof this combobox and when any irregular entry is made that isn’t part of the selection range for it to revert back to "" empty? Or pop up with an error box stating "Invalid Input"?

>Solution :

If Find fails to find anything it returns a null object. That returned object has no methods or properties so you can’t take the offset() or value of it. To work around this you need to separate out the returned object and its methods/properties and test the validity of the returned object.

Private Sub ComboBox1_Change()
    If ComboBox1.Value = "" Then
        Label3.Caption = ""
    Else
        Dim fndrng As Range
        'Get just the find range
        Set fndrng = Worksheets("Currency").Cells.Find(UserForm1.ComboBox1.Value)
        'Make sure find found something
        If Not fndrng Is Nothing Then
            'use the methods/properties we want
            Label3.Caption = fndrng.Offset(0, -1).Value
        Else
            MsgBox "Selection not found", vbOKOnly, "Error"
        End If
    End If
End Sub

Private Sub UserForm_Initialize()
    ComboBox1.List = [Currency!C2:C168].Value
    Label3.Caption = ""
End Sub