VBA for dynamic charts

[WillamYan]

I have a range consisting of countries on the Y-axis and years on the X-axis (I am tracking the inflation change over time for each country with a line chart). I want to create a dynamic chart for my sample which will update when a new country is added. The data in the range is linked to another source, hence the number of rows can increase or deacrease. I am happy to use VBA. I don't want to turn the country name into a table and make the dynamic chart based on that, because the legend sign on the chart for empty cells don't disappear. When I turn the whole range into a table and build the chart, it does not do the job either, unless I enter new countries and respective data manually. I tried to use OFFSET&COUNTA, but no luck. The latter works only if I add new date (a new year, new column), but not when I add a new country (new row). This is why I think the only way to do is using VBA.




I will appreciate your help.

 

[WillamYan]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Country Name[/TD]
[TD]2010[/TD]
[TD]2011[/TD]
[TD]2012[/TD]
[/TR]
[TR]
[TD]Austria[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]1,813535[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]3,266939[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]2,485675[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Belarus[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]59,21974[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]18,31226[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]18,11955[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This is my range. I want to add Belgium.

 

[JLGWhiz]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Country Name
[/TD]
[TD]2010
[/TD]
[TD]2011
[/TD]
[TD]2012
[/TD]
[/TR]
[TR]
[TD]Austria
[/TD]
[TD]
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]1,813535
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]3,266939
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]2,485675
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Belarus
[/TD]
[TD]
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]59,21974
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]18,31226
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]18,11955
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This is my range. I want to add Belgium.

If I am reading your data correctly, when you add a new country you are adding a data series that is not currently in the scope of the chart data source. That means that you must modify the chart data source parameters and that cannot be done with standard Excel functions. You would need to do that manually through the edit procedure.

 

[WillamYan]

The data source is split into groups (A,B,C) of countries based on a certain criteria. The groups can expand or shrink (Austria can move to group B from group A). My range covers the countries in group A.

 

[JLGWhiz]

The data source is split into groups (A,B,C) of countries based on a certain criteria. The groups can expand or shrink (Austria can move to group B from group A). My range covers the countries in group A.

Maybe this link will help more than I can: Dynamic Chart Source Data - Peltier Tech Blog

 

[WillamYan]

Maybe this link will help more than I can: Dynamic Chart Source Data - Peltier Tech Blog

Thank you.

I have already been here. I tried to follow the steps. And I used the following VBA:

Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("ChtSourceData")) Is Nothing Then
Me.ChartObjects(1).Chart.SetSourceData _
Source:=Me.Range("ChtSourceData"), _
PlotBy:=xlColumns
End If
End Sub

It did not work for me. For some reaseon, when I include "(ByVal Target As Range)", I cant find the macro to run it. Without this piece, the macro is there, but does not produce desirable outcome.

 

[JLGWhiz]

Worksheet event code does not show up in the macro dialogue box if it is properly installed in the worksheet code module. The 'Private' in the titles indicates that it is not available for public use. A worksheet event macro is not run by clicking the run button, it is triggered to run by the event for which it is written, in your case, a cjamge to the source range. If properly written and properly installed, the code would run when you make a change anywhere in the named range, 'ChtSourceData'.

 

[WillamYan]

Thanks.It worked