Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.misc
|
|||
|
|||
Detecting Cancel in an InputBox Method
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Detecting Cancel in an InputBox Method
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 JMB wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formating inputbox result in excel | Excel Worksheet Functions | |||
Can't get Cancel to work in message box | Excel Discussion (Misc queries) | |||
Password redundant through use of cancel | Excel Discussion (Misc queries) | |||
Cancel user's changes but save other changes | Excel Discussion (Misc queries) | |||
If a cell is blank do no let a user print or save? | Excel Worksheet Functions |