Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting Cancel in an InputBox Method
I am using the following code to obtain an Oracle number from the user
and then check to determine whether the user number is valid. Valid means that the number can be found on a separate sheet in the workbook titled "Employee List". I do a vlookup when I do the check. My intent is that the user will enter an Oracle number when prompted, and if the Oracle number is not contained on the list they will get another message to say that the number input is not valid; please enter a valid oracle number. The problem with the code below is that it repeats the first input box, probably because I have the InputBox in the If statement to check if the user clicks cancel, and then after the "ELSE". How do I eliminate prompting the original Inputbox twice? Thanks. Connie Private Sub Enter_Employee_Data_Click() Dim TempOracleNo As Long Dim sh As Worksheet Dim rng As Range If Application.InputBox(Prompt:="Enter Employee Number", Type:=1) = False Then MsgBox "User clicked cancel" Exit Sub Else TempOracleNo = Application.InputBox(Prompt:="Enter Employee Number", Type:=1) Set sh = Worksheets("Employee List") Set rng = GetRealLastCell(sh) Lookuprange = ("$a$2:" + rng.Address) ReturnValue = Application.VLookup(TempOracleNo, _ Sheets("Employee List").Range(Lookuprange), 1, False) Do Until Not IsError(ReturnValue) If Application.InputBox(Prompt:="Invalid employee number. Please enter a valid employee number.", Type:=1) = False Then MsgBox "User clicked cancel" Exit Sub Else TempOracleNo = Application.InputBox(Prompt:="Invalid employee number. Please enter a valid employee number.", Type:=1) ReturnValue = Application.VLookup(TempOracleNo, _ Sheets("Employee List").Range(Lookuprange), 1, False) End If Loop Sheets("Field Rep Time Sheet").Range("B5").Select ActiveCell.FormulaR1C1 = TempOracleNo End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting Cancel in an InputBox Method
There is a response at your other post
"Connie" wrote: I am using the following code to obtain an Oracle number from the user and then check to determine whether the user number is valid. Valid means that the number can be found on a separate sheet in the workbook titled "Employee List". I do a vlookup when I do the check. My intent is that the user will enter an Oracle number when prompted, and if the Oracle number is not contained on the list they will get another message to say that the number input is not valid; please enter a valid oracle number. The problem with the code below is that it repeats the first input box, probably because I have the InputBox in the If statement to check if the user clicks cancel, and then after the "ELSE". How do I eliminate prompting the original Inputbox twice? Thanks. Connie Private Sub Enter_Employee_Data_Click() Dim TempOracleNo As Long Dim sh As Worksheet Dim rng As Range If Application.InputBox(Prompt:="Enter Employee Number", Type:=1) = False Then MsgBox "User clicked cancel" Exit Sub Else TempOracleNo = Application.InputBox(Prompt:="Enter Employee Number", Type:=1) Set sh = Worksheets("Employee List") Set rng = GetRealLastCell(sh) Lookuprange = ("$a$2:" + rng.Address) ReturnValue = Application.VLookup(TempOracleNo, _ Sheets("Employee List").Range(Lookuprange), 1, False) Do Until Not IsError(ReturnValue) If Application.InputBox(Prompt:="Invalid employee number. Please enter a valid employee number.", Type:=1) = False Then MsgBox "User clicked cancel" Exit Sub Else TempOracleNo = Application.InputBox(Prompt:="Invalid employee number. Please enter a valid employee number.", Type:=1) ReturnValue = Application.VLookup(TempOracleNo, _ Sheets("Employee List").Range(Lookuprange), 1, False) End If Loop Sheets("Field Rep Time Sheet").Range("B5").Select ActiveCell.FormulaR1C1 = TempOracleNo End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Detecting Cancel in an InputBox Method | Excel Discussion (Misc queries) | |||
How to handle cancel when using the inputbox method | Excel Programming | |||
Cancel button in Inputbox method | Excel Programming | |||
Inputbox method using type:=8 - How to Cancel? | Excel Programming | |||
Inputbox method using type:=8 - How to Cancel? | Excel Programming |