MsgBox to validate an entry

[Guest]

I have created a user form that has a text box asking the user to enter an
employee ID number. Upon entry, the number is placed in cell C3 on the
spreadsheet. There is a VLOOKUP formula in cell C2 that goes to another
sheet (in the same workbook) and searches for the ID number and brings back
to cell C2 the cooresponding employee name.

If the number entered in the text box is not found then #N/A results in cell
C3.
I want to create a msgBox that would pop up as soon as an invalid number is
entered in the text box saying "Invalid Employee ID Number".

How do I get there?

My TextBox code is as follows:

Private Sub TxtEmployeeNumber_Change()
Range ("C3")=TxtEmployeeNumber.Text
End Sub

 

[Die_Another_Day]

Private Sub TxtEmployeeNumber_Change()
Range ("C3")=TxtEmployeeNumber.Text
Range("C2").Calculate 'Shouldn't be needed unless
calculation is manual
If Evaluate(iserror(c2)) then
MsgBox "You Infidel!!, You have entered an Invalid
Employee ID Number"
Range("C3").ClearContents
End If
End Sub

HTH

Die_Another_Day

 

[Guest]

Add the value when you exit the textbox... Not while entering the ID... The
principal is that this is going to do a count of how many times the ID is
found in the lookup range. If it is 0 then the ID was not found and we will
send the user back to the drawing board. Else we will add the value to cell
C3. Since I don't know what your lookup range is I have assumed Column A on
sheet LookupSheet... The only other thing to add will be something to allow
the user to exit the box if they just can not get a Valid ID... Let me know
if you want help with that...

Private Sub TxtEmployeeNumber_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Application.WorksheetFunction.CountIf _
(Sheets("LookupSheet").Range("A:A"), TxtEmployeeNumber.Value) = 0 Then
MsgBox "Sorry, Invalid ID", vbInformation
Cancel = True
Else
Sheets("STD Calc").Range ("C3")=TxtEmployeeNumber.Text
End If
End Sub

 

[Guest]

Jim,

Thanks again for your help. I'm not getting it to work though. I still get
the Run time error '438' Object doesn't support this property or method
error. What is causing that to happen?

Tim

 

[Guest]

Sorry for being gone so long. Meetings... Which line is it stopping on???

 

[Guest]

It is pointing to the following line:

If Application.WorksheetFunction.CountIf _
(Sheets("LookupSheet").Range("A:A"), TxtEmployeeNumber.Value) = 0

 

[Guest]

Jim,

Also, just to clarify, I made the following changes to match my worksheet
name:

If Application.WorksheetFunction.CountIf _
(Sheets("Sick").Range("A:A"), TxtEmployeeNumber.Value) = 0

The worksheet that the lookup occurs in is titled "Sick" and the employee
number is in column A.