In This Chapter
Macros to create and format
Macros to apply data labels to a series
Macros to export charts as GIF files
Macros to identify and modify ranges used by a chart
A macro to create a word cloud in a shape
Macros that are triggered by events
This chapter presents a wide variety of macros that manipulate charts in various ways. These examples are intended to demonstrate how to work with the objects that comprise a chart. You may be able to use some of these as written, but others may require some modification to make them more useful to you.
The following sections describe how to use VBA to create an embedded chart and a chart on a chart sheet.
In Excel 2007, a ChartObject
is a special type of Shape
object. Therefore, it's a member of the Shapes
collection. To create a new chart, use the AddChart
method of the Shapes
collection. The following statement creates an empty embedded chart:
ActiveSheet.Shapes.AddChart
The AddChart
method can use five arguments (all are optional):
Type: The type of chart. If omitted, the default chart type is used. Constants for all the chart types are provided (for example, xlArea
, xlColumnClustered
, and so on).
Left: The left position of the chart, in points. If omitted, Excel centers the chart horizontally.
Top: The top position of the chart, in points. If omitted, Excel centers the chart vertically
Width: The width of the chart, in points. If omitted, Excel uses 354.
Height: The height of the chart, in points. If omitted, Excel uses 210.
In many cases, you may find it more efficient to create an object variable when the chart is created. The following procedure creates a line chart that can be referenced in code by using the MyChart
object variable:
Sub CreateChart() Dim MyChart As Chart Set MyChart = ActiveSheet.Shapes.AddChart(xlLineMarkers).Chart End Sub
A chart without data is not very useful, so you'll want to use the SetSourceData
method to add data to a newly created chart. The procedure that follows demonstrates the SetSourceData
method. This procedure creates the chart shown in Figure 15-1.
Sub CreateChart() Dim MyChart As Chart Dim DataRange As Range Set DataRange = ActiveSheet.Range("A1:C7") Set MyChart = ActiveSheet.Shapes.AddChart.Chart MyChart.SetSourceData Source:=DataRange End Sub
The examples in this section are available on the companion CD–ROM. The filename is
create a chart.xlsm.
The preceding section describes the basic procedures for creating an embedded chart. To create a chart on a chart sheet, use the Add
method of the Charts
collection. The Add
method of the Charts
collection uses several optional arguments, but these arguments specify the position of the chart sheet — not chart–related information.
The example that follows creates a chart on a chart sheet and specifies the data range and chart type:
Sub CreateChartSheet() Dim MyChart As Chart Dim DataRange As Range Set DataRange = ActiveSheet.Range("A1:C7") Set MyChart = Charts.Add MyChart.SetSourceData Source:=DataRange ActiveChart.ChartType = xlColumnClustered End Sub
A common type of chart macro applies formatting to one or more charts. For example, you may create a macro that applies consistent formatting to all charts in a worksheet. If you experiment with the macro recorder, you'll find that commands in the following Ribbon groups are recorded:
Chart Tools
Chart Tools
Chart Tools
Chart Tools
Chart Tools
Unfortunately, formatting any individual chart element (for example, changing the color of a chart series) is not recorded by the macro recorder. Therefore, you'll need to figure out the objects and properties on your own. The sections that follow provide lots of pointers.
I used output from the macro recorder as the basis for the FormatChart
procedure shown here, which converts the active chart to a clustered column chart (using Chart Tools
Sub FormatChart() If ActiveChart Is Nothing Then Exit Sub With ActiveChart .ChartType = xlColumnClustered .ApplyLayout 3 .ChartStyle = 28 .SetElement msoElementPrimaryValueGridLinesNone .ClearToMatchStyle End With End Sub
This example, named format a chart.xlsm
, is available on the companion CD–ROM. The CD also contains an example (format all charts.xlsm
) that applies formatting to several charts.
Figure 15-2 shows a chart before and after executing the FormatChart
macro.
After executing this macro, the actual appearance of the chart depends on the document theme that's in effect.
Note the following points about the FormatChart
procedure:
VBA provides constants for the various chart types, and you can assign these constants to the ChartType
property
The ApplyLayout
method uses a number to represent the layout, and the numbers vary with the chart type. These numbers appear as ToolTips when you hover the mouse pointer over an icon in the Chart Tools
.ApplyLayout 3, xlColumnClustered
The ChartStyle
property also uses a nondescriptive number (from 1 to 48) for its argument. These numbers appear as ToolTips when you hover the mouse pointer over an icon in the Chart Tools
The SetElement
method controls the visibility of just about every aspect of the chart. It accepts more than 120 descriptive constants. For example, the constant msoElementChartTitleNone
hides the chart's title.
The ClearToMatchStyle
method clears all user–applied formatting on the chart. This method is typically used in conjunction with the ChartStyle
property to ensure that the applied style does not contain any formatting that's not Part of the style.
As I've noted, the macro recorder in Excel 2007 ignores many formatting commands when working with a chart. This deficiency is especially irksome if you're trying to figure out how to apply some of the new formatting options such as shadows, beveling, and gradient fills.
In this section, I provide some examples of chart formatting. I certainly don't cover all the options, but it should be sufficient to help you get started so that you can explore these features on your own. These examples assume an object variable named MyChart
, created as follows:
Dim MyChart As Chart Set MyChart = ActiveSheet.ChartObjects(1).Chart
If you apply these examples to your own charts, you need to make the necessary modifications so that MyChart
points to the correct Chart
object.
To delete all user–applied (or VBA–applied) formatting from a chart, use the ClearToMatchStyle
method of the Chart
object. For example:
MyChart.ClearToMatchStyle
One of the most interesting formatting effects in Excel 2007 is shadows. A shadow can give a chart a three–dimensional look and make it appear as if it's floating above your worksheet.
The following statement adds a default shadow to the chart area of the chart:
MyChart.ChartArea.Format.Shadow.Visible = msoTrue
In this statement, the Format
property returns a ChartFormat
object, and the Shadow
property returns a ShadowFormat
object. Therefore, this statement sets the Visible
property of the ShadowFormat
object, which is contained in the ChartFormat
object, which is contained in the ChartArea
object, which is contained in the Chart
object.
Not surprisingly, the ShadowFormat
object has some properties that determine the appearance of the shadow. Here's an example of setting five properties of the ShadowFormat
object, contained in a ChartArea
object, and Figure 15-3 shows the effect:
With MyChart.ChartArea.Format.Shadow .Visible = msoTrue .Blur = 10 .Transparency = 0.4 .OffsetX = 6 .OffsetY = 6 End With
The example that follows adds a subtle shadow to the plot area of the chart:
With MyChart.PlotArea.Format.Shadow .Visible = msoTrue .Blur = 3 .Transparency = 0.6 .OffsetX = 1 .OffsetY = 1 End With
If an object has no fill, applying a shadow to the object has no visible effect. For example, a chart's title usually has a transparent background (no fill color). To apply a shadow to an object that has no fill, you must first add a fill color. This example applies a white fill to the chart's title and then adds a shadow:
MyChart.ChartTitle.Format.Fill.BackColor.RGB = RGB(255, 255, 255) With MyChart.ChartTitle.Format.Shadow .Visible = msoTrue .Blur = 3 .Transparency = 0.3 .OffsetX = 2 .OffsetY = 2 End With
Adding a Bevel
Adding a bevel to a chart can provide an interesting 3–D effect. Figure 15-4 shows a chart with a beveled chart area. To add the bevel, I used the ThreeD
property to access the ThreeDFormat
object. The code that added the bevel effect is as follows:
With MyChart.ChartArea.Format.ThreeD .Visible = msoTrue .BevelTopType = msoBevelDivot .BevelTopDepth = 12 .BevelTopInset = 32 End With
Chapter 16 contains some additional charting examples that deal with color.
As you may know, applying data labels to a chart series has a serious limitation: Excel does not allow you to specify an arbitrary range for the data label text. With a fairly simple macro, however, you can overcome this limitation and apply data labels from any range.
Figure 15-5 shows an XY chart. The goal is to label each point with the corresponding name in column A. The following macro accomplishes this goal:
Sub ApplyDataLabels() Dim Ser As Series, Pt As Point Dim Counter As Long Set Ser = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1) Chapter 15: Advanced VBA Examples 469 Part III 21_044001 ch15.qxp 8/24/07 9:34 PM Page 469
Ser.HasDataLabels = True Counter = 1 For Each Pt In Ser.Points Pt.DataLabel.Text = Range("A1").Offset(Counter, 0) Counter = Counter + 1 Next Pt End Sub
Figure 15-5. Excel does not provide a direct way to use the text in column A as data labels in the chart.
The ApplyDataLabels
macro creates an object variable (Ser
) that represents the chart's data series. It then sets the HasDataLabels
property to True
. (Without this statement, the macro would end with an error.) The next statement initializes a variable (Counter
) to 1. The next four statements comprise a For Each–Next
loop, which loops through each Point
object in the series. The code sets the Text
property of the Point
object's DataLabel
object equal to a cell that is offset from cell A1. The offset row is specified by the Counter
variable, which is incremented each time through the loop.
Figure 15-6 shows the chart after executing the ApplyDataLabels
macro.
A data label can contain a simple formula that refers to a cell. In such a case, the data label is linked to that cell ‐ if the cell changes, so does the corresponding data label. It's a simple matter to modify the ApplyDataLabels
macro from the previous section so that it creates links to the cells. Only one statement needs to be changed:
Pt.DataLabel.Text = "=" & Range("A1").Offset(Counter, 0) _ .Address(True, True, xlR1C1, True)
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.
The preceding macros hard–code the data label range, so they are not general–purpose 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 15-7).
This example, named data labels.xlsm
, is available on the companion CD–ROM. In addition, the author's PUP v7 add–in includes a utility to apply data labels to charts. A 30– day trial version is available on the companion CD–ROM.
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 a statement 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 Dim Ser As Series Dim Counter As Long On Error Resume Next If ActiveChart Is Nothing Then MsgBox "Select a chart." Exit Sub End If Set RngLabels = Application.InputBox _ (prompt:="Range for data labels?", Type:=8) If RngLabels Is Nothing Then Exit Sub 'Canceled On Error GoTo 0 If RngLabels.Rows.Count > 1 And RngLabels.Columns.Count > 1 Then MsgBox "Select a single row or column for labels." Exit Sub End If Set Ser = ActiveChart.SeriesCollection(1) Ser.HasDataLabels = True For Counter = 1 To Ser.Points.Count Ser.Points(Counter).DataLabel.Text = _ RngLabels.Cells(Counter) Next Counter End Sub
The DataLabelsWithPrompt
procedure always works with the first series in the chart. A more general macro would prompt the user for the series if the chart has more than one series.
Saving a chart as a GIF file is very easy: Just use the Export
method of the Chart
object. Here's a simple macro that saves the active chart as a GIF file named mychart.gif
:
Sub ExportToGIF() If ActiveChart Is Nothing Then MsgBox "Select a chart." Else ActiveChart.Export "mychart.gif", "GIF" End If End Sub
The macro first checks to ensure that a chart is active. If so, it saves the chart to the current directory.
If you prefer to be prompted for a filename and location, use the macro that follows. This macro uses the GetSaveAsFilename
function to display a dialog box with a default filename (the chart's name) and directory. The user can then accept these defaults or select a different directory or filename. Figure 15-8 shows the dialog box that's displayed when this macro is executed. If the Cancel button is clicked, the function returns False
. In such acase, the macro ends with no action.
Sub SaveAsGIF() Dim FileName As Variant If ActiveChart Is Nothing Then MsgBox "Select a chart." Exit Sub End If ' Get the filename FileName = Application.GetSaveAsFilename( _ InitialFileName:=ActiveChart.Name & ".gif", _ FileFilter:="GIF Files (*.gif), *.gif", _ Title:="Save chart as GIF file") If FileName <> False Then ActiveChart.Export FileName, "GIF" End If End Sub
This example, named save as gif.xlsm
, is available on the companion CD–ROM. In addition, the author's PUP v7 add–in includes a utility to export charts as GIF files. A 30– day trial version is available on the companion CD–ROM.
Figure 15-9 shows a chart with 5, 218 data points in each series. The workbook contains these six names:
StartDay: A name for cell F1
NumDays: A name for cell F2
Increment: A name for cell F3 (used for automatic scrolling)
Date: A named formula:
=OFFSET(Sheet1!$A$1, StartDay,0, NumDays,1)
ProdA: A named formula:
=OFFSET(Sheet1!$B$1, StartDay,0, NumDays,1)
ProdB: A named formula:
=OFFSET(Sheet1!$C$1, StartDay,0, NumDays,1)
Each of the SERIES formulas on the chart uses names for the category values and the data. The SERIES formula for the Product A series is as follows:
=SERIES(Sheet1!$B$1, Sheet1!Date, Sheet1!ProdA, 1)
The SERIES formula for the Product B series is as follows:
=SERIES(Sheet1!$C$1, Sheet1!Date, Sheet1!ProdB, 2)
Using these names enables the user to specify a value for StartDay and NumDays, and the chart will display a subset of the data.
The companion CD–ROM contains a workbook that includes this animated chart. The filename is
scrolling chart.xlsm
A relatively simply macro makes the chart scroll. The button in the worksheet executes the following macro that scrolls (or stops scrolling) the chart:
Public AnimationInProgress As Boolean Sub AnimateChart() Dim StartVal As Long, r As Long
If AnimationInProgress Then AnimationInProgress = False End End If AnimationInProgress = True StartVal = Range("StartDay") For r = StartVal To 5219 – Range("NumDays") _ Step Range("Increment") Range("StartDay") = r DoEvents Next r AnimationInProgress = False End Sub
The AnimateChart
procedure uses a public variable (AnimationInProgress
) to keep track of the animation status. The animation results from a loop that changes the value in the StartDay cell. Because the two chart series use this value, the chart is continually updated with a new starting value. The Scroll Increment setting determines how quickly the chart scrolls.
To stop the animation, I use an End
statement rather than an Exit Sub
statement. For some reason, Exit Sub
doesn't work reliably and may even crash Excel.
The example in this section doesn't use a chart. Rather, it uses a shape object to display words that vary in font size. (The font size is proportional to a value associated with each word.) Such a display is often seen on Web sites, and is referred to as a word cloud.
Figure 15-10 shows a word cloud that depicts the population of each of the 50 states in the United States.
The macro that creates the word cloud shape is rather lengthy, so I don't show the code here. The example, named word cloud.xlsm
, is available on the companion CD–ROM.
This macro can be used with any two–column list of data in which the first column is text and the second column contains values. Adjust the following statements in the WordCloud macro:
'Two column range that contains the data Set DataRange = Range("A1:B50") 'Minimum and maximum font size FontMin = 8 FontMax = 80
You might need a VBA macro that, as Part of its job, must first determine the ranges used by each series in chart. For example, you may want to increase the size of each series by adding a new cell. Following is a description of three properties that seem relevant to this task:
Formula property: Returns or sets the SERIES formula for the series. When you select a series on a chart, its SERIES formula is displayed in the formula bar. The Formula
property returns this formula as a string.
Values property: Returns or sets a collection of all the values in the series. This can be a range in a worksheet or an array of constant values, but not a combination of both.
XValues property: Returns or sets an array of x values for a chart series. The XValues
property can be set to a range in a worksheet or to an array of values — but it can't be a combination of both. The XValues
property can also be empty.
If you create a VBA macro that needs to determine the data range used by a particular chart series, you may think that the Values
property of the Series
object is just the ticket. Similarly, the XValues
property seems to be the way to get the range that contains the x values (or category labels). In theory, that certainly seems correct. But in practice, it doesn't work. When you set the Values
property for a Series
object, you can specify a Range
object or an array. But when you read this property, it is always an array. Unfortunately, the object model provides no way to get a Range
object used by a Series
object.
One possible solution is to write code to parse the SERIES
formula and extract the range addresses. This sounds simple, but it's actually a difficult task because a SERIES
formula can be very complex. Following are a few examples of valid SERIES
formulas.
=SERIES(Sheet1!$B$1, Sheet1!$A$2:$A$4, Sheet1!$B$2:$B$4, 1) =SERIES(,Sheet1!$B$2:$B$4, 1) =SERIES(,Sheet1!$A$2:$A$4, Sheet1!$B$2:$B$4, 1) =SERIES("Sales Summary",Sheet1!$B$2:$B$4, 1) =SERIES(,{"Jan","Feb","Mar"},Sheet1!$B$2:$B$4, 1) =SERIES(,(Sheet1!$A$2, Sheet1!$A$4),(Sheet1!$B$2, Sheet1!$B$4),1) =SERIES(Sheet1!$B$1, Sheet1!$A$2:$A$4, Sheet1!$B$2:$B$4, 1,Sheet1!$C$2:$C$4)
As you can see, a SERIES
formula can have missing arguments, use arrays, and even use noncontiguous range addresses. And to confuse the issue even more, a bubble chart has an additional argument (for example, the last SERIES formula in the preceding list). Attempting to parse out the arguments is certainly not a trivial programming task.
I worked on this problem for several years, and I eventually arrived at a solution. The trick involves evaluating the SERIES
formula by using a dummy function. This function accepts the arguments in a SERIES
formula and returns a 2 × 5 element array that contains all the information in the SERIES
formula.
I simplified the solution by creating four custom VBA functions, each of which accepts one argument (a reference to a Series
object) and returns a two–element array. These functions are the following:
SERIESNAME_FROM_SERIES: The first array element contains a string that describes the data type of the first SERIES
argument (Range, Empty, or String). The second array element contains a range address, an empty string, or a string.
XVALUES_FROM_SERIES: The first array element contains a string that describes the data type of the second SERIES
argument (Range, Array, Empty, or String). The second array element contains a range address, an array, an empty string, or a string.
VALUES_FROM_SERIES: The first array element contains a string that describes the data type of the third SERIES
argument (Range or Array). The second array element contains a range address or an array.
BUBBLESIZE_FROM_SERIES: The first array element contains a string that describes the data type of the fifth SERIES
argument (Range, Array, or Empty). The second array element contains a range address, an array, or an empty string. This function is relevant only for bubble charts.
Note that a function to get the fourth SERIES
argument (plot order) is not needed. This argument can be obtained directly by using the PlotOrder
property of the Series
object.
The following example demonstrates how to determine the address of the values range for the first series in the active chart.
Sub ShowValueRange() Dim Ser As Series Dim x As Variant Set Ser = ActiveChart.SeriesCollection(1) x = VALUES_FROM_SERIES(Ser) If x(1) = "Range" Then MsgBox Range(x(2)).Address End If End Sub
The variable x
is defined as a variant and will hold the two–element array that's returned by the VALUES_FROM_SERIES
function. The first element of the x
array contains a string that describes the data type. If the string is Range, the message box displays the address of the range contained in the second element of the x
array.
The VBA code for these functions is too lengthy to be presented here, but it's available on the companion CD–ROM. It's documented such that it can be easily adapted to other situations. The filename is get series ranges.xlsm
.
The following sections present examples of VBA event procedures. An event procedure is a macro that responds to certain events. For example, opening a workbook is an event, and you can write a macro that is executed when that particular event occurs. Excel supports many other events; several of them are described in the sections that follow.
The examples in this section deal with the Worksheet_Change
event, which is one of many events available at the worksheet level. This event is triggered whenever a cell is changed in a worksheet. As you'll see, these types of macros can be very useful when you want to update charts automatically.
The event macros in this section must be located in the code module for the worksheet (for example, the module named Sheet1
). They do not work if they are contained in a standard VBA module.
When you activate a code module for a chart, sheet, or workbook, you can use the dropdown lists at the top to select an object and event. Figure 15-11 shows a code module for a worksheet. The object (Worksheet
) is selected in the left drop–down list, and the right drop–down list displays all the events that can be monitored for the object. When you choose an item from the list, the procedure "shell" (consisting of the first and last statements) is inserted for you.
Hiding And Displaying Charts
The worksheet shown in Figure 15-12 contains five embedded charts, four of which are hidden. Cell B2 controls which of the five charts is visible. This cell uses data validation to display a list of values from 1 through 5, plus an additional entry: (none).
This example, named hide charts – event macro.xlsm
, is available on the companion CD–ROM.
Figure 15-12. Cell B2 contains a drop–down list of chart numbers. The selected chart number is visible and the others are hidden.
The worksheet uses a Worksheet_Change
event procedure, which follows. The macro is executed whenever a cell is changed on the sheet. Notice that this procedure has an argument: Target
. This argument is a variable that represents the cell or range that is changed. If the address of Target
is $B$2, the macro performs two operations: It hides all members of the ChartObjects
collection and then unhides the chart that corresponds to the value in cell B2. The On Error
statement ignores the error that occurs if an invalid ChartObject
number is referenced, or when the user selects (none).
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$2" Then ActiveSheet.ChartObjects.Visible = False On Error Resume Next ChartObjects(Target.Value).Visible = True End If End Sub
The five embedded charts are stacked on top of each other, so they all appear in the same position on–screen.
Adjusting Axis Scaling
The example in this section further uses an event macro to adjust a chart's value axis. Figure 15-13 shows the worksheet. The Axis property values are entered in the cells in column D. These cells are named AxisMin, AxisMax, and MajorUnit.
Figure 15-13. An event macro, executed when any cell on the sheet is changed, modifies properties for the chart's value axis.
The Worksheet_Change
procedure that follows is executed whenever any cell in the sheet is changed. The first statement of the macro checks the Column
property of the Target
variable, which represents the cell that was changed. If the changed cell is in column D, the property values are retrieved from the sheet and applied to the chart. Otherwise, nothing happens.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 4 Then On Error Resume Next With ActiveSheet.ChartObjects(1).Chart.Axes(xlValue) .MinimumScale = Range("AxisMin") .MaximumScale = Range("AxisMax") .MajorUnit = Range("MajorUnit") End With End If End Sub
This procedure doesn't do any error handling. Rather, it uses an On Error Resume Next
statement to ignore any errors that occur. For example, if the user types a non–numeric entry for the AxisMax setting, no error is generated.
This example, named axis settings – event macro.xlsm
, is available on the companion CD–ROM.
A Selection_Change
event occurs whenever the user selects a different cell or range. You can use this event to create interactive charts.
Changing The Beginning Point Of A Series
Figure 15-14 shows an example. The data displayed on the chart depends on the active cell. The chart displays 12 data points, beginning with the data in the row of the active cell.
Figure 15-14. This chart uses a Selection_Change
event procedure to display 12 data points beginning in the row of the active cell.
This example, named series based on active cell – event macro.xlsm
, is available on the companion CD–ROM.
The event macro, located in the code module for Sheet1, is as follows:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Ser As Series If ActiveCell.Column > 2 Or ActiveCell.Row < 3 Then Exit Sub Set Ser = ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1) ' Specify values Ser.Values = Range(Cells(ActiveCell.Row, 2), _ Cells(ActiveCell.Row + 12, 2)) ' Specify category Ser.XValues = Range(Cells(ActiveCell.Row, 1), _ Cells(ActiveCell.Row + 12, 1)) End Sub
Whenever the user makes a new cell or range selection, the Worksheet_SelectionChange macro is executed. This macro first checks the active cell. If the active cell is not within the two–column data range, the macro ends. Otherwise, it creates an object variable (Ser
), which represents the chart's data series. It then sets the Values
property and the XValues
property of the Series
object, based on the active cell.
Plotting Data In The Active Row
The example in this section is similar to the previous examples. It uses the Selection_Change
event to display data that's determined by the active cell. Figure 15-15 shows a chart that displays the responses to a survey question. The data displayed in the chart depends on the position of the active cell.
This example, named plot data in active row– event macro.xlsm
, is available on the companion CD–ROM.
The event procedure is as follows:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Ser As Series If ActiveCell.Row > 2 And ActiveCell.Row < 17 Then ActiveSheet.ChartObjects("Chart 1").Visible = True Set Ser = ActiveSheet.ChartObjects("Chart 1"). _ Chart.SeriesCollection(1) 'Specify values
Ser.Values = Range(Cells(ActiveCell.Row, 2), _ Cells(ActiveCell.Row, 6)) 'Specify title ActiveSheet.ChartObjects("Chart 1").Chart.ChartTitle.Text = _ Cells(ActiveCell.Row, 1) Else ActiveSheet.ChartObjects("Chart 1").Visible = False End If End Sub
This procedure uses an If–Then–Else
construct. If the active cell is in a row that contains data, the code modifies the Values
property of the Series
object and also specifies the title. If the active cell is in any other row, the chart is hidden.