Module 5 - Messages and User Inputs
Lesson 5.4 - The Excel Input Box
Topic 5.4.6 - Selecting Cell Contents

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:

Selecting cell

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:

Capture contents

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

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:

Message box

The message box shows the value captured in the variable.

 

Selecting Multiple Cells

You can select multiple cells when the input box is displayed:

Multiple cells

Here we've selected a block of six cells.

 

The results of this selection may not be quite what you expect:

Top left

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:

Non adjacent cells

Hold Ctrl and click to select non-adjacent cells.

 

You won't be able to proceed with such a selection, however:

Error message

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:

Type 1

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

Message box

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:

Select non 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:

Warning message

Maybe try reading the instructions next time?

 

To practise using an input box to select cells:

  1. Using the same module, create a new subroutine called MoviePicker.
  2. 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

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

  1. Step through the procedure with the F8 key and use the Locals window to check that your choices are being captured:
Choices

There's only really one choice for worst film.

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

  1. Run the procedure and check that the values you selected appear in the correct cells:
Cells

Your selected films should appear as shown here.

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

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