Yes you get 2 inputboxes by having it in your If statement. You could save
the value from the inputbox to your variable first, then test the variable
for false, or error, etc. You should be able to get away w/having just one
loop to get the user input. Below is an example of how I would try to set it
up - I didn't rewrite all of the code - just the loop to get the user input.


Dim strMsg As String
strMsg = "Enter Employee Number"

Do Until Not IsError(ReturnValue)
TempOracleNo = Application.InputBox(Prompt:=strMsg, Type:=1)
If TempOracleNo = False Then
MsgBox "User Canceled"
Exit Sub
Else
ReturnValue = Application.VLookup(TempOracleNo, _
Sheets("Employee List").Range(Lookuprange), 1, False)
strMsg = "Invalid employee number. Please enter a valid employee number."
End If
Loop


"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