Consulting

Results 1 to 8 of 8

Thread: Custom Number format in charts vs. cells

  1. #1
    VBAX Expert TrippyTom's Avatar
    Joined
    Jul 2005
    Location
    New York, NY (USA)
    Posts
    556
    Location

    Custom Number format in charts vs. cells

    Hi gang,

    Unfortunately we're switching to Office 2007 at work and in testing I ran into this strange "bug?" that Microsoft doesn't seem to care about because it's behaving the same way in Office 2010 as well.

    Here's the situation. If you open the attached file, I have the same custom number format in the chart's data labels and in the cells. Why does it work in the cells but not when used on data labels in a chart?

    According to this site it should be [color25] that I'm going after (a custom red).
    click here

    I'm so confused.
    Office 2010, Windows 7
    goal: to learn the most efficient way

  2. #2
    VBAX Expert TrippyTom's Avatar
    Joined
    Jul 2005
    Location
    New York, NY (USA)
    Posts
    556
    Location
    forgot to attach my file sample...
    Office 2010, Windows 7
    goal: to learn the most efficient way

  3. #3
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,778
    Location
    Curiously, as far as I can tell, every other number code seems to work the same in a cell as in the datalabels except for [color25].

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    You have chosen one of the chart CIs, and guess what, 25 is dark blue, which is what you get. Use numbers between 1-16 and 33-56.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,785
    Location
    I'm not sure that you can do that -- from Help ...


    Specify colors To specify the color for a section of the format, type the name of one of the following eight colors enclosed in square brackets in the section. The color code must be the first item in the section. [Black][Green][White][Blue][Magenta][Yellow][Cyan][Red]

    [red] works



    Paul

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    You can Paul, it is an (AFAIK) an undocumented feature.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Expert TrippyTom's Avatar
    Joined
    Jul 2005
    Location
    New York, NY (USA)
    Posts
    556
    Location
    XLD, I changed one of the other colors in the range you specified and it's still behaving the same way. It doesn't seem to be honoring the same set of colors at all, because I changed color38 to the my custom color and it still chose the color from the original set instead of my custom color.

    If you go into Tools > Options > SAVE settings > then click on the COLORS button it will show you the color window that the [color] feature follows in formulas, but it doesn't seem to be the same for charts.

    Note: This works as expected in Excel2003, but not in 2007 or 2010.
    Office 2010, Windows 7
    goal: to learn the most efficient way

  8. #8
    Ok. Thanks for these instructions. It is really helpful. My VBA is now working.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •