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
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
MsgBox "You entered " & CancelTest & ".", 64, "Thank you!"
End If
End Sub
Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , ,
5 comments on “Tom’s Tutorials For Excel: Capturing Cancel With InputBoxes
  1. Your blog won’t show up properly on my iphone 3gs – you might want to try and repair that

  2. Don B says:

    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.

  3. Don B says:

    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 If
    On Error GoTo 0

Leave a Reply to pandora house 925 charms Cancel reply

Your email address will not be published. Required fields are marked *