How to Handle VBA InputBox Cancel Button in Excel (2 Examples)

Looking for ways to handle VBA InputBox Cancel button in Excel? Then, this is the right place for you.

We normally use VBA InputBox to prompt the user for input. It is commonly used in VBA macros to collect data from the user, such as a value or a text string, without the need for a custom user form.

An InputBox by default displays two buttons: OK button and Cancel button. When a user clicks on the OK button, the input that they entered into the box is returned to the VBA code as a string. But when the user clicks on the Cancel button, the InputBox function returns a null value. Thus, the user can not differentiate if he entered a null value or clicked on the Cancel button.

However, this situation can be handled using some methods. In this article you will find some easy examples showing how you can handle the VBA InputBox Cancel button in Excel.

Here, you can see that we have added MsgBoxes to show the user when he clicked the Cancel button and when he inserted a null value.

How to Launch VBA Editor in Excel

To write a VBA code in Excel, you have to launch the VBA editor. For launching this VBA editor, you must need the Developer tab, now if you don’t see the Developer tab then you have to enable the Developer tab.

You can insert your code into a module following the steps given below.

• Firstly, go to the Developer tab >> click on Visual Basic.

• Now, the Microsoft Visual Basic Editor will open.
• Lastly, to add a module, click on Insert >> select Module.

Introduction to VBA InputBox in Excel

• Function Objective:

This function returns an InputBox with some kind of criteria.

• Syntax:

InputBox(Prompt, [Title], [Default], [Xpos], [Ypos], [Helpfile], [Context] )

• Arguments Explanation:

• Return Parameter:

The function returns a box to input data along with the defined buttons.

Read More: How to Use Excel VBA InputBox with Example

What Happens When the User Clicks on the Cancel Button of VBA InputBox in Excel?

Suppose we have a dataset containing information of some Products and their Sales value. We can insert the Month on which we have these sales values by using an Inputbox.

Sub Inputbox1()
Dim Month As String
Dim cell_range As Range
Month = Inputbox("Please enter Month:", "Sales Analysis")
Set cell_range = Range("C11")
cell_range.Cells(1, 1).Value = Month
End Sub

Using this code you can insert a string value in Cell C11 using InputBox. However, if you click on the Cancel button or insert nothing on the InputBox and then click on OK the code simply runs each time and can not differentiate these two conditions.

Video to show what happens when user clicks on Cancel button or inserts null string in InputBox

You can use any of the following methods to handle the Cancel button in VBA InputBox.

2 Examples to Handle VBA InputBox with Cancel Button in Excel

Here, we will show you 3 examples to handle VBA InputBox with Cancel button in Excel using StrPtr function and If-ElseIf Statement, Select Case Statement and employing Split function to insert Array.

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

StrPtr function returns the memory address of a string. It can be used to differentiate between an empty string and null string. When a user clicks on Cancel it returns an empty string and when user clicks on OK button without inserting any value it returns a null string. So, by using this function, we can easily handle the Cancel button in an InputBox.

Now, we will use the If ElseIf statement to differentiate when you clicked on the Cancel button and when you inserted a null string. You can use If Elseif statements to make decisions based on multiple conditions, allowing for more complex decision-making. In fact, you can also use nested If statements when necessary. To use this method follow the steps given below.

• 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

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

In this example, we will apply the Select Case Statement to handle VBA InputBox with Cancel button in Excel. Select Case statements can be easier to read than If-Elseif statements, especially if you have a lot of conditions to check. However, it does not allow you to use the nested conditions. Here are the steps to following this method.

• 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

• After that, run your code to show a message in Msgbox if the user clicked in Cancel or entered a null string. Otherwise, return the user input value in Cell C11.

Read More: How to Use VBA Input Box with Buttons in Excel

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

The Exit Sub statement is used to immediately exit a Sub procedure. When the Exit Sub statement is executed, any remaining statements in the current procedure are skipped. It can be used for error handling or conditional statements.

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

You can use the If statement to Exit Sub procedure for conditional statements. In this example, we will show you how you can exit sub when the user clicks the Cancel button in InputBox modifying the previous code we used in Example-1.

• 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

• Now, click on the run button to execute the code.

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

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. Now, we will 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

Read More: Excel VBA: Input Box with Options

InputBox vs Application.InputBox in Excel VBA

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

Note that both functions display a modal dialog box that prompts the user to enter data. However, Application.InputBox is more flexible than InputBox because it allows for more customization. For example, you can specify the type of data that the user must enter, such as a date or a number. Additionally, 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.

Conclusion

In this article, we tried to show you some easy ways to handle the VBA InputBox Cancel button in Excel through 3 examples. We also showed the use of the Exit Sub statement in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here.

Related Articles

• How to Create Yes-No InputBox with Excel VBA
• Excel VBA: Create InputBox with Multiple Inputs
• Excel VBA InputBox with Multiple Lines
• Excel VBA Input Box with Drop Down List
• Excel VBA: InputBox with Password Mask