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?

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?

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?

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?

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?

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?

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?

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?