Detecting Cancel in an InputBox Method

[Connie]

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

 

[JMB]

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]

Your suggestion worked beautifully! I had to do some manipulations,
but I got it to work. Sorry it took so long to respond; I was pulled
out of town on business and haven't been able to work on the
spreadsheet for a couple of days. In case anyone else is trying to do
something similar, I am posting my final code for this functionality.
Thanks again. Connie

Dim TempOracleNo As Long
Dim sh As Worksheet
Dim rng As Range
Dim strMsg As String
Dim Name As String
Dim intReturn As Integer



strMsg = "Enter Employee Number"
Set sh = Worksheets("Employee List")
'Determine Range of Employee List
Set rng = GetRealLastCell(sh)
Lookuprange = ("$a$2:" + rng.Address)
TempOracleNo = Application.InputBox(Prompt:=strMsg, Type:=1)
If TempOracleNo = False Then
MsgBox "You have elected to cancel this operation."
Exit Sub
End If
ReturnValue = Application.VLookup(TempOracleNo, _
sh.Range(Lookuprange), 1, False)
Do Until Not IsError(ReturnValue)
strMsg = "Invalid employee number. Please enter a valid
employee number."
TempOracleNo = Application.InputBox(Prompt:=strMsg,
Title:="Invalid Employee Number", Type:=1)
If TempOracleNo = False Then
MsgBox "You have elected to cancel the operation."
Sheets("Field Rep Time Sheet").Range("B5").Select
Selection.ClearContents
Exit Sub
End If
ReturnValue = Application.VLookup(TempOracleNo, _
sh.Range(Lookuprange), 1, False)
Loop
'Populate Oracle_No
Sheets("Field Rep Time Sheet").Range("B5").Select
ActiveCell.FormulaR1C1 = TempOracleNo
Sheets("Field Rep Time Sheet").Range("M5").Select

********************************

Function GetRealLastCell(sh As Worksheet) As Range
Dim RealLastRow As Long
Dim RealLastColumn As Long
'On Error Resume Next
RealLastRow = _
sh.Cells.Find("*", sh.Range("A1"), , , xlByRows, xlPrevious).Row
RealLastColumn = _
sh.Cells.Find("*", sh.Range("A1"), , , xlByColumns,
xlPrevious).Column
Set GetRealLastCell = sh.Cells(RealLastRow, RealLastColumn)
End Function