MODULES
LESSONS
TOPICS
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.
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:
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:
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:
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:
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:
This is preferable to the partially complete message from earlier.
To practise cancelling an input box:
- 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.
- Try using the Actor Search button to find an actor:
Click the Actor Search button, enter a name in the input box and click OK.
- 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):
You should see something similar to this if the actor you have searched for is in the list.
- Try searching for another actor but this time click the Cancel button on the input box:
This fairly useless message appears if you cancel the input box.
- In the VBE, find the subroutine called ActorSearch in Module1.
- 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
- 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
- Run the subroutine and check that everything works as intended.
Your user should now be able to tell what went wrong.
- Save and close the file. You can click here to download the completed code.