MsgBox to validate an entry

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

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
TimN wrote:
> 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

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

--
HTH...

Jim Thomlinson


"TimN" wrote:

> 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

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

"Jim Thomlinson" wrote:

> 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
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "TimN" wrote:
>
> > 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

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

Jim Thomlinson


"TimN" wrote:

> 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
>
> "Jim Thomlinson" wrote:
>
> > 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
> >
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "TimN" wrote:
> >
> > > 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

It is pointing to the following line:

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

"Jim Thomlinson" wrote:

> Sorry for being gone so long. Meetings... Which line is it stopping on???
> --
> HTH...
>
> Jim Thomlinson
>
>
> "TimN" wrote:
>
> > 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
> >
> > "Jim Thomlinson" wrote:
> >
> > > 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
> > >
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "TimN" wrote:
> > >
> > > > 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

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.

"TimN" wrote:

> It is pointing to the following line:
>
> If Application.WorksheetFunction.CountIf _
> (Sheets("LookupSheet").Range("A:A"), TxtEmployeeNumber.Value) = 0
>
> "Jim Thomlinson" wrote:
>
> > Sorry for being gone so long. Meetings... Which line is it stopping on???
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "TimN" wrote:
> >
> > > 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
> > >
> > > "Jim Thomlinson" wrote:
> > >
> > > > 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
> > > >
> > > > --
> > > > HTH...
> > > >
> > > > Jim Thomlinson
> > > >
> > > >
> > > > "TimN" wrote:
> > > >
> > > > > 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