Microsoft Office Tutorials and References
In Depth Information
Try It
As a further example, this will take you to the last row of the DataForm, assuming column B is a
part of its source table:
Cells(Rows.Count, 2).End(xlUp).Select
SendKeys “%DO”
Finally, the following pair of code lines will open the DataForm and get you ready for entering the
next record. This example also shows how the Select statement has its place as well; sometimes it’s
okay to select a cell when working with it.
Cells(Rows.Count, 2).End(xlUp).Select
SendKeys “%DO%W”
SendKeys is as safe as any other method when used with common sense for awareness in your
coding as to what application and window are active at the point of execution. You see another example
of SendKeys in the Try It section.
Try iT
In this lesson, you compose a short Worksheet_Selection procedure that uses the SendKeys
method to automatically expand the drop-down list of a cell containing Data Validation.
lesson requirements
Using the SendKeys method with the Worksheet_Selection event, if a worksheet cell has Data
Validation, make the Data Validation drop-down list appear automatically when the cell is selected.
If the cell is not validated, the SendKeys instruction is bypassed. To get the sample database files
you can download Lesson 25 from the book’s website at www.wrox.com .
step-by-step
1.
On a new worksheet, select cell C3 and establish Data Validation to allow a list, such as the
seven days of the week. Be sure that the In-Cell Dropdown option is selected on the Settings
tab of the Data Validation dialog.
2.
Select any cell other than C3 on that worksheet.
3.
Right-click the worksheet tab and select View Code.
4.
In the Object drop-down list, select Worksheet. By default, the Worksheet_Selection event
line and its accompanying End Sub line will appear in the worksheet module, which will
look like this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
5.
For the first line of code, instruct VBA to do nothing if more than one cell is selected. The
code will be of value only when one cell at a time is selected.
If Target.Cells.Count> 1 Then Exit Sub
Search JabSto ::




Custom Search