Tom’s Tutorials For Excel: Capturing Cancel With InputBoxes

Tom’s Tutorials For Excel: Capturing Cancel With InputBoxes

In VBA, there are two kinds of InputBoxes: The InputBox Function and the InputBox Method. Visually, they appear similar and in fact serve most of the same purposes, with the InputBox Method providing optional argument parameters for specifying inputs for text, or numbers, or ranges.

For today, the focus is how to make the Cancel button work for you if the user clicks it. These two examples can give you some ideas as to how to extend the user’s experience by telling them which button they clicked and what the result of that action is.

In the first macro using the InputBox Function, the undocumented StrPtr function is employed in the Select Case structure as you can see in the following macro:

Sub InputBoxFunctionExample()
Dim myInput As String
myInput = InputBox("Please enter your name:", "Name")
Select Case True
Case StrPtr(myInput) = 0
MsgBox "You clicked Cancel.", 48, "Entry cancelled."
Exit Sub
Case Len(myInput) = 0
MsgBox "You hit OK but entered nothing.", 48, "No input was made."
Exit Sub
Case Else
MsgBox "You entered ''" & myInput & "''.", 64, "Thank you!"
End Select
End Sub

In this next example for the InputBox Method, the Cancel button is utilized to recognize when the user clicked Cancel instead of just clicking OK without having input anything.

Sub InputBoxMethodExample()
Dim CancelTest As Variant
showInputBox:
CancelTest = Application.InputBox("Enter a value, or click Cancel to exit:")
If CancelTest = False Then
MsgBox "You clicked the Cancel button, Input Box will close.", 64, "Cancel was clicked."
Exit Sub
ElseIf CancelTest = "" Then
MsgBox "You must click Cancel to exit.", 48, "You clicked Ok but entered nothing."
GoTo showInputBox
Else
MsgBox "You entered " & CancelTest & ".", 64, "Thank you!"
End If
End Sub