Module 5 - Messages and User Inputs
Lesson 5.3 - The VBA Input Box
Topic 5.3.3 - Cancelling an Input Box

Up to this point we've assumed that the user always clicks OK on the input box.  This section explains what happens if Cancel is clicked instead.

Clicking cancel

What happens to the deep and profound answer we've written if we click the Cancel button?

 

Files Needed

Click here to download the file you need for the first part of this topic.

Completed Code

You can click here to download a file containing the sample code.

Cancelling an Input Box

Extract and open the file linked to in the Files Needed section above.  In the VBE, find the subroutine called CancellingAnInputBox in Module1. The code in the subroutine asks the user to enter their name and then presents a message box with a simple greeting:

Basic code

The basic code to ask a question and display a response.

 

If you click Cancel on an input box it will return an empty string, regardless of whether you've already typed something:

Cancelling input

You can click Cancel to abandon the input box even if you've already typed something.

 

Cancelling the input box doesn't prevent the rest of the procedure from running.  In our simple example, the message box will still appear but without displaying a name:

Empty message

The message box concatenates "Hello " with the empty string stored in the variable.

 

Testing for an Empty String

You can test if the input box has been cancelled by checking if the variable contains an empty string:

Test for empty string

This code will display a different message and exit from the procedure if the user cancels the input box.

 

Cancelling the input box this time will result in a different message appearing:

Error message

This is preferable to the partially complete message from earlier.

 

To practise cancelling an input box:

  1. Click here to download the file needed for this section of the topic.  Extract the contents of the zip file and open the file called Actors.xlsm.
  2. Try using the Actor Search button to find an actor:
Search for an actor

Click the Actor Search button, enter a name in the input box and click OK.

 
  1. The result will be a message box showing you details of the actor you've searched for (or a warning if the actor's name isn't in the list):
Message

You should see something similar to this if the actor you have searched for is in the list.

 
  1. Try searching for another actor but this time click the Cancel button on the input box:
Useless message

This fairly useless message appears if you cancel the input box.

 
  1. In the VBE, find the subroutine called ActorSearch in Module1.
  2. Find the section labelled with the comment TODO and add code to check if the SearchName variable contains an empty string:

Sub ActorSearch()

 

Dim SearchName As String

Dim ActorDoB As Date

Dim ActorCell As Range

 

'Ask the user which name to search for

SearchName = InputBox( _

"Enter an actor name")

 

'TODO

'Test if the SearchName variable contains an empty string

'If so, show a message and exit the procedure

If SearchName = "" Then

 

End If

  1. Add code to the If statement to display a message to the user and then exit from the procedure:

Sub ActorSearch()

 

Dim SearchName As String

Dim ActorDoB As Date

Dim ActorCell As Range

 

'Ask the user which name to search for

SearchName = InputBox( _

"Enter an actor name")

 

'TODO

'Test if the SearchName variable contains an empty string

'If so, show a message and exit the procedure

If SearchName = "" Then

 

MsgBox _

"You didn't enter a name!", _

vbExclamation

 

Exit Sub

 

End If

  1. Run the subroutine and check that everything works as intended.
Error message

Your user should now be able to tell what went wrong.

 
  1. Save and close the file.  You can click here to download the completed code.
This page has 0 threads Add post