Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

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:

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.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

Returning the Contents of a Cell

Selecting Multiple Cells

Selecting Numbers

Head office

London

Manchester