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!
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!
Code:
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