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
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



sasquatch wrote:
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?

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" wrote:

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
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



sasquatch wrote:
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?

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

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



sasquatch wrote:
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" wrote:

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
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



sasquatch wrote:
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?