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?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.