Error handling with a combo box VLOOKUP in VBA

Question

I have created a userform that takes a list of job refs(jobRefCbo) and populates text boxs based on the selection. I have not yet understood errors and if you select a blank or press escape in the combo box it jumps back to the VBA error screen.

How can I implement a way to just 'do nothing' if the job ref selection is not valid?

I guess a smart way would to be to have a locked list but I am trying to pull the cbo list from a list of active jobs in a column.

Private Sub UserForm_Initialize()
Sheets("Lists ").Activate
Dim xRg As Range

Application.ScreenUpdating = False

Call Lists_sort


    Set xRg = Worksheets("Lists ").Range("I2:P21")
    Me.jobRefCbo.List = xRg.Columns(1).Value

    Application.ScreenUpdating = True

End Sub

Here is the combo box code:

Private Sub jobRefCbo_Change()
Application.ScreenUpdating = False


Call Lists_sort


Sheets("Tracker").Activate


    'Formatting Issue
    jobCloseFrm.date2Txt.Value = Format(Range("W1").Value, "dd/mm/yyyy")


    Me.nameTxt.Value = Application.VLookup(Me.jobRefCbo.Value & "", Worksheets("Lists ").Range("I3:P21"), 2, False)
     'Me.acNoTxt.Value = Application.WorksheetFunction.VLookup(Me.jobRefCbo.Value, Worksheets("Lists ").Range("I3:P21"), 2, False)
    Me.jobDesc2Txt.Value = Application.WorksheetFunction.VLookup(Me.jobRefCbo.Value, Worksheets("Lists ").Range("I3:P21"), 3, False)
    Me.date2Txt.Value = Application.WorksheetFunction.VLookup(Me.jobRefCbo.Value, Worksheets("Lists ").Range("I3:P21"), 4, False)
    Me.month2Txt.Value = Application.WorksheetFunction.VLookup(Me.jobRefCbo.Value, Worksheets("Lists ").Range("I3:P21"), 5, False)
    Me.timeOnJobTxt.Value = Application.WorksheetFunction.VLookup(Me.jobRefCbo.Value, Worksheets("Lists ").Range("I3:P21"), 6, False)
    Me.StatusTxt.Value = Application.WorksheetFunction.VLookup(Me.jobRefCbo.Value, Worksheets("Lists ").Range("I3:P21"), 7, False)
    Me.startTime2Txt.Value = Format(CDate(Application.WorksheetFunction.VLookup(Me.jobRefCbo.Value, Worksheets("Lists ").Range("I3:P21"), 8, False)), "hh:mm:ss AM/PM")
    jobCloseFrm.date2Txt.Value = Format(Range("W1").Value, "dd/mm/yyyy")

    Application.ScreenUpdating = True

End Sub

Any help you can give is much appreciated!

Craig

Answer 1

You can do this one of two ways. The first is to implement an error handler in your jobRefCbo_Change code that will catch the failure of the VLOOKUP function.

Private Sub jobRefCbo_Change()
    Application.ScreenUpdating = False
    Call Lists_sort
    Sheets("Tracker").Activate

    'Formatting Issue
    jobCloseFrm.date2Txt.Value = Format(Range("W1").Value, "dd/mm/yyyy")

    On Error GoTo VLookup_Error
    Me.nameTxt.Value = Application.VLookup(Me.jobRefCbo.Value & "", Worksheets("Lists ").Range("I3:P21"), 2, False)
    'Me.acNoTxt.Value = Application.WorksheetFunction.VLookup(Me.jobRefCbo.Value, Worksheets("Lists ").Range("I3:P21"), 2, False)
    Me.jobDesc2Txt.Value = Application.WorksheetFunction.VLookup(Me.jobRefCbo.Value, Worksheets("Lists ").Range("I3:P21"), 3, False)
    Me.date2Txt.Value = Application.WorksheetFunction.VLookup(Me.jobRefCbo.Value, Worksheets("Lists ").Range("I3:P21"), 4, False)
    Me.month2Txt.Value = Application.WorksheetFunction.VLookup(Me.jobRefCbo.Value, Worksheets("Lists ").Range("I3:P21"), 5, False)
    Me.timeOnJobTxt.Value = Application.WorksheetFunction.VLookup(Me.jobRefCbo.Value, Worksheets("Lists ").Range("I3:P21"), 6, False)
    Me.StatusTxt.Value = Application.WorksheetFunction.VLookup(Me.jobRefCbo.Value, Worksheets("Lists ").Range("I3:P21"), 7, False)
    Me.startTime2Txt.Value = Format(CDate(Application.WorksheetFunction.VLookup(Me.jobRefCbo.Value, Worksheets("Lists ").Range("I3:P21"), 8, False)), "hh:mm:ss AM/PM")
    jobCloseFrm.date2Txt.Value = Format(Range("W1").Value, "dd/mm/yyyy")
    Application.ScreenUpdating = True

VLookup_Error:
    '--- nothing to do, just exit

End Sub

The other method is to grab the list of values from the worksheet into an array and perform a quick check before you try to use the value.

Option Explicit

Private Type InternalData
    jobRefs As Variant
End Type
Private this As InternalData

Private Sub jobRefCbo_Change()
    Dim cbValue As Variant
    cbValue = Me.jobRefCbo.Value
    If ValueInList(cbValue) Then
        Debug.Print "the value is valid! (" & cbValue & ")"
    Else
        Debug.Print "the value is invalid! (" & cbValue & ")"
    End If
End Sub

Private Sub QuitButton_Click()
    Me.Hide
End Sub

Private Function ValueInList(ByVal thisValue As Variant) As Boolean
    Dim i As Long
    ValueInList = False
    For i = LBound(this.jobRefs) To UBound(this.jobRefs)
        If this.jobRefs(i, 1) = thisValue Then
            ValueInList = True
            Exit For
        End If
    Next i
End Function

Private Sub UserForm_Initialize()
    this.jobRefs = Worksheets("Lists ").Range("I2:P21").Columns(1).Value
    Me.jobRefCbo.List = this.jobRefs
End Sub