Automatic Chart Updating

I'm working on the assumption that the chart is within a worksheet (Sheet1, say) and you only have one chart on that worksheet.  I've also assumed that your named range is a normal global name.  If any of that doesn't hold then send over your Workbook and I'll have a look at it.

You need to put the following code in the VBA for that sheet (right-click on the tab and do View Code).

Private Sub Worksheet_Change(ByVal Target As Range)
  Me.ChartObjects(1).Chart.SetSourceData Source:=ThisWorkbook.Names("CompletionByTheArea").RefersToRange
End Sub

Select allOpen in new window

You can vary this by refering to the chart by its name Me.ChartObjects("Chart 1") perhaps assigning your own name to it by typing Me.ChartObjects(1).Name="Fred" in the Immediate window in the VBA editor.  If you find the code is slowing things down at all, you could also use a different event, e.g. the Calculate event, or use Target to check whether the change has actually impacted you chart.  

Hello,

a chart will update if a row of data is added or removed to the source table. What exactly is the definition of the range name, and what is the definition of the chart series?

This should not require VBA. In fact, if the chart already uses the range name as the data source, then the macro above will achieve nothing. But you must make sure that range name and chart source are properly set up. Can you post a sample file that illustrates the problem?

cheers, teylyn

ChartExample.xls

Sorry sent file w/out explaination.  I click on row 4 of the data file and insert a row, which expands both range areas. In this example ChartOneArea and ChartTwoArea.  After inserting the row and populating the data, I have to manually redefine the chart range to reset the chart and show the new row in the chart.

What I am looking for is a way to automate that with VBA..

This may sound like a wierd request too, but is there a way to get the newest data in the the graph left-to-right? instead of right-to-left?  I minor detail but does make a difference to me.  Thanks.  -R-

Hello,

>>  I click on row 4 of the data file and insert a row, which expands both range areas.

With this, you are actually inserting a row ABOVE the chart range, not extending the chart range. Despite the fact that you have used a range name and entered that into the Data Range box, the actual series definitions override that. Look at the series formulas and see where they start. Insert a row and look again. The series definition has moved down one row.

But you don't need VBA to fix that. You could use dynamic range names to define the chart source and plug these range names into the series definition.

In the attached file, I've defined the following range names to populate chart one:

cht1Labels      =OFFSET(ChartData!$A$3,1,0,COUNT(ChartData!$A:$A),1)
cht1Series1      =OFFSET(cht1Labels,0,1)
cht1Series2      =OFFSET(cht1Labels,0,2)
cht1Series3      =OFFSET(cht1Labels,0,3)
cht1Series4      =OFFSET(cht1Labels,0,4)

I've left chart two unchanged.

If you want to change the order of the X axis, you can format the Axis with the setting "Dates in reverse order" on the Scale tab. But that will push the Y axis to the right hand side.

As an alternative, you can specify the axis as a Category axis instead of using the default, which will be Time. Click the chart and then Chart > Chart Options > Axes and tick Category. Now you can sort the source table ascending or descending and the chart will reflect that order.

cheers, teylyn



ChartExample.xls

Can we do this with VBA?  My macro inserts a row at row 4.  When I search the named range it does increase, just not the chart ranges.  Is there a way to not have the absolute values in the Data range? and just give it a name?

Not understanding the cht1Label cht1Series1 ?  where are  those named ranges?  I hit the dropdown kittycorner cell A1 and none are list in that?

Guess I am not understanding this. -R-

Hello,

dynamic range names don't show in that kittycorner. Click Insert > Name > Define to see these range names.

The problem lies in how the chart is created. As I said above, Data Range box for the whole range will work fine when the chart is first populated, but the series formulas derived from that will not use that range. They will use individual ranges which start one row below the title row, i.e. row 4. If you insert a new row below the title row, then the series definition will start in row 5.

If you want to work with range names to define the chart source, you need to define the range names at the series level. Range names don't work with the Data Range.

Once the range names have been defined and plugged into the chart, the macro can create as many rows as you want, above the data, below the data,  doesn't really matter. The range will grow and since the series formula refers to that range directly, the chart will update.

cheers, teylyn

Where are the names in xl2007?  It looks different then in xl2003.  I understand that naming the Series ranges so each time a row in inserted that those expand too, but not sure that it will solve the issue of re-defining the chart data range.

Still confused.. and trying to understand.  Do you know of any screen captures or videos that would show how to set this up in xl2007? -R-

ChartDataRange.png

Not following this!  Need more of a step-by-step then how to create this kind of Chart, if I can not redefine the Chart data range.  HELP!  -R-

I am still not getting the manual way of setting this up, but the VBA worked great!!  I wonder why the named chart range does not expand?  as it does on the sheet..  one would think that the chart range would expand too?  The VBA work for m needs.  THANKS!!  -R-