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
Your blog won’t show up properly on my iphone 3gs – you might want to try and repair that
Thanks for letting me know. When you say the blog won’t show up properly, what exactly do you see, or not see?
This is good code for getting text input, but as you pointed out, the InputBox can also request a range, eg Set MyRange = Application.InputBox(“Click a cell.”, “Select Next Cell”, Type:=8)
Clicking “Cancel” causes the Set statement to fail.
This works:
On Error Resume Next
Set MyRange = Application.InputBox(“Click a cell.”, “Select Next Cell”, Type:=8)
‘ Check for user clicking Cancel
If MyRange Is Nothing Then
Call Cleanup
End
End If
On Error GoTo 0
Thanks Don