Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MsgBox to validate an entry
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
MsgBox to validate an entry
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
MsgBox to validate an entry
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
MsgBox to validate an entry
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
MsgBox to validate an entry
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
MsgBox to validate an entry
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validate MsgBox Entry to Data in Cells | Excel Discussion (Misc queries) | |||
Validate InputBox entry | Excel Programming | |||
Validate textbox entry | Excel Programming | |||
Validate Combobox entry | Excel Programming | |||
Validate Textbox entry | Excel Programming |