Microsoft Office Tutorials and References
In Depth Information
Using a macro to force a recalculation
As with the previous examples, the SERIES formula for the chart’s data series
uses these named formulas. The SERIES formula looks like this:
=SERIES(Sheet1!ChartTitle,Sheet1!$B$2:$F$2,Sheet1!ChartData,1)
Notice that a named formula is not required for the second argument (category
labels). These labels do not change. When the worksheet is recalculated, the named
formulas are updated based on the active cell.
If the cell cursor is not within the data range, the chart displays an empty series.
Using a macro to force a recalculation
An alternative version of this example uses a simple VBA macro to calculate the
sheet when the selection changes. This macro eliminates the need to press F9 to
calculate the sheet and update the chart.
To add this macro:
1. Right-click the worksheet tab and choose View Code from the shortcut
menu. This activates the VBA Editor and displays an empty code module
for the worksheet.
2. Enter the following code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Row > 2 And ActiveCell.Row < 17 Then _
ActiveSheet.Calculate
End Sub
3. Press Alt+F11 to return to Excel.
This VBA macro is executed whenever the range selection is changed on the
worksheet. The code checks the location of the active cell. If the active cell is in a row
greater than 2 but less than 17, it executes a statement that calculates the worksheet.
Chapter 16 contains additional examples of macros that execute when a
particular event occurs.
Defining a Series Based
on the Active Cell
As with the previous example, the example in this section (see Figure 7-14) uses the
active cell. But in this case, the active cell determines the extent of a series. The
Search JabSto ::




Custom Search