564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
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.
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:
There's nothing to stop you entering any film name, even if it's not in the list.
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
You'll see a warning message appear if you type in a value which can't be evaluated as a cell reference.
You should still be able to populate these cells by selecting film names in the list.
25 Aytoun Street