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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|