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