BET: Microsoft Excel Charts Common Problems - Displaying a Different Series

Leading the way in Microsoft Office Development

Home

Excel

Word

Microsoft Excel > Charts Common Problems > Displaying a Different Series

< Previous | Next >

Step 1 - Change the Chart By Selecting a Different Row

	This example shows you how you can change the source data of a chart when you select different cells on a worksheet.
	By selecting a row in the table you can quickly display the corresponding series of data on the chart.
	We are going to use the Worksheet_SelectionChange event to be able to detect when a different cell is selected.
	This chart will always display the data from the row of the active cell.

Step 2 - Things to Look Out For

If you select a range of cells that span multiple rows the first row in the selection will be used.

Step 3 - Worksheet_SelectionChange Event

	Every time the user selects a cell on the worksheet this event is fired.
	The Target argument contains the range object for the cell (or cells) that are currently selected.

1
2
3
4
5
6
7
8
9
10
11

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rgeData As Range

   If (ActiveCell.Column >= 2 And ActiveCell.Column <= 6) And _
      (ActiveCell.Row >= 3 And ActiveCell.Row <= 9) Then

      Set rgeData = Range(Cells(ActiveCell.Row, 3), Cells(ActiveCell.Row, 6))
      ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Values = rgeData
      ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).XValues = Range("C2:F2")
   End If
End Sub

Step 4 - Line By Line

	This event handler will be called when any cell (or range of cells) is selected.
	The first thing we need to do is make sure that the active cell is in our table.
	We can check this by checking the row and column numbers of the active cell.

12
13

If (ActiveCell.Column >= 2 And ActiveCell.Column <= 6) And _
(ActiveCell.Row >= 3 And ActiveCell.Row <= 9) Then

If the selected cell is not in our table then we don’t need to do anything.

	This line of code creates a range which corresponds to the row of data we want to display on our chart.
	This basically defines the range to be the cells between "C" and "F" on the active row.

14	Set rgeData = Range(Cells(ActiveCell.Row, 3), Cells(ActiveCell.Row, 6))

This line of code defines the values for the chart to be the range we defined in the previous line.

15	ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Values = rgeData

This line of code defines the chart labels on the X-axis to be the column headings of our table.

16	ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).XValues = Range("C2:F2")

< Previous | Top | Next >

Leading the way in Microsoft Office Development

Step 1 - Change the Chart By Selecting a Different Row

This example shows you how you can change the source data of a chart when you select different cells on a worksheet.

By selecting a row in the table you can quickly display the corresponding series of data on the chart.

We are going to use the Worksheet_SelectionChange event to be able to detect when a different cell is selected.

This chart will always display the data from the row of the active cell.

Step 2 - Things to Look Out For

If you select a range of cells that span multiple rows the first row in the selection will be used.

Step 3 - Worksheet_SelectionChange Event

Every time the user selects a cell on the worksheet this event is fired.

The Target argument contains the range object for the cell (or cells) that are currently selected.

Step 4 - Line By Line

This event handler will be called when any cell (or range of cells) is selected.

The first thing we need to do is make sure that the active cell is in our table.

We can check this by checking the row and column numbers of the active cell.

If the selected cell is not in our table then we don’t need to do anything.

This line of code creates a range which corresponds to the row of data we want to display on our chart.

This basically defines the range to be the cells between "C" and "F" on the active row.

This line of code defines the values for the chart to be the range we defined in the previous line.

This line of code defines the chart labels on the X-axis to be the column headings of our table.