Variable minimum and maximum axis values?

[HalPlz]

Hello all,

I have 2 charts on top of eachother - an area graph and column chart. I need them to match up on the x axis, meaning, meaning the bar charts start at 0 on the area graph. I can do this by setting the axis minimum and maximum of the column chart to -55 and 55, or -45 and 45.

Is there some way to set these maximum and minimum axis values equal to a cell on the sheet, or a formula? If I set them to 'auto' they won't always be equal to one another.

Thanks

[MrShorty]

It can be done using VBA, but not without VBA. I do not know how knowledgeable you are about VBA as a programming language or Excel's object model, so I will first refer you to the axis object members: https://msdn.microsoft.com/EN-US/lib.../ff195770.aspx Your main interest for this will be the "maximumscale" and "minimumscale" properties.

Basic code structure would look something like:

Please Login or Register  to view this content.

The procedure can be run from the change or calculate event if desired or attached to a button of some kind or other variations.

Does that help?

[Kaper]

Have a look also on a bit longer description: http://peltiertech.com/link-excel-ch...lues-in-cells/

[HalPlz]

Thanks for this.

I now have 2 charts - how do I specify each chart in VBA?

[MrShorty]

I will refer you again to the help files that describe Excel's object model. If the charts are in stand-alone chart sheets, you will need the charts collection to specify which chart you want. If you are referring to 2 charts embedded in a worksheet, then you will need the Chartobjects object/collection. In my or Jon's sample code, replace "activechart" with the appropriate "charts(item)" or "chartobjects(item)"
object model: https://msdn.microsoft.com/en-us/lib.../ff194068.aspx
charts collection/object: https://msdn.microsoft.com/EN-US/lib.../ff193297.aspx
chargobjects collection/object: https://msdn.microsoft.com/EN-US/lib.../ff822178.aspx

[humdingaling]

if two charts on worksheets

Please Login or Register  to view this content.

[humdingaling]

if you have both primary and secondary axis and want to use cells to set the axis
below code

Please Login or Register  to view this content.

replace H1/H2 with whatever cell you want to use