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.4 - The Excel Input Box

Topic 5.4.7 - Forcing Cell Selection

Although you can ask users to select a cell and read its value into a variable, there's nothing to stop them from simply typing in a value:

Although we've asked the user to pick a film, there's nothing to stop them from typing any value into the input box. Sadly, although the value shown here is the correct answer, it isn't one of the films available in the list.

Files Needed

Click here to download the file needed for this part of the lesson.

Completed Code

You can click here to download a workbook containing the completed code.

Extract and open the workbook linked to in the Files Needed section above.

Setting the Return Type of an Input Box

If you'd like to prevent your users from typing any value into an Excel input box, you can set its Type to 8. In Module1 find the subroutine called ForceCellSelection. Edit the code to set the Type parameter of the InputBox function to 8:

A Type of 8 means that the input box is expecting a value in the form of a cell reference.

You can select a cell as normal when the input box is displayed:

There is a subtle difference in the way the cell reference appears in the input box: it lacks the = sign in front of the address.

The message box will appear as normal when you click OK on the input box:

The message box will show the value of the selected cell, not its cell reference.

Entering an Invalid Reference

You can still attempt to type a value into the input box:

We're attempting to type the name of the film, rather than selecting the cell containing it.

After clicking OK on the input box, you'll see a rather long warning message (show in part below):

You'll be warned if you enter anything into the input box which isn't a valid cell reference.

You also need to be careful to select only a single cell in the example shown above: selecting multiple cells will cause a run-time error. A later part of the lesson explains how to avoid this by capturing multiple cells as a range object.

To practise forcing cell selection:

Find and run the MoviePicker subroutine in Module1.
Try typing in the names of films rather than selecting cells containing them:

There's nothing to stop you entering any film name, even if it's not in the list.

Edit the code to set the Type parameter of each input box to a value of 8:

Sub MoviePicker()

Dim BestFilm As String

Dim WorstFilm As String

BestFilm = Application.InputBox( _

Prompt:="Pick the best film", _

Type:=8)

If BestFilm = "False" Then Exit Sub

WorstFilm = Application.InputBox( _

Prompt:="Pick the worst film", _

Type:=8)

If WorstFilm = "False" Then Exit Sub

wsMovies.Range("B16").Value = BestFilm

wsMovies.Range("B17").Value = WorstFilm

End Sub

Run the subroutine and try to type a film name into the first input box:

You'll see a warning message appear if you type in a value which can't be evaluated as a cell reference.

Try selecting cells to populate the input boxes to make sure that your code still works when used properly.

You should still be able to populate these cells by selecting film names in the list.

Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

Setting the Return Type of an Input Box

Entering an Invalid Reference

Head office

London

Manchester