Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

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.

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.

This page has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

MODULES

LESSONS

TOPICS