Excel-VBA Solutions

Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Detect VBA InputBox Cancel - Two Solutions

We often use InputBox to get inputs from users. But do you know how to detect if a user cancels an InputBox. If you don’t detect it then it may cause some errors. Because if you have lines of codes after the InputBox, and if you don’t detect the cancel and handle it accordingly, then the program will execute the lines of codes after the InputBox method. And this can cause various errors. So you should always detect InputBox cancel and handle it accordingly when you use this method.

So let’s look at how we can detect when a user cancels an InputBox. Let’s consider this sample subroutine.

Sub AskUserName()

     Dim Response As String

     Response = InputBox("Enter the user name:", "Username")

     Debug.Print "Hello " & Response & "!"

End Sub

If we run this, the program will ask to enter the username.

Get the username via InputBox

Then when we click “OK”, the program will print this in the immediate window.

Print username in the immediate window

However if we click the “Cancel” button, the program still prints in the immediate window.

Even user click cancel button the program still print in the immediate window

But if the user clicks the “Cancel” button, the program should not print anything in the immediate window. So how do we modify the subroutine to do that. When a user clicks the Cancel button, the InputBox method returns an empty string. So we can use that returned value to detect the cancellation action.

Sub AskUserName()

     Dim Response As String

     Response = InputBox("Enter the user name:", "Username")

     If Len(Response) = 0 Then
          Exit Sub
     Else
          Debug.Print "Hello " & Response & "!"
     End If

End Sub

So in the above code first we check the length of the string. If it is 0 then we exit the subroutine using the Exit statement. However there is a small problem in this method. If a user clicks the OK button without entering anything then the program still sees it as a cancellation. Because the InputBox method returns an empty string as the user didn’t enter anything. So if you want to act differently, for the user clicks cancel than to when the user clicks ok without entering anything, then you can’t use the above method. If you want to treat the user in different ways for those two actions, then you should use your own userform and handle the cancellation as desired. This second method explains how to do that.

First go to the VBA editor and insert a userform.

Open VBA editor and click insert
Insert Userform

Then add label, textbox and two command buttons to the form and format them to your preference.

Also see
How to add labels to VBA Userforms
How to Add Textboxes to VBA Userforms
How to Format Label Text in VBA Forms (To Give Professional Look)

Userform with OK and cancel buttons

In this method you have more freedom. You can apply any colors you want and also add additional buttons if needed. Next, insert a module.

Insert a module

Then add this code to the module.

Sub Main()

     UserForm1.Show

End Sub

When you run the “Main” Sub procedure, it will show the form. We can add the following code to the OK button of the form.

Private Sub cmdOK_Click()

     Debug.Print "Hello " & txtUsername.Value & "!"

End Sub

So this will print the username when you click the OK button. Finally we can add below code to the cancel button.

Private Sub cmdCancel_Click()

     Unload Me

End Sub

Now the form will close when the user clicks the cancel button.

Contact Form

Name

Email *

Message *