Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Automatic Chart Updating

Posted on 2011-09-21
Medium Priority
Last Modified: 2012-05-12
I am trying to insert a new line of data in a defined chart area called "CompletionByTheArea".  "CompletionByTheArea" is a defined name on Sheet1.

Problem is that when I insert a row, expanding the range by one, the chart does not see the new row, even though the new inserted row is contained inside of the defined "CompletionByTheArea" range.  

The only way I get the new row of data into the chart is to do another chart>select data and type over the chart data range each time, ='Sheet1'!CompletionbytheArea   Excel macro recorder did not do this.

Have I have a chart named Chart1.

How in VBA would I redefine the chart range so that it updates the chart, and includes my new row? without having to redefine it everytime?  -R-

Question by:RWayneH
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4

Expert Comment

ID: 36576851
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

Open 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.  
LVL 50
ID: 36577007

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

Author Comment

ID: 36577965
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 36577985
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..

Author Comment

ID: 36578012
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-
LVL 50
ID: 36578081

>>  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


Author Comment

ID: 36578114
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-
LVL 50
ID: 36578218

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

Author Comment

ID: 36580356
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-

Author Comment

ID: 36580370

Author Comment

ID: 36581199
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-
LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 36583706
Since you posted an XLS file above, I assumed you are using 2003. It would help to let us know what version you are using, because there are differences.

In 2007 or later, you can see the dynamic range names in the Name Manager.  Formulas Ribbon > Name Manager

Select a range name, then click the formula in the Refers To box and you will see the marching ants highlight the data that the formula returns. To apply a range name to a series, select the data source and edit the series. In the Series values box enter the range name with the reference to the sheet or to the file, e.g.




You have to precede the range name with the sheet or file name, otherwise it will not be accepted.

A few things:
- Although the "Chart data range" does accept a range name when you define a chart, it will convert that range name into a direct reference. When the definition of the range name changes, this does not affect the chart at all, because it no longer refers to the named range.

- When the chart is created, the Series formulas will be derived from the chart data range and will be direct references to a range of cells, starting one row below the chart data range (because the first row is holding the title)

When you insert a new row after the title row, the chart data range formula now consists of two separate ranges. The newly inserted row is not included. The series definition has shifted down.

So, you have two options:

Either re-apply the data range with a macro, or use dynamic ranges for each of the series definitions.

Since you apparently have code that inserts the new row, you can simply add this line to the code to re-define the chart source (just change the "Chart 1" to the actual name of your chart.

    ActiveSheet.ChartObjects("Chart 1").Chart.SetSourceData Source:=Range("CompletionByTheArea")

Open in new window

cheers, teylyn

Author Closing Comment

ID: 36590973
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-

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question