conditional number formats for y-axis labels

[sasquatch]

I have a chart that dynamically sets the upper and lower values and
increments based on a selected series using vba script. (Thanks to previous
posters and Jon Peltier for help with that!) I would now like to dynamically
set the number format for the tick mark labels.

For example, in one series, I may have labels of -1,000,000, -500,000, 0,
500,000, 1,000,000. In this case, I would like to format that as -1M, -.5M,
0, .5M, 1M. If I select a different series to be plotted in the same chart,
the gridlines may be -200,000, -100,000, 0, 100,000, 200,000. I would like
these to be formatted as -200K, -100K, 0, 100K, 200K.

Any thoughts on how to conditionally set those number formats?

 

[Jon Peltier]

If you're using VBA anyway to set the axis scales, it's a matter of
adding one or two lines to set your number formats.

- Jon

 

[sasquatch]

Agreed; that is what I was thinking too. Unfortunately, I am a VBA newbie
and do not know how to reference the y-axis number format in VBA script, nor
how to actually set the format once it is referenced. I've tried looking in
several VBA resources, but to no avail.

 

[Jon Peltier]

Use the macro recorder to get the syntax you need, then insert it into
the existing routine.

- Jon