conditional number formats for y-axis labels

  • Thread starter Thread starter sasquatch
  • Start date Start date
S

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?
 
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
 
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.
 
Use the macro recorder to get the syntax you need, then insert it into
the existing routine.

- Jon
 
Back
Top