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.
Click here to download the file needed for this part of the lesson.
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:
Dim BestFilm As String
Dim WorstFilm As String
BestFilm = Application.InputBox( _
Prompt:="Pick the best film", _
If BestFilm = "False" Then Exit Sub
WorstFilm = Application.InputBox( _
Prompt:="Pick the worst film", _
If WorstFilm = "False" Then Exit Sub
wsMovies.Range("B16").Value = BestFilm
wsMovies.Range("B17").Value = WorstFilm
- 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.