Excel 2003 VBA How do I make TickLabelSpacing automatic?

mss72
mss72 used Ask the Experts™
on
I am using Excel 2003 and wish to make the spacing of the category labels on the Category (x) axis automatic, using VBA. Using TickLabelSpacing requires a number between 1 and 31999 and does not accommodate xlAutomatic.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2011

Commented:
There is no option for automatic. The closest thing to it is to not specify a value and let Excel default to a value.

Author

Commented:
The problem is that Excel normally does set the TickLabelSpacing automatically in a new chart but once the spacing is set to a specific value you cannot return to the automatic mode. Setting TickLabelSpacing to Null seems to have no effect. Setting the value to 0, Empty, Nothing or "" throws an Error.
Most Valuable Expert 2011
Top Expert 2011

Commented:
I don't think there's an option (in later versions there is a TickLabelSpacingIsAuto property), but I'll do some testing.
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Author

Commented:
I am still unclear why a new chart in Excel 2003 does have automatic TickLabelSpacing and yet once a value is supplied you cannot return to automatic
Most Valuable Expert 2011
Top Expert 2011
Commented:
That just seems to be the way it was designed.
Once set I cannot find any way to reset it to automatic other than creating a new chart. Removing the axis and then reinstating it doesn't work; changing it from category to timescale and back doesn't work.
Comparing all the properties in VBA of a newly created automatically scaling axis with a manually scaled axis reveals no differences, so whatever the setting is, it is not exposed in VBA (at least not in 2003)

Author

Commented:
The problem remains because of the design of EXCEL. This is not the fault of the person answering the question

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial