Subscribe to the   RSS Feed or by Email and receive free daily updates

Sometime back I had to figure out a way to change the data range for a chart in excel dynamically at run-time based on certain conditions being met. This post covers provides a simplified version of the code with essential items and logic in it to help you put with the process.

The following function runs when the excel file is opened. It runs through a loop for all the charts in ”Sheet1″ and then finds a specific chart using the chart name (.Name). It reassigns a revised range for the various series of data in the chart.

Code:

Sub Auto_Open()
Dim chartCount As Integer
Dim i As Integer
Dim colName As String
Dim rangeStr As String
Dim chartOnSheet As Chart  

chartCount = ActiveWorkbook.Worksheets("Sheet1").ChartObjects.Count
For i = 1 To chartCount
  With ActiveWorkbook.Worksheets("Sheet1").ChartObjects(i).Chart
   If .Name = "Sheet1 Chart 103" Then
      colName = "D"
      rangeStr = "A84:" & colName & "84"
      .SeriesCollection(2).Values = Worksheets("Sheet1").Range(rangeStr)
      rangeStr = "A91:" & colName & "91"
      .SeriesCollection(3).Values = Worksheets("Sheet1").Range(rangeStr)
      rangeStr = "A85:" & colName & "85"
      .SeriesCollection(4).Values = Worksheets("Sheet1").Range(rangeStr)
   End If
  End With
Next i
End Sub

Popularity: 5% [?]

Related Posts