Make the scale of one chart dependent on the scale of another chart

[behrensf84]

Hi,

I was wondering if anyone might know how to make a code that changes the x axis range of a chart to match the x axis range of another chart. I'm thinking along the following line:

1. chart 1 would have a x axis with a particular range (say 1 to 10) which would be preset by the range of the data that is being graphed

2. chart 2 would use a different data set with a different range (say from -5 to 5).

3. Excel would normaly make a chart that best fits the range of data, and the range would be different for both charts.

4. For chart 2 what I would want the display range to be someting like this:

With ActiveChart
.Axes(xlValue).MinimumScale = 'here would be a variable that gets the minimum scale from chart 1
.Axes(xlValue).MinimumScale = 'here would be a variable that gets the maximum scale from chart 1
End With

5. I would want to make it so that if the data range from chart 1 changes, the range in chart 2 would change automatically. The range for chart 1 would be predetermined by excel based on the data.

thanks

[Bernie Deitrick]

Please Login or Register  to view this content.

You could use the worksheet's calculate event or change event to make the code run.

[Andy Pope]

Rather than code why not add an additional series to each chart that contains min and max value of both data sets. That way excel will set both charts automatically to the same autoscale properties

[behrensf84]

Hi Bernie,

sorry for the delay.

your code works great, except for one thing. I have the "chart1" data linked to a pivot table that I can change the data range with a slicer, so if I change the slicer selection, the second chart won't change, unless I have a separate action button to update the scale second chart. is there a way to link your code to the selection in the slicer? I would like to be able to make a selection in the slicer, and update both charts...

Thanks.

[behrensf84]

Hi Andy,

Im not sure that would work, because I have the data for the first chart in a pivot table...
Thanks for the help though...

[Bernie Deitrick]

Sure - just use the PivotTableUpdate event to call the macro: in the codemodule of the sheet with the pivot table

Please Login or Register  to view this content.

[Bernie Deitrick]

Oops - I replied to myself initially...

Sure - just use the PivotTableUpdate event to call the macro: in the codemodule of the sheet with the pivot table

Please Login or Register  to view this content.

[behrensf84]

Oh. Thanks Bernie!

I didnt know it was possible to do that! It's an Awesome trick that's going to eliminate some 50 lines of code for me.

Do you also know if its possible to program a pivot table slicer so that a person cannot select more than 1 item at a time? (ie, they wouldn't be able to use the shift key or the control key and select multiple items). I can do it with an action button, but I would like to be able to do it by clicking on the slicer.

thanks!

[Andy Pope]

See Bob Phillips' recent article in single choice slicers
http://wessexbi.wordpress.com/2014/0...-slice-please/