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

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