change graph source range with vba

**wouter_leeuwen** · 03-21-2011, 05:30 AM

Hi everybody,

I have a rather complicated problem and i'm hoping somebody here is able to help me.

where i work i have lots of raw data that i import from an xml file. I want a macro that converts this data into a graph. This is no problem, but...in summary it has 96 'samples' that all need a graph with 4 lines. each line has 50 values. That's still ok, but i want 1 graph and 2 buttons to select the previous and next source.

for example, the graph shows the data from cells B2:D201. The name of the graph is the same a shown in cell A2. The idea is that when I click the button for next data, the graph source/range changes to B202:D401 and the name changes to that shown in A202.The same button should be able to change the data again to the next data (B402:D601) etc.

is this possible??

i added the file as attachment.

thanx in advance!!

**teylyn** · 03-21-2011, 06:29 AM

Hello,

you can do that with a dynamic chart, with not a single line of VBA. In the attached file, I created a Scroll Bar from the Forms Controls toolbox and formatted it with minimum value =1, maximum value = 10000 (you can increase that, if required), Incremental change = 200 and Page Change = 200 and specified cell F28 as the cell link for the currently selected scroll bar value.

Then I created five range names.

chtTitle =OFFSET(Blad1!$D$1,Blad1!$F$28,-3,1,1)
Series0 =OFFSET(Blad1!$D$1,Blad1!$F$28,0,50,1)
Series1 =OFFSET(Series0,50,0)
Series2 =OFFSET(Series0,100,0)
Series3 =OFFSET(Series0,150,0)

I re-defined the chart series to draw the values from

0 (CT) ='make graph macro.xls'!Series0
1 (NG) ='make graph macro.xls'!Series1
3 (NA) ='make graph macro.xls'!Series2
4 (IC) ='make graph macro.xls'!Series3

I entered =chtTitle into cell G28 and then changed the chart title to refer to G28.

Now you just need to click the scroll bar to see the chart values change.

cheers, teylyn

**wouter_leeuwen** · 03-21-2011, 07:44 AM

Wow teylyn! that does exactly what i want. Thank you so much! I didn't know this was possible. It is indeed much easier and faster like this.

thank you for the explanation aswell. it's very clear so i have no further questions. topic closed =D