Using VBA to specify the data used by a chart

The examples in this section describe VBA techniques that enable you to change the data used by a chart.


Figure 18-4 displays a chart based on the data in the row of the active cell. When the user moves the cell pointer, the chart is updated automatically.

Figure 18-4: This chart always displays the data from the row of the active cell.

This example uses an event handler for the Worksheet object. The SelectionChange event occurs whenever the user changes the selection by moving the cell pointer. The event handler procedure for this event (which is located in the code module for the Sheet1 object) is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target _ As Excel.Range)

Call UpdateChart End Sub

In other words, every time that the user moves the cell cursor, the Worksheet_ SelectionChange procedure is executed. This procedure calls the UpdateChart procedure, which follows:

Sub UpdateChart()

Dim TheChartObj As ChartObject

Dim TheChart As Chart

Dim UserRow As Long

Dim CatTitles As Range

Dim SrcRange As Range

Dim SourceData As Range

If Sheets("Sheet1").CheckBox1 Then

Set TheChartObj = ActiveSheet.ChartObjects(l) Set TheChart = TheChartObj.Chart UserRow = ActiveCell.Row

If UserRow < 3 Or IsEmpty(Cells(UserRow, 1)) Then TheChartObj.Visible = False


Set CatTitles = Range("A2:F2")

Set SrcRange = Range(Cells(UserRow, 1), _

Cells(UserRow, 6)) Set SourceData = Union(CatTitles, SrcRange) TheChart.SetSourceData _

Source:=SourceData, PlotBy:=xlRows TheChartObj.Visible = True End If End If End Sub

The first step is to determine whether the Auto Update Chart check box is marked. If this check box is not selected, nothing happens. The UserRow variable contains the row number of the active cell. The If statement checks to make sure that the active cell is in a row that contains data. (The data starts in row 3.) If the cell cursor is in a row that doesn't have data, the ChartObject object is hidden. Otherwise, the code creates a Range object (CatTitle) that holds the category titles and another Range object (SrcRange) that contains the data for the row. These two Range objects are joined by using the VBA Union function and assigned to a Range object named SourceData. Finally, the SourceData range is assigned to the chart by using the SetSourceData method of the Chart object.


The next example uses a ComboBox control on a chart sheet to allow the user to select a chart. Figure 18-5 shows how this looks:

Figure 18-5: Selecting from the ComboBox changes the source data for the chart.

The ComboBox used in this example is from the Forms toolbar (not the Control Toolbox toolbar). For some reason, Excel does not let you add ActiveX controls to a chart sheet.

on the cd on the cd

This workbook is available on the companion CD-ROM.

A macro named DropDown1_Change is attached to the ComboBox. When the user makes a selection from the ComboBox, the following procedure is executed:

Sub DropDown1_Change()

Dim ListIndex As Integer ListIndex = ActiveChart.DropDowns(1).Value Call UpdateChart(ListIndex) End Sub

This procedure calls the UpdateChart procedure and passes an integer that represents the user's choice. Following is the listing of the UpdateChart procedure. This is very similar to the UpdateChart procedure in the preceding section.

Sub UpdateChart(Item)

' Updates the chart using the selected dropdown item Dim TheChart As Chart Dim DataSheet As Worksheet Dim CatTitles As Range, SrcRange As Range Dim SourceData As Range

Set TheChart = Sheets("Chart1") Set DataSheet = Sheets("Sheet1")

With DataSheet

Set CatTitles = .Range("A1:F1") Set SrcRange = .Range(.Cells(Item + 2, 1), _ .Cells(Item + 2, 6)) End With

Set SourceData = Union(CatTitles, SrcRange)

With TheChart

.SetSourceData Source:=SourceData, PlotBy:=xlRows .ChartTitle.Left = TheChart.ChartArea.Left .Deselect End With End Sub

0 0

Post a comment