Microsoft Office Tutorials and References
In Depth Information
Try It
6.
Declare a Variant type variable that will verify the Data Validation Type in the selected cell:
Dim dvCell As Variant
7.
Insert an error bypass with the On Error Resume Next statement to avoid a runtime error
when a selected cell does not contain Data Validation:
On Error Resume Next
8.
Assign the Data Validation Type to the variable:
dvCell = Target.Validation.Type
9.
If there is no VBA runtime error, that is, if the selected cell contains Data Validation, execute
the SendKeys method that simulates the keyboard action of pressing the Alt and Down
Arrow keys. The optional True keyword refers to the Wait argument of SendKeys, for VBA
to wait until the SendKeys action is completed before executing the next line of code.
If Err = 0 Then SendKeys “%{down}”, True
10.
Clear the Error object in case a runtime error did occur:
Err.Clear
11.
The entire Worksheet_Selection procedure will look as follows:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count> 1 Then Exit Sub
Dim dvCell As Variant
On Error Resume Next
dvCell = Target.Validation.Type
If Err = 0 Then SendKeys “%{down}”, True
Err.Clear
End Sub
12.
Press Alt+Q to return to your worksheet. Select a few cells, then select cell C3. When you do
so, its Data Validation drop-down list will appear, as shown in Figure 25-5.
figurE 25-5
To view the video that accompanies this lesson, please select Lesson 25, available
at the following website: www.wrox.com/go/excelvba .
Search JabSto ::




Custom Search