I have built a User Form that asks for an employee ID number to be entered.
That data then goes to cell A2 on the spreadsheet. Cell A1 has a VLOOKUP
formula that goes to another sheet and finds that employee ID number and
returns the name to cell A1. If it can't find the ID number then #N/A
returns in cell A1.
I want to create a message box that will pop up after the user types an
invalid number in the User Form indicating "Invalid Employee Number".

How can I do that? Also, I know this sound s extremely simple, but being
new to this I am unsure. The code for the above message box, where do I
insert that? I assume in the code for the User Form?

Thanks so much for any assistance!
Tim

I assume the user is entering the ID into a text box... If so then Text boxes
have events one of which is exit. When the user exits the text box you can
valicdate their input something like this

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Application.WorksheetFunction.CountIf _
(Sheets("Sheet1").Range("A:A"), TextBox1.Value) = 0 Then
MsgBox "Sorry, Invalid ID", vbInformation
Cancel = True
End If
End Sub
--
HTH...

Jim Thomlinson


"TimN" wrote:

I have built a User Form that asks for an employee ID number to be entered.
That data then goes to cell A2 on the spreadsheet. Cell A1 has a VLOOKUP
formula that goes to another sheet and finds that employee ID number and
returns the name to cell A1. If it can't find the ID number then #N/A
returns in cell A1.
I want to create a message box that will pop up after the user types an
invalid number in the User Form indicating "Invalid Employee Number".

How can I do that? Also, I know this sound s extremely simple, but being
new to this I am unsure. The code for the above message box, where do I
insert that? I assume in the code for the User Form?

Thanks so much for any assistance!
Tim

Jim,

Thanks for your help. I know this is my mistake somewhere, but here is what
I am getting:

I have the following code for my Text Box:
Private Sub TxtEmployeeNumber_Change()
Range("C3") = TxtEmployeeNumber.Text
End Sub
As a new Sub procedure just below the above code I entered the code you
provided only changing out TextBox1 to TxtEmployeeNumber and changing out
"Sheet1" to "STD Calc" and Range("A:A") to Range ("C3") for the cell that
contains the resulting employee name. All else is the same.
I get the following error message "Run time error '438' Object doesn't
support this property or method.

What is happening?
By the way STD Calc stands for Short Term Disability :)

Tim

"Jim Thomlinson" wrote:

I assume the user is entering the ID into a text box... If so then Text boxes
have events one of which is exit. When the user exits the text box you can
valicdate their input something like this

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Application.WorksheetFunction.CountIf _
(Sheets("Sheet1").Range("A:A"), TextBox1.Value) = 0 Then
MsgBox "Sorry, Invalid ID", vbInformation
Cancel = True
End If
End Sub
--
HTH...

Jim Thomlinson


"TimN" wrote:

I have built a User Form that asks for an employee ID number to be entered.
That data then goes to cell A2 on the spreadsheet. Cell A1 has a VLOOKUP
formula that goes to another sheet and finds that employee ID number and
returns the name to cell A1. If it can't find the ID number then #N/A
returns in cell A1.
I want to create a message box that will pop up after the user types an
invalid number in the User Form indicating "Invalid Employee Number".

How can I do that? Also, I know this sound s extremely simple, but being
new to this I am unsure. The code for the above message box, where do I
insert that? I assume in the code for the User Form?

Thanks so much for any assistance!
Tim