How to Handle VBA InputBox Cancel Button in Excel: 2 Methods

1. Use StrPtr Function & If ElseIf Statement to Show If User Clicked Cancel Button in VBA Inputbox

Insert the following code into your module.

Sub StrPtr_Function()
Dim Month As String
Dim cell_range As Range
Month = Inputbox("Enter Month", "Sales Analysis")
Set cell_range = Range("C11")
'using StrPtr function and if elseif statement
If StrPtr(Month) = 0 Then
MsgBox "You Clicked Cancel."
ElseIf Len(Month) = 0 Then
MsgBox "Please Enter a Value."
Else
'using nested if-else loop
If cell_range.Cells(1, 1) = "" Then
cell_range.Cells(1, 1).Value = Month
Else
MsgBox "You already have a value here"
End If
End If
End Sub

Code Breakdown

In the code, we declared Month as String and cell_range as Range.
We used the InputBox function where we inserted “Enter Month” as prompt, “Sales Analysis” as Title and then assigned the value as Month.
Set Cell C11 as cell_range.
We checked If the value of Month is empty using the StrPtr function, and if it returns True then the MsgBox will appear with a prompt as “You Clicked Cancel.”
If the length of Month is equal to 0 then the MsgBox will appear with a prompt as “Please Enter a Value.” To count the length of the string we used the Len Function.
We used a nested If Else statement to Check if the cell_range is empty or not. The value of Month will be inserted in that cell_range otherwise it will return a Msgbox with a prompt as “You already have a value here.”
Click on the run button to see the following MsgBox.
You can handle the Cancel button in VBA InputBox.

Method 2 – Apply Select Case Statement to Handle VBA InputBox with Cancel Button in Excel

Insert the following code into your module

Sub Select_Case()
Dim Month As String
Dim cell_range As Range
Month = Inputbox("Please enter Month:", "Sales Analysis")
Set cell_range = Range("C11")
'using select case statement
Select Case True
Case StrPtr(Month) = 0
MsgBox "You Clicked Cancel."
Case Len(Month) = 0
MsgBox "Please Enter a Value."
Case Else
cell_range.Cells(1, 1).Value = Month
End Select
End Sub

We used the Select Case Statement for 3 different cases. Check if the value of Month is empty, check if the value of Month is a null string, and insert the value of Month in Cell C11.

Run your code to show a message in Msgbox if the user clicked in Cancel or entered a null string. Return the user input value in Cell C11.

How to Use VBA InputBox Cancel Button to Exit Sub in Excel

Method 1 – Use IF Statement to Exit Sub Procedure When User Clicked Cancel Button in InputBox

To use this method, insert the following code into your module.

Sub If_Statement_Exit_Sub()
Dim Month As String
Dim cell_range As Range
Month = Inputbox("Please enter Month:", "Sales Analysis")
Set cell_range = Range("C11")
'use if statement to return a msgbox when you click cancel
If StrPtr(Month) = 0 Then
MsgBox ("You Clicked Cancel.")
'use exit sub
Exit Sub
End If
cell_range.Cells(1, 1).Value = Month
End Sub

We used the If statement to check if the value of Month is zero, return a MsgBox with prompt “You Clicked Cancel” and it will Exit the sub procedure and the remaining code will not execute.

Click on the run button to execute the code.

Method 2 – Employ On Error Statement to Use VBA InputBox Cancel Button to Exit Sub

The On Error Statement is used to handle errors in a code. Using this statement, you can handle any error that may occur while running the code. Employ the On Error Statement to use the VBA InputBox Cancel button with Exit Sub.

Sub On_Error_Statement()
Dim cell_range As Range
Set cell_range = Range("B5")
'using on error statement
On Error GoTo ErrorHandler
'using Application.inputbox to create an inputbox
Set product = Application.Inputbox("Input range", _
"Number of Products", Selection.Address, Type:=8)
cell_range.Cells(7, 2).Value = product.Rows.Count
Exit Sub
'using ErrorHandler when you clicked on cancel
ErrorHandler:
MsgBox ("You clicked on Cancel button.")
End Sub

We used the On Error statement to specify that if an error occurs during code execution, the program should jump to the ErrorHandler section of the code. It will jump to ErrorHandler when the user clicks on the Cancel button. The ErrorHandler section will display a MsgBox that informs the user that they clicked on the Cancel button.

InputBox vs Application.InputBox in Excel VBA

The following chart highlights the key differences between Excel VBA’s InputBox and Application.InputBox:

Arguments	InputBox	Application.InputBox
Default Prompt	Type a value:	“” (empty string)
Default Title	InputBox	“” (empty string)
Default Value	“” (empty string)	“” (empty string)
Type of Return Value	String	Variant
Optional Parameters	None	Yes
Help Button	No	Yes

Note that both functions display a modal dialog box that prompts the user to enter data. The Application.InputBox is more flexible than InputBox because it allows for more customization. You can specify the type of data the user must enter, such as a date or a number. You can provide help text or error messages to guide the user through the input process.

Download Practice Workbook

You can download the workbook to practice yourself.

Handle InputBox Cancel Button.xlsm

Get FREE Advanced Excel Exercises with Solutions!