Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code for MsgBox
For the following script I get an inputbox for which the user has to enter a
number: Dim result As Long result = Application.InputBox("Enter Number of Days which are in this report( highest number of worksheets in document", "Days in Report") Select Case result Case 1 Columns("T:CJ").Select Case 2 Columns("U:CJ").Select Case 3 Columns("V:CJ").Select Case Else MsgBox "please enter a valid number" End Select I would like the macro to exit the sub when "cancel" is selected. Can anyone help me with this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code for MsgBox
Sub test()
Dim result result = Application.InputBox("Enter Number of Days which are in this report( highest number of worksheets in document", "Days in Report") If result = False Then Exit Sub End If Select Case result Case 1 Columns("T:CJ").Select Case 2 Columns("U:CJ").Select Case 3 Columns("V:CJ").Select Case Else MsgBox "please enter a valid number" End Select End Sub RBS "Chris D" wrote in message ... For the following script I get an inputbox for which the user has to enter a number: Dim result As Long result = Application.InputBox("Enter Number of Days which are in this report( highest number of worksheets in document", "Days in Report") Select Case result Case 1 Columns("T:CJ").Select Case 2 Columns("U:CJ").Select Case 3 Columns("V:CJ").Select Case Else MsgBox "please enter a valid number" End Select I would like the macro to exit the sub when "cancel" is selected. Can anyone help me with this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code for MsgBox
Hi Chris,
Try changing your result variable to a variant, which will wenabl you to trap a cancel operation. Try, therefo '========== Public Sub Tester() Dim result As Variant result = Application.InputBox _ ("Enter Number of Days" _ & " which are in this report" _ & "( highest number of worksheets in " _ & "document", "Days in Report") Select Case result Case 1 Columns("T:CJ").Select Case 2 Columns("U:CJ").Select Case 3 Columns("V:CJ").Select Case False Exit Sub Case Else MsgBox "please enter a valid number" End Select End Sub '<<========== --- Regards. Norman "Chris D" wrote in message ... For the following script I get an inputbox for which the user has to enter a number: Dim result As Long result = Application.InputBox("Enter Number of Days which are in this report( highest number of worksheets in document", "Days in Report") Select Case result Case 1 Columns("T:CJ").Select Case 2 Columns("U:CJ").Select Case 3 Columns("V:CJ").Select Case Else MsgBox "please enter a valid number" End Select I would like the macro to exit the sub when "cancel" is selected. Can anyone help me with this? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code for MsgBox
For Application.Inputbox if the result is FALSE it was cancelled. However if
the result is the word False it's treated the same as a cancel. Dim v As Variant v = Application.InputBox("enter a value") If v = False Then MsgBox "cancelled or typed in False" Else MsgBox "typed in : " & v & " (but can never display the typed-in value of 'false' here)" End If Another way to do it is to use the regular Inputbox, and use the little-known function StrPtr. Dim s As String s = InputBox("enter a value") If StrPtr(s) = 0 Then MsgBox "cancelled" Else MsgBox "Typed in : " & s End If -- Tim Zych www.higherdata.com Compare data in worksheets and find differences with Workbook Compare A free, powerful, flexible Excel utility "Chris D" wrote in message ... For the following script I get an inputbox for which the user has to enter a number: Dim result As Long result = Application.InputBox("Enter Number of Days which are in this report( highest number of worksheets in document", "Days in Report") Select Case result Case 1 Columns("T:CJ").Select Case 2 Columns("U:CJ").Select Case 3 Columns("V:CJ").Select Case Else MsgBox "please enter a valid number" End Select I would like the macro to exit the sub when "cancel" is selected. Can anyone help me with this? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code for MsgBox
Sub test()
Dim result As Long On Error GoTo endit result = InputBox("Enter Number of Days which are in this" & vbLf & _ "report( highest number of worksheets in document", "Days in Report") Select Case result Case 1 Columns("T:CJ").Select Case 2 Columns("U:CJ").Select Case 3 Columns("V:CJ").Select Case Else MsgBox "please enter a valid number from 1 to 3" End Select Exit Sub endit: MsgBox "You pressed Cancel or did not enter anything. Try again" End Sub Application.InputBox usually reserved for selecting ranges using mouse or typed reference. Gord Dibben MS Excel MVP On Sun, 1 Jun 2008 14:14:00 -0700, Chris D wrote: For the following script I get an inputbox for which the user has to enter a number: Dim result As Long result = Application.InputBox("Enter Number of Days which are in this report( highest number of worksheets in document", "Days in Report") Select Case result Case 1 Columns("T:CJ").Select Case 2 Columns("U:CJ").Select Case 3 Columns("V:CJ").Select Case Else MsgBox "please enter a valid number" End Select I would like the macro to exit the sub when "cancel" is selected. Can anyone help me with this? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code for MsgBox
You don't want to advise the user to try again if he has just done a Cancel,
so you will either have to pick up the False from Application.Msgbox or use StrPtr(strVar) = 0 with Msgbox. RBS "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Sub test() Dim result As Long On Error GoTo endit result = InputBox("Enter Number of Days which are in this" & vbLf & _ "report( highest number of worksheets in document", "Days in Report") Select Case result Case 1 Columns("T:CJ").Select Case 2 Columns("U:CJ").Select Case 3 Columns("V:CJ").Select Case Else MsgBox "please enter a valid number from 1 to 3" End Select Exit Sub endit: MsgBox "You pressed Cancel or did not enter anything. Try again" End Sub Application.InputBox usually reserved for selecting ranges using mouse or typed reference. Gord Dibben MS Excel MVP On Sun, 1 Jun 2008 14:14:00 -0700, Chris D wrote: For the following script I get an inputbox for which the user has to enter a number: Dim result As Long result = Application.InputBox("Enter Number of Days which are in this report( highest number of worksheets in document", "Days in Report") Select Case result Case 1 Columns("T:CJ").Select Case 2 Columns("U:CJ").Select Case 3 Columns("V:CJ").Select Case Else MsgBox "please enter a valid number" End Select I would like the macro to exit the sub when "cancel" is selected. Can anyone help me with this? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code for MsgBox
Thanks RBS
Just threw that in so's OP could see what would occur. No real need for the msgbox if just wants to exit sub. Gord On Sun, 1 Jun 2008 23:24:03 +0100, "RB Smissaert" wrote: You don't want to advise the user to try again if he has just done a Cancel, so you will either have to pick up the False from Application.Msgbox or use StrPtr(strVar) = 0 with Msgbox. RBS "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . Sub test() Dim result As Long On Error GoTo endit result = InputBox("Enter Number of Days which are in this" & vbLf & _ "report( highest number of worksheets in document", "Days in Report") Select Case result Case 1 Columns("T:CJ").Select Case 2 Columns("U:CJ").Select Case 3 Columns("V:CJ").Select Case Else MsgBox "please enter a valid number from 1 to 3" End Select Exit Sub endit: MsgBox "You pressed Cancel or did not enter anything. Try again" End Sub Application.InputBox usually reserved for selecting ranges using mouse or typed reference. Gord Dibben MS Excel MVP On Sun, 1 Jun 2008 14:14:00 -0700, Chris D wrote: For the following script I get an inputbox for which the user has to enter a number: Dim result As Long result = Application.InputBox("Enter Number of Days which are in this report( highest number of worksheets in document", "Days in Report") Select Case result Case 1 Columns("T:CJ").Select Case 2 Columns("U:CJ").Select Case 3 Columns("V:CJ").Select Case Else MsgBox "please enter a valid number" End Select I would like the macro to exit the sub when "cancel" is selected. Can anyone help me with this? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code for MsgBox
What I mean is that somehow you will need to differentiate between:
pressed Cancel or did not enter anything as you want a silent exit on Cancel, but a msg on not entering anything with a press on the OK button. RBS "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Thanks RBS Just threw that in so's OP could see what would occur. No real need for the msgbox if just wants to exit sub. Gord On Sun, 1 Jun 2008 23:24:03 +0100, "RB Smissaert" wrote: You don't want to advise the user to try again if he has just done a Cancel, so you will either have to pick up the False from Application.Msgbox or use StrPtr(strVar) = 0 with Msgbox. RBS "Gord Dibben" <gorddibbATshawDOTca wrote in message . .. Sub test() Dim result As Long On Error GoTo endit result = InputBox("Enter Number of Days which are in this" & vbLf & _ "report( highest number of worksheets in document", "Days in Report") Select Case result Case 1 Columns("T:CJ").Select Case 2 Columns("U:CJ").Select Case 3 Columns("V:CJ").Select Case Else MsgBox "please enter a valid number from 1 to 3" End Select Exit Sub endit: MsgBox "You pressed Cancel or did not enter anything. Try again" End Sub Application.InputBox usually reserved for selecting ranges using mouse or typed reference. Gord Dibben MS Excel MVP On Sun, 1 Jun 2008 14:14:00 -0700, Chris D wrote: For the following script I get an inputbox for which the user has to enter a number: Dim result As Long result = Application.InputBox("Enter Number of Days which are in this report( highest number of worksheets in document", "Days in Report") Select Case result Case 1 Columns("T:CJ").Select Case 2 Columns("U:CJ").Select Case 3 Columns("V:CJ").Select Case Else MsgBox "please enter a valid number" End Select I would like the macro to exit the sub when "cancel" is selected. Can anyone help me with this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW TO PUT MSGBOX CODE IN MACRO | Excel Programming | |||
Msgbox Code Help Please.... | Excel Discussion (Misc queries) | |||
Code for MsgBox | Excel Programming | |||
VBA code using if then and msgbox | Excel Discussion (Misc queries) | |||
syntax for code in MsgBox()? | Excel Programming |