Microsoft Office Tutorials and References
In Depth Information
Prompting for a range
This statement inserts an equal sign (to indicate a formula), followed by the
Address property of the cell. The Address property takes four arguments. After
executing the modified macro, the first data label in the series contains this formula:
=Sheet1!$A$2
The arguments for the Address property specify that the cell address be
represented as an absolute reference (arguments 1 and 2), in R1C1 reference style
(argument 3), and in “external” format (argument 4). Using the external format
ensures that the sheet name is appended to the cell reference. The
requirement to use an R1C1 reference style is an unusual quirk that’s not documented.
Prompting for a range
The preceding macros hard-code the data label range, so they are not
generalpurpose macros. A better approach is to specify the range interactively. The
following macro uses the InputBox method to display a simple dialog box in which the
user can specify a range by clicking a cell (see Figure 16-12).
Figure 16-12: The InputBox method prompts
the user for a range.
The DataLabelsWithPrompt macro, which follows, is similar to the preceding
macros but contains additional code to display the dialog box. The cell specified by
the user is assigned to a Range object variable ( RngLabels ). Notice that there’s a
statement that checks to see whether RngLabels is Nothing . That will be the case if
the user clicks the Cancel button. If so, the macro ends with no action. The On
Error Resume Next statement is present because clicking Cancel causes an error.
That statement simply ignores the error. Also, notice the second Set statement that
uses the RngLabels object. This statement ensures that if the user selects a multicell
range, only the first cell in the selected range is assigned to RngLabels .
Sub DataLabelsWithPrompt()
Dim RngLabels As Range
Search JabSto ::




Custom Search