VBA code for MsgBox

[Chris D]

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?

 

[RB Smissaert]

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

 

[Norman Jones]

Hi Chris,

Try changing your result variable to a
variant, which will wenabl you to trap a
cancel operation.

Try, therefore:

'==========>>
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
'<<==========

 

[Tim Zych]

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

 

[Gord Dibben]

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

 

[RB Smissaert]

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]

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

 

[RB Smissaert]

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