PDA

View Full Version : Sleeper: Input Box - Exit Sub on Cancel



malik641
09-20-2005, 11:05 AM
What's the code to exit a sub when you press Cancel for an Input box?

Just a short example:

Sub InputBox()
Dim WSName As String
WSName = Application.InputBox("What is the Sheet Name?", Type:=2)
If WSName = "" Then
MsgBox "Sheet will not be created. There is no name for it.", _
vbOKOnly + vbExclamation, "No new sheet"
Exit Sub
End If
Msgbox "Still Running"
End Sub

If you press cancel the macro continues, and I want it to exit the sub.

Thanks in advance!

Jacob Hilderbrand
09-20-2005, 11:11 AM
Check out this Kb Entry (http://vbaexpress.com/kb/getarticle.php?kb_id=119) for an example on how to determine if Cancel was pressed or not.

malik641
09-20-2005, 11:29 AM
That works for VBA's Input box function, but what about Excel's Input box method?

If you change the code to this:


Sub InputBoxNew()
Dim Temp$
Temp = Application.InputBox("Enter something here:", "Inputbox", Type:=2)
If StrPtr(Temp) = 0 Then
MsgBox "You pressed Cancel!" 'Option 1
Else
If Temp = "" Then
MsgBox "You entered nothing and pressed OK" ' Option 2
Else
MsgBox Temp 'Option 3
End If
End If
End Sub

It doesn't work, you end up with Option 3 and Temp is False.

I was trying to change the code to correct it, but I'm having a hard time figuring it out. :dunno

Jacob Hilderbrand
09-20-2005, 11:32 AM
It works for me. False is when you press Cancel.

malik641
09-20-2005, 11:39 AM
It works for me. False is when you press Cancel.
Yeah, but when you press cancel the KB Entry says that you're supposed to get a MsgBox saying "You pressed Cancel!" (Option 1 in KB Entry), not a MsgBox that says "False" (which would be option 3 in the KB Entry).

Right?

malik641
09-20-2005, 11:47 AM
Okay, I got it.

I had to get rid of the StrPtr function and change 0 to "False"
Like this:


Using Excel's InputBox method:


Sub InputBoxNew()
Dim Temp As String
Temp = Application.InputBox("Enter something here:", "Inputbox", Type:=2)
If Temp = "False" Then
MsgBox "You pressed Cancel!" 'Option 1
Else
If Temp = "" Then
MsgBox "You entered nothing and pressed OK" ' Option 2
Else
MsgBox Temp 'Option 3
End If
End If
End Sub


Using VBA's InputBox function (from KB Entry):


Sub InputBoxNew()
Dim Temp As String
Temp = InputBox("Enter something here:", "Inputbox")
If StrPtr(Temp) = 0 Then
MsgBox "You pressed Cancel!" 'Option 1
Else
If Temp = "" Then
MsgBox "You entered nothing and pressed OK" ' Option 2
Else
MsgBox Temp 'Option 3
End If
End If
End Sub

That's strange how it works differently for each one, huh :think:

Maybe someone should state that in the KB Entry.



Anyway, thanks DRJ works great now! :thumb

Bob Phillips
09-20-2005, 11:51 AM
See this recent thread on the same topic

http://vbaexpress.com/forum/showthread.php?t=5211

malik641
09-20-2005, 01:13 PM
Interesting...:think:

Why did excel even bother with two different types of Input boxes anyway?