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:

Type name

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:

Type 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:

Select cell

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:

Message 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:

Typing value

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):

Warning

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:

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

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

 
  1. 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

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

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

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

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

 
  1. Save and close the workbook.
This page has 0 threads Add post