Chart Y Axis autoScale from Cell Content through VBA Macro

[analystbank]

Hello Senior,

Sample Chart book is attached in 97-2003 format.

I have a Chart, and names as appears in Name box for same is "Chart 5" for which Max and Min Scale are set through Formula in Cell T59 (max value), and U59 (Min value).

Using VBA Macro , how can
a) i Select specific chart , in instant case, - "Chart 5" and
b) how can i set its Y Axis limit to what is being set in Cell T59 (Max) and U59 (Min)

How to do it, i tried a lot and read few blogs also but unable to do it.

c) I guess changing worksheet Event (when either Max / Min or Chart Data changes), chart should auto update.

Thanks

P.S.: As can be seen for no data in rows Q12 onward, chart shows 0 data on right hand side, just in case if it can be fixed. In any case, Chart data ragne will only be within Q3:W32 for Chart 5. (this is optional, as i will try using OFFSET function, but if someone can help, thanks).

[MrShorty]

Your file does not include any of your past failed attempts, which I think would help us understand what you are trying to do and what difficulty you are having.

Using VBA Macro , how can a) i Select specific chart ,

You do this using the ChartObjects collection. Latest version help file: https://msdn.microsoft.com/en-us/vba...s-object-excel I know there have been some changes over time to how the ChartObjects collection works. It is not clear to me which versioin of Excel/VBA that you need this compatible with, so you may want to spend some time with the help files for the versions you are using to see what you need to do specific to each version.
Sometimes I think the easiest way (especially as a beginner) to see how VBA accesses objects is to record a macro. I recorded this macro, starting from a cell in your worksheet, selecting different parts of the chart:

Please Login or Register  to view this content.

as for (b) and (c), I don't know what tutorials you have read, but I find this one by Jon Peltier easy to follow: https://peltiertech.com/link-excel-c...lues-in-cells/ It will show you how to link axis limits to cells, and how to make that code part of a change event that will fire whenever changes are made to those cells.

Where do you get stuck?

[analystbank]

yes, i read peltietetech, but will have to try again and again till i understand, thanks for head up and got clue how to select chart.
Instead of Active worksheet, i, i guess, i can make reference to specific Worksheet, and chart therein also.
I was referring Chart 5, as Chart5 , without space and single word, perhaps that could be the problem for non selection of chart, and also chartobjects, i was not knowing, i will retry. thanks, sir.

Anu further suggestion, as i want only Y Axis to autoscale. through cell value.

[MrShorty]

Instead of Active worksheet, i, i guess, i can make reference to specific Worksheet, and chart therein also,

Yes, you can. It is just a matter of knowing your way around Excel's object model (https://msdn.microsoft.com/en-us/VBA...-vba-reference again, remember that there are differences between versions, so research and test in all versions you need). Just as a single chartobject object is a member of the chartobjects collection, and you can specify a single chart object like activesheet.chartobjects("chart5"), you can specify a single worksheet like thisworkbook.sheets("sheet1") or activeworkbook.sheets("sheet5") [note the difference between the thisworkbook object and the activeworkbook object].

What part of Peltier's tutorial are you having trouble with?