change chart data range when dynamic data range changes?

Question

I have created a dynamic name range in my worksheet, which is called 'grafiekrange' (chartrange in dutch). I Created a chart and where you select the chart data range, I typed: ='blad1'!grafiekrange because blad1 is the worksheet name where the name range is located.

I press ok and the chart is connected to the range of cells the name refers to at that particular moment. When the range from the name range expands, the data range for the chart is still the same. So when I connect the chart to the name range, excel somehow converts the name range to a hard coded range of cells; e.g. $A$1:$H$10 instead of 'blad1'!grafiekrange.

I found some vba code which is some sort of workaround, but this workaround deletes the chart and creates a new one, on every single change in the worksheet.

this is the code for the worksheet:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveSheet
.ChartObjects.Delete
End With
Call CreateChart
End Sub

this is the code in the module:

Option Explicit
'=========================================================================
Sub CreateChart()
   Dim objChart As ChartObject
   Dim myChtRange As Range
   Dim myDataRange As Range
   With ActiveSheet
    ' What range should chart cover

    Set myChtRange = Range("chtArea")   'Range("G2:Q30")
    ' What range contains data for chart


    Set myDataRange = Range("grafiekrange")


    ' Cover chart range with chart
    Set objChart = .ChartObjects.Add( _
        Left:=myChtRange.Left, Top:=myChtRange.Top, _
        Width:=myChtRange.Width, Height:=myChtRange.Height)
    ' Put all the right stuff in the chart
    With objChart.Chart
      .ChartArea.AutoScaleFont = False
      .ChartType = xlLine
      .SetSourceData Source:=myDataRange, PlotBy:=xlRows
           .HasTitle = True
           .ChartTitle.Characters.Text = "YTD Afzetontwikkeling (cumulatief) bij AS Watson (KV+TP)"
      .ChartTitle.Font.Bold = True
      .ChartTitle.Font.Size = 12
      With .Axes(xlValue, xlPrimary)
        .HasTitle = False
      End With
    End With
   End With
  End Sub
  '=========================================================================

Creating a new chart on every single change is not exactly what I wanted, but if it does what I want, it doesn't matter it works like this. However, I would like to have 2 series with certain formatting. The first 2 series should have a dash line. I used the macro recorder to see how this works and tried to implement this in the code above. But I did not succeed in have a working code.

this is the code the macro recorder returns:

Sub Macro3()
'
' Macro3 Macro
'

'
ActiveSheet.ChartObjects("Grafiek 29").Activate
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveChart.SeriesCollection(1).Select
With Selection.Format.Line
  .Visible = msoTrue
  .DashStyle = msoLineDash
End With
End Sub

My question is: can you either help me implement the series formatting in the code above, or provide me a code which just updates the data range on every single change (to me, that sounds more efficiently but I do not know how to do so). many thanks in advance.

I tried something myself but that does not the chart data and somehow, now returns an error where I first did not:

Option Explicit

Private Sub Worksheet_Change(ByVal myrange As Range)

Set myrange = grafiekrange

  With ActiveSheet.ChartObjects("Grafiek 35")

    ActiveChart.SetSourceData Source:="myrange" 

  End With

End Sub

I don't believe there is VBA necessary for this. techrepublic.com/blog/microsoft-office/… — Axel Richter
ah. I understand the dynamic range method. Although I hoped I just could enter 1 dynamic range for the entire chart. But Ok, I have to create a dyname range for each individual series and axis. — DutchArjo
It helps for expanding the data range for the individual series. But not for having new series added. It is a pity that the option to just connect the chart data to a dynamic named range is not available. — DutchArjo
I tried. But the table method, converts all my data in to plain tekst. And I will lose my formula's. And my data is 100% formula's, which will change when new periods of data are available in the source sheet. — DutchArjo

Axel Richter Axel Richter · Accepted Answer · 2014-09-09T16:22:30

I can't believe, that the Insert Table kills the formulas, but anyway.

If the ='blad1'!grafiekrange gets the right Chart data, then put the following macro into a module:

Sub setSelectedChartSourceData()
 Dim oChart As Chart

 On Error Resume Next 'If Selection is not the CharArea, then next line throws error.

 Set oChart = Selection.Parent
 oChart.SetSourceData Source:=Range("blad1!grafiekrange")

 On Error GoTo 0 'Set Error handling to default.

End Sub

You can assign a keyboard shortcut to the macro. [Ctrl]+[Shift]+[D] for ex.

Now, if the chart data and the named range data has changed, select the Chart and press [Ctrl]+[Shift]+[D] or run the macro setSelectedChartSourceData in another way.

Greetings

Axel

change chart data range when dynamic data range changes?

2 Answers