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.

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

However if we click the “Cancel” button, the program still prints 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.

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)

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.

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.