what to have userform do when 'error' happens...

[kbishop94]

when my userform opens up (it is executed by clicking on sheet tab named 'open edit form') a field is available that prompts the user to enter in a "Incident ID" number. If a match is found, then the form populates with the data from that particular row on the spreadsheet.

But, if a match ISN'T found, then the user is prompted with a message box stating "Incident ID not found."
From there, I just want the form to reload again so the user can try and enter a different number...
But instead it I get " Run-time error '1004' Unable to get the VLookup property of the WorksheetFunction class. "

ive tried several 'on error' events, but i still cannot get it to just ignore the error and just have the user retry another number... (?)

Here is my code:
Thanks for any help here!

Private Sub txtIncidentID1_AfterUpdate()

[COLOR=#008000][B]' Lookup Incident ID Number[/B][/COLOR]
Dim cK1 As String

If WorksheetFunction.CountIf(Sheet1.Range("A:A"), Me.txtIncidentID1.Value) = 0 Then

[COLOR=#008000][B]' If Incident ID is not found then:[/B][/COLOR]
MsgBox "Incident ID not found."
Me.txtIncidentID1.Value = ""
End If

[COLOR=#008000][B]' If Incident ID [I]IS[/I] found, then populate the userform with the data specific to that Incident ID
[/B][/COLOR]
With Me
.DTPicker3 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 2, 0)
.cboLocation1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 6, 0)
.cboPriority1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 4, 0)
.txtCAPA1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 18, 0)
.cboCustomer1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 7, 0)
.txtProblem1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 8, 0)
.txtAction1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 9, 0)
.cboIssuedBy1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 10, 0)
.cboOnBehalfOf1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 11, 0)
.cboIssuedTo1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 12, 0)
.cboIssuedTo21 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 13, 0)
.txtCostProd1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 20, 0)
.txtCostShip1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 21, 0)
.txtCostConcess1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 22, 0)
.txtCostTravel1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 23, 0)
.txtCostFacility1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 24, 0)
.txtCostOther1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 25, 0)
.txtCost1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 16, 0)
.txtNotes1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 19, 0)


cK1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 14, 0)
If cK1 = "YES" Then .chkYes1 = True Else
If cK1 = "no" Then .chkNo1 = True Else

End With
End Sub

 

[Fluff]

If you're happy changing it to an exit event, you could use

Private Sub txtIncidentID1_[COLOR=#0000ff]Exit(ByVal Cancel As MSForms.ReturnBoolean)[/COLOR]

' Lookup Incident ID Number
Dim cK1 As String

If WorksheetFunction.CountIf(Sheet1.Range("A:A"), Me.txtIncidentID1.Value) = 0 Then

' If Incident ID is not found then:
MsgBox "Incident ID not found."
Me.txtIncidentID1.Value = ""
[COLOR=#0000ff]Cancel = True
Exit Sub[/COLOR]
End If

' If Incident ID IS found, then populate the userform with the data specific to that Incident ID

 

[kbishop94]

That works. THanks again, Fluff.

 

[Fluff]

You're welcome