Sleeper: Input Box - Exit Sub on Cancel

[malik641]

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]

Check out this Kb Entry for an example on how to determine if Cancel was pressed or not.

[malik641]

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.

[Jacob Hilderbrand]

It works for me. False is when you press Cancel.

[malik641]

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]

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

Maybe someone should state that in the KB Entry.



Anyway, thanks DRJ works great now!

[Bob Phillips]

See this recent thread on the same topic

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

[malik641]

Interesting...

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