A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

conditional number formats for y-axis labels



 
 
Thread Tools Display Modes
  #1  
Old August 27th, 2009, 07:10 PM posted to microsoft.public.excel.charting
Sasquatch
external usenet poster
 
Posts: 16
Default conditional number formats for y-axis labels

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?

  #2  
Old August 28th, 2009, 04:28 AM posted to microsoft.public.excel.charting
Jon Peltier[_2_]
external usenet poster
 
Posts: 386
Default conditional number formats for y-axis labels

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?

  #3  
Old August 28th, 2009, 05:45 AM posted to microsoft.public.excel.charting
Sasquatch
external usenet poster
 
Posts: 16
Default conditional number formats for y-axis labels

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?


  #4  
Old August 29th, 2009, 04:20 AM posted to microsoft.public.excel.charting
Jon Peltier[_2_]
external usenet poster
 
Posts: 386
Default conditional number formats for y-axis labels

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?

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:38 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.