VBA - MsgBox IFERROR

[sprigelz]

Good day!

I have a Userform that displays a combobox and also a textbox. I want to display a MsgBox if the user types in a name in the Textbox that doesn't already exist in the table. Right now it technically works as it displays a "Runtime Error 1004 - Unable to get the Match property of the WorksheetFunction class." But rather than showing this Excel VBA error message, I'd rather it just show a MsgBox that a user can just click OK and return to the userform.

Table Worksheet - Employee List
Table - Employees

Private Sub CommandButton1_Click()

'Check to see if Valid search entries
If ComboBox1.Value <> "" And Trim(TextBox1.Text) <> "" Then
    MsgBox "Please use the drop down menu OR the Last Name text box. You cannot search using both."
    Exit Sub
ElseIf ComboBox1.Value = "" And Trim(TextBox1.Text) = "" Then
    MsgBox "Invalid parameters."
    Exit Sub
Else
End If

'Write data to cell for lookup later
If ComboBox1.Value <> "" Then
    Range("FullNameLookUp").Value = ComboBox1.Value
Else
    Range("LastNameLookUp").Value = TextBox1.Value
End If

Unload Me
With EmployeeLookup
  .StartUpPosition = 0
  .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
  .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
  .Show
End With

End Sub

 

[Dan_W]

I don't think that the code you posted above contains the code that VBA is complaining about. Do you have WorksheetFunction.Match written elsewhere in your code?

 

[sprigelz]

That opens the next Userform - EmployeeLookup.

The Functions are activated on the Userform_Initialize of this next Userform, below.

I want the MsgBox to appear when someone hits the CommandButton1 button above, not when it opens this form as that would just result in an error as it is now.

Private Sub UserForm_Initialize()

'Search Employee List Sheet and populate Userform Textboxes
TextBox3.Value = Worksheets("Dashboard").Range("R1").Value
TextBox1.Value = Application.WorksheetFunction.Index(Sheets("Employee List").Range("D1:D1000"), Application.WorksheetFunction.Match(TextBox3.Value, Sheets("Employee List").Range("C1:C1000"), 0))
TextBox2.Value = Application.WorksheetFunction.Index(Sheets("Employee List").Range("E1:E1000"), Application.WorksheetFunction.Match(TextBox3.Value, Sheets("Employee List").Range("C1:C1000"), 0))
TextBox4.Value = Application.WorksheetFunction.Index(Sheets("Employee List").Range("F1:F1000"), Application.WorksheetFunction.Match(TextBox3.Value, Sheets("Employee List").Range("C1:C1000"), 0))
TextBox5.Value = Application.WorksheetFunction.Index(Sheets("Employee List").Range("G1:G1000"), Application.WorksheetFunction.Match(TextBox3.Value, Sheets("Employee List").Range("C1:C1000"), 0))
TextBox6.Value = Application.WorksheetFunction.Index(Sheets("Employee List").Range("H1:H1000"), Application.WorksheetFunction.Match(TextBox3.Value, Sheets("Employee List").Range("C1:C1000"), 0))
TextBox7.Value = Application.WorksheetFunction.Index(Sheets("Employee List").Range("J1:J1000"), Application.WorksheetFunction.Match(TextBox3.Value, Sheets("Employee List").Range("C1:C1000"), 0))
    
End Sub

 

[Dan_W]

I understand that, but it's just not very helpful to be told about an error message that results from code that you're not being shown.
As it is, your code will show the msgbox if both the combobox and textbox have text in them, or neither have text in them. If there is text in either of them, then the code proceeds and likely results in the error you're experiencing.

What code are you using to test if the name in the Textbox that doesn't already exist in the table?

 

[sprigelz]

Apologies. The current Both and Neither MsgBox code is working as prescribed.

What I am looking to do is write new code that checks the value of TextBox1, does a lookup or index/match with the table, and if no results are found displays a MsgBox.