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.8 - Returning a Range Object

In the previous part of this lesson you saw how to use an Excel input box to return the contents of a cell. Even more usefully, you can use the input box to capture a reference to the range object that you select:

You can select any combination of cells on a worksheet. Rather than returning the contents of the cells you can capture a reference to the range object.

Files Needed

You can 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.

Extract and open the workbook linked to in the Files Needed section above.

Capturing a Reference to a Range

You can capture a reference to a range object by setting the Type of input box to 8. You can then return the result of the Excel InputBox function to a Range variable using the Set statement. Open the VBE and in Module1 create a subroutine called SelectARange. Add code to the subroutine as shown below:

Use code similar to this to capture a reference to a range object.

In Excel, select Sheet1 and then run the SelectARange subroutine. Select a range of cells on the worksheet:

Select a range of cells on Sheet1.

If you cancel an input box which returns a reference to a range object, you'll receive a run-time error. You can write error-handling code to solve this issue and you'll learn how to do this in the Error Handling module.

Using the Range Returned

Once you've captured a reference to the range you've selected, you have access to all of its methods and properties via the variable. Add code to the SelectARange subroutine to change the fill colour of the selected cells:

You can use any of the methods and properties of a range object.

Running the code shown above produces results similar to those in the image below:

Not the most practical of uses for this technique, but it demonstrates the principle well enough.

Limiting the Number of Cells Selected

In some cases you might want to limit the number of cells your users can select:

In this example, we'd like to prevent the user from selecting multiple cells.

While you can't prevent the user selecting multiple cells when the input box is displayed, you can count the number of cells returned afterwards. In Module1, find the subroutine called MoviePicker. Add an If statement to the subroutine to check how many cells have been selected:

We're using the CountLarge property to check if the user has selected a range of more than one cell.

Restricting the Allowed Range

You'll often want to restrict the user to selecting cells within a specific range:

In this example, we'd like to limit the allowable selection range to B2:B11.

You can't prevent the user from selecting a cell while the input box is displayed, but you can check whether the selected cell is within your permitted range afterwards. In the MoviePicker subroutine, add an If statement to check that the user picked a cell within the allowed range:

You can use the Intersect method to determine whether a cell is within a specific range.

If you were allowing a user to select multiple cells and you wanted to check that every cell was within an allowed range, you'd need to check each cell in the selection individually. The best way to do this is with a For Each loop, which you'll learn all about in the Collections and Loops module.

To practise returning a reference to a range of cells:

In the MoviePicker subroutine, declare another Range variable and ask the user to populate it by selecting the worst film in the list:

Sub MoviePicker()

Dim BestFilm As Range

Dim WorstFilm As Range

Set BestFilm = Application.InputBox( _

Prompt:="Select the best film", _

Type:=8)

'check that only one cell is selected

If BestFilm.CountLarge > 1 Then

MsgBox _

"You were supposed to pick 1!", _

vbCritical

Exit Sub

End If

'check that the cell is in allowed range

If Intersect(BestFilm, Range("B2:B11")) Is Nothing Then

MsgBox _

"You must select a cell between B2:B11", _

vbCritical, _

"You picked " & BestFilm.Address

Exit Sub

End If

Set WorstFilm = Application.InputBox( _

Prompt:="Select the worst film", _

Type:=8)

End Sub

Add an If statement to check how many cells the user has selected and captured in the WorstFilm variable. If it's more than one, display a warning message and exit the subroutine:

Sub MoviePicker()

Dim BestFilm As Range

Dim WorstFilm As Range

Set BestFilm = Application.InputBox( _

Prompt:="Select the best film", _

Type:=8)

'check that only one cell is selected

If BestFilm.CountLarge > 1 Then

MsgBox _

"You were supposed to pick 1!", _

vbCritical

Exit Sub

End If

'check that the cell is in allowed range

If Intersect(BestFilm, Range("B2:B11")) Is Nothing Then

MsgBox _

"You must select a cell between B2:B11", _

vbCritical, _

"You picked " & BestFilm.Address

Exit Sub

End If

Set WorstFilm = Application.InputBox( _

Prompt:="Select the worst film", _

Type:=8)

'check that only one cell is selected

If WorstFilm.CountLarge > 1 Then

MsgBox _

"You were supposed to pick 1!", _

vbCritical

Exit Sub

End If

End Sub

Run the subroutine and check that see a warning message if you attempt to pick multiple cells:

Your message should resemble this one.

Add an If statement to check that the WorstFilm variable contains a cell within the range B2:B11:

Sub MoviePicker()

Dim BestFilm As Range

Dim WorstFilm As Range

Set BestFilm = Application.InputBox( _

Prompt:="Select the best film", _

Type:=8)

'check that only one cell is selected

If BestFilm.CountLarge > 1 Then

MsgBox _

"You were supposed to pick 1!", _

vbCritical

Exit Sub

End If

'check that the cell is within allowed range

If Intersect(BestFilm, Range("B2:B11")) Is Nothing Then

MsgBox _

"You must select a cell between B2:B11", _

vbCritical, _

"You picked " & BestFilm.Address

Exit Sub

End If

Set WorstFilm = Application.InputBox( _

Prompt:="Select the worst film", _

Type:=8)

'check that only one cell is selected

If WorstFilm.CountLarge > 1 Then

MsgBox _

"You were supposed to pick 1!", _

vbCritical

Exit Sub

End If

'check that the cell is within allowed range

If Intersect(WorstFilm, Range("B2:B11")) Is Nothing Then

MsgBox _

"You must select a cell between B2:B11", _

vbCritical, _

"You picked " & WorstFilm.Address

Exit Sub

End If

End Sub

Run the subroutine and check that you can't pick a cell outside the selected range:

This is the type of message you should see if you select a cell outside the allowed range.

Add code to copy the selected cells to the appropriate place on the worksheet:

Sub MoviePicker()

Dim BestFilm As Range

Dim WorstFilm As Range

Set BestFilm = Application.InputBox( _

Prompt:="Select the best film", _

Type:=8)

'check that only one cell is selected

If BestFilm.CountLarge > 1 Then

MsgBox _

"You were supposed to pick 1!", _

vbCritical

Exit Sub

End If

'check that the cell is within allowed range

If Intersect(BestFilm, Range("B2:B11")) Is Nothing Then

MsgBox _

"You must select a cell between B2:B11", _

vbCritical, _

"You picked " & BestFilm.Address

Exit Sub

End If

Set WorstFilm = Application.InputBox( _

Prompt:="Select the worst film", _

Type:=8)

'check that only one cell is selected

If WorstFilm.CountLarge > 1 Then

MsgBox _

"You were supposed to pick 1!", _

vbCritical

Exit Sub

End If

'check that the cell is within allowed range

If Intersect(WorstFilm, Range("B2:B11")) Is Nothing Then

MsgBox _

"You must select a cell between B2:B11", _

vbCritical, _

"You picked " & WorstFilm.Address

Exit Sub

End If

BestFilm.Copy _

Destination:=Range("B16")

WorstFilm.Copy _

Destination:=Range("B17")

End Sub

Run the subroutine and check that the cells you select are copied to the correct locations:

Your selected films should end up in these cells.

Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

Capturing a Reference to a Range

Using the Range Returned

Limiting the Number of Cells Selected

Restricting the Allowed Range

Head office

London

Manchester