Changing Chart Min & Max Axis Values via VBA

**TheRobsterUK** · 11-19-2019, 11:19 AM

I want to change the min and max y-axis values on a chart depending on the values in two cells on another worksheet. The code below is in the worksheet code module for the tab called "Dashboard(RR)" and first it checks to see if cell J3 has been changed (options from a drop-down menu) and if True then change the values on corresponding chart "RR_chart_2":

Please Login or Register  to view this content.

When I run this however I get the error "Runtime error '438' object doesn't support this property". I've used the macro recorder to change the axis min / max values manually and the code looks similar, but obviously I'm doing something wrong. I would guess it's a scope / object reference problem but can't figure out what's wrong. Anyone got any ideas???

Thanks :-)

**TheRobsterUK** · 11-19-2019, 11:29 AM

I thought this might fix it but I still get an error:

Please Login or Register  to view this content.

This time I get "The specified dimension is not valid for the current chart type", although I know the values in ranges L27 and L28 I'm referring to are definitely valid numbers and I can manually change the chart min and max settings to those exact numbers!

**MrShorty** · 11-19-2019, 01:55 PM

I don't access charts very often with VBA, but I think the error is about specifying the chart object -- not really about changing the axis scale. I think what happens is that there is both a Chart object and a ChartObject object, and you need to specify both when referencing an embedded. From other examples I see around the net, this change to post #1 should work (or at least do something different:

Please Login or Register  to view this content.

Notice that there is both a ChartObjects object and a separate Chart object within the ChartObject object (don't ask me why).

If it helps, Jon Peltier has a nice tutorial here for linking axis parameters to cells: https://peltiertech.com/link-excel-c...lues-in-cells/ If you are really excited by this, you might follow the link at the end of that essay to the one about using a UDF for the task.