Change a (F11) chart's axis values using cell values

[JimmyWilliams]

Hi Guys,

I want to be able to edit axis of a chart (a "F11" chart not a chart within a sheet) by entering in numbers within cells.
See the "Chart1 tab" and "Chart2 tab"

The following code words for a chart within a sheet, but I dont know how to select a chart if that chart is it's own tab.

Please Login or Register  to view this content.

[Andy Pope]

Try this modification to handle chart sheets and chart objects on chart sheets

Please Login or Register  to view this content.

Also check the cell formulas as you need to change the chart object names or use "" is a standard chart sheet.

[JimmyWilliams]

Hi Andy,

Super cool what you've done so far.

How did you determine that the objects were "Chart 1" and "Chart 4" within Chart1?
I normally need to use the "Name box" (in the top left corner) to determine an object's name. However both "Chart1","Chart 1" and "Chart1","Chart 4" don't show in the "Name box".



A little bit off topic, I edited the code to allow for Major and Minor axis units to be determined.
And I feel like this code repeats its self unnecessarily and could be tidier (I'm hoping to keep the code for a really long time, so it'd be nice if it was more compact, so I can add more to it /etc.)


A little bit even more off topic,
While you were typing your code I tried to add in Major/Minor Axis units (See below).
However I am having trouble with optional variables as excel seems to stop working if they are not defined, I have a worked around this by using "goto line1" however this wouldn't work for more complicated programs.
I would like a way of doing something like: "If NO-DEFINED-USER-INPUT then.."

Can you grab what you've done with mine and put it all together?
Please and thank you.

Please Login or Register  to view this content.

[Andy Pope]

I used the immediate window to find the names of the shapes on the chart sheet

Please Login or Register  to view this content.

If you declare the optional arguments as Variant you can use the IsMissing function.

[JimmyWilliams]

I used the immediate window to find the names of the shapes on the chart sheet

Please Login or Register  to view this content.

If you declare the optional arguments as Variant you can use the IsMissing function.

Hi Andy,

How do I use the code:

Please Login or Register  to view this content.

Do i put it into the immediate window, or into a sub?

[Andy Pope]

You would use that example bit of code in the immediate window.