MODULES▼
LESSONS▼
TOPICS▼
One of the interesting things about the Excel input box is that it is modeless. This means that you can click on cells in the Excel window while the input box is displayed on screen:

Clicking on a cell will read its address into the input box.
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.
Download and extract the workbook linked to in the Files Needed section above.
Returning the Contents of a Cell
You can use an Excel input box to ask the user to select a cell and capture its contents in a variable. Insert a new module and create a subroutine called ReturnCellContents. Add code to return the result of an Excel input box to a String variable as shown below:

Here we're using a String variable and have not specified the Type parameter of the input box.
When you execute the code show above, you can click on a cell while the input box is displayed:

Click on a cell to select it and read its address into the input box. Click OK to return the cell's contents to the variable.
After clicking OK on the input box, a message box will appear which shows the value of the selected cell:

The message box shows the value captured in the variable.
Selecting Multiple Cells
You can select multiple cells when the input box is displayed:

Here we've selected a block of six cells.
The results of this selection may not be quite what you expect:

The variable only captures the value of the top left cell in the selection.
You can also select non-adjacent cells by holding Ctrl while clicking on them:

Hold Ctrl and click to select non-adjacent cells.
You won't be able to proceed with such a selection, however:

This is the message you'll see if you select non-adjacent cells.
Selecting Numbers
You can set the Type parameter of the Excel input box to 1, which means that it will only accept numeric values. Create a new subroutine called SelectANumber. Add code to return the result of an Excel input box to a Double variable as shown below:

Set the Type parameter to 1 to make sure the input box returns a number.
After executing the code shown above, you can select a cell containing a number:

Select any cell containing a number.
After clicking OK on the input box, a message box appears and shows the value that was selected:

This basic message box shows that the value of the cell you selected was captured in the variable.
You can try to select a cell which doesn't contain a number:

This cell clearly doesn't contain a number.
You'll see the standard warning message whenever you enter a non-numeric value into an input box with a Type of 1:

Maybe try reading the instructions next time?
To practise using an input box to select cells:
- Using the same module, create a new subroutine called MoviePicker.
- Declare a String variable and populate it using an Excel input box which asks the user to pick the best film:
Sub MoviePicker()
Dim BestFilm As String
BestFilm = Application.InputBox( _
Prompt:="Pick the best film")
End Sub
- Repeat this to ask the user to pick the worst film:
Sub MoviePicker()
Dim BestFilm As String
Dim WorstFilm As String
BestFilm = Application.InputBox( _
Prompt:="Pick the best film")
WorstFilm = Application.InputBox( _
Prompt:="Pick the worst film")
End Sub
- Step through the procedure with the F8 key and use the Locals window to check that your choices are being captured:

There's only really one choice for worst film.
- Add code to place the values you have captured into the appropriate cells:
Sub MoviePicker()
Dim BestFilm As String
Dim WorstFilm As String
BestFilm = Application.InputBox( _
Prompt:="Pick the best film")
WorstFilm = Application.InputBox( _
Prompt:="Pick the worst film")
wsMovies.Range("B16").Value = BestFilm
wsMovies.Range("B17").Value = WorstFilm
End Sub
- Run the procedure and check that the values you selected appear in the correct cells:

Your selected films should appear as shown here.
- Add two If statements which will exit the procedure if the user cancels either input box:
Sub MoviePicker()
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
End Sub
- Save and close the workbook.