VBA for dynamic charts

WillamYan

New Member
Joined
Dec 23, 2016
Messages
20
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.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Country Name201020112012
Austria
1,813535

<tbody>
</tbody>
3,266939

<tbody>
</tbody>
2,485675

<tbody>
</tbody>
Belarus
59,21974

<tbody>
</tbody>
18,31226

<tbody>
</tbody>
18,11955

<tbody>
</tbody>

<tbody>
</tbody>

This is my range. I want to add Belgium.
 
Upvote 0
Country Name
2010
2011
2012
Austria

1,813535

<tbody>
</tbody>

3,266939

<tbody>
</tbody>

2,485675

<tbody>
</tbody>
Belarus

59,21974

<tbody>
</tbody>

18,31226

<tbody>
</tbody>

18,11955

<tbody>
</tbody>

<tbody>
</tbody>

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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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'.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top