The Example Workbook

Extract and open the workbook linked to in the Files Needed section above.  You'll find a workbook which allows you to search for hit and flop films by clicking buttons on the Menu worksheet:

Click the Find Hit Film button and enter the name of a film into the input box which appears.

When the film you have searched for is found, you'll see a message providing you with information:

You'll see a message similar to this one when the film name is found.

When the film cannot be found, an error handler ensures that you see a user-friendly error message rather than a standard run-time error message:

This is the type of message you'll see when the film name is not found in the list.

When you click OK on the error message the subroutine simply ends.  We'd like to provide the user with a choice to perform another search immediately.

Providing the User with a Choice

The first step is to modify the error message to provide the user with a choice of buttons to click and to capture their response in a variable.  In the VBE, find the Find_Hit_Film subroutine and declare a variable to capture the user response at the top of the procedure:

Use the VbMsgBoxResult type to declare the variable.

Scroll to the bottom of the subroutine and find the error handling section which begins with the FilmNotFound line label:

Find this section at the bottom of the subroutine.

Modify the message box so that it displays Retry and Cancel buttons and assign the result of the MsgBox function to the variable you have just declared:

Your code should now look like this.

You can test the code by clicking the Find Hit Film button on the Menu worksheet and entering some text which will not be found in the list on the Hits worksheet:

The user now has a choice of button to click when the film they have searched for does not exist.

Resuming at a Specific Line

Now that the user has a choice of button to click we can test their response and take the appropriate action.  If the user clicks the Retry button, we want to return to the line of code which asked them to enter a film name:

The line which causes the run-time error is shown highlighted in yellow. The line we want to resume at is shown highlighted in blue.

If we wanted to return to the line which caused the run-time error we could use the Resume statement.  If we wanted to return to the line after the one which caused the error we could use the Resume Next statement.  In this case, however, we want to return to a different line of code entirely.  In order to locate this line we can add a line label immediately above it:

Add a line label above the instruction which displays the input box.

In the error handling section we can use an If statement to test if the user clicked the Retry button:

Add an If statement to the end of the error handling section as shown here.

If the condition is met, we can use the Resume statement to instruct the code to return to the line label we have just created:

Enter Resume followed by the line label at which you want to continue running the code.

We can complete the If statement using an Else clause which will return the user to the Menu worksheet and then end the procedure if they do not click Retry:

If the user does not click the Retry button, the code will return the user to the Menu worksheet and the subroutine will end.

Testing the System

You can test that the system works by returning to the Menu sheet and clicking the Find Hit Film button.  Try entering the name of a film which doesn't exist in the Hits sheet:

Enter the name of a film and click OK on the input box.

Click Retry on the message box which appears:

Click the button shown here.

You should find that you are prompted to enter a new film name immediately:

You can enter a new film name and click OK to ensure that the system behaves as expected.

You'll see a message similar to this one when the film you are looking for is found in the list.

Test the system again but this time click the Cancel button when the error message appears:

Click the button shown here when the error message appears.

This time you will be taken back to the Menu worksheet.

To practise resuming at a specific line of code:

1. Find the Find_Flop_Film subroutine in Module1 and declare a new variable which can hold the result of a message box:

Declare a VbMsgBoxResult variable.

2. Scroll down to the error handling section marked with the NoFilmWasFound line label:

Scroll to this section of code.

3. Alter the code to capture the result of the message box in the variable you have declared and display Retry and Cancel buttons on the message:

Alter the code as shown here.

4. Scroll up to find the instruction which displays an input box.  Add a line label above this instruction:

Enter a line label above the instruction which displays the input box.

5. Return to the error handling section at the bottom of the subroutine and add an If statement to check if the user clicked the Retry button:

Begin the If statement as shown here.

6. If the condition is met, add an instruction to return to the line label that you have just created:

Resume at the line label you have just created.

7. Complete the If statement with an Else clause which selects the Menu worksheet if the user doesn't click Retry:

Complete the If statement as shown here.

8. Test that the system behaves as expected then save and close the workbook.