none
Bug in ChartFormat object, Excel 2010? RRS feed

  • Question

  • I am trying to change the color of the font used in the axis of a pivot chart but I just can't find a way.

    The macro recorded when I do the action manually taught me that the code below was supposed to work, but it raises an error. Here is the code that, in my opinion, shows the bug:

    Sub BugInChartFormatObject()
    
      Dim cho As ChartObject
      Dim ax As Axis
      Dim cft As ChartFormat
      Dim txt As TextFrame2
      
      
      Set cho = shCharts.ChartObjects("Chart XYZ")
      Set ax = cho.Chart.Axes(xlCategory)
      Set cft = ax.Format
      
      Debug.Print TypeName(cft.TextFrame2)
      
    End Sub

    Chart XYZ is a Pivot Chart. Place a pivot chart in a sheet and try to access its axis' font. No way. It can be the xlCategory axis or the xlValue axis, the same error is raised.

    "Run-time error '-2147467259 (80004005)1:
    Method 'TextFrame2' of object 'ChartFormat' failed"

    The font was supposed to be accessible using this code:

      With cho.Chart.Axes(xlValue).Format.TextFrame2.TextRange.Font
        .Fill.ForeColor.ObjectThemeColor = soThemeColorAccent1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = -0.25
      End With

    But it does not work. Can someone please shed some light on this issue?

    I don't know if this is new stuff from Excel 2010 or if it already existed in Excel 2007. I jumped from Excel 2003 directly to 2010 so I have zero experience in Excel 2007.

    Thank you.

    Friday, June 25, 2010 4:42 AM

Answers

  • Hello,

    The Format object is read only for an axis. We could not use this object to set fore color. For more information, please see this MSDN page: http://msdn.microsoft.com/en-us/library/bb242488(office.12).aspx. So you could select this axis, and use Selection.Format to set the fore color. Code like this,

        ActiveChart.Axes(xlCategory).Select
        With Selection.Format.Fill
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorAccent1
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = -0.25
            .Solid
        End With

    Please feel free to follow up, if this post does not help you.

    Best regards,
    Bessie 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Proposed as answer by Bessie Zhao Wednesday, June 30, 2010 10:21 AM
    • Marked as answer by Bessie Zhao Friday, July 2, 2010 2:01 AM
    Tuesday, June 29, 2010 9:48 AM

All replies

  • Hello,

    The Format object is read only for an axis. We could not use this object to set fore color. For more information, please see this MSDN page: http://msdn.microsoft.com/en-us/library/bb242488(office.12).aspx. So you could select this axis, and use Selection.Format to set the fore color. Code like this,

        ActiveChart.Axes(xlCategory).Select
        With Selection.Format.Fill
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorAccent1
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = -0.25
            .Solid
        End With

    Please feel free to follow up, if this post does not help you.

    Best regards,
    Bessie 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Proposed as answer by Bessie Zhao Wednesday, June 30, 2010 10:21 AM
    • Marked as answer by Bessie Zhao Friday, July 2, 2010 2:01 AM
    Tuesday, June 29, 2010 9:48 AM
  •  

    Hello,

     

    I found the same problem but i want to change de font color and size of the category names in the category axis. It seems that i have no way around the TextFrame2, have I?

     

    Thank you!

     



    Thursday, August 18, 2011 12:53 PM
  • Instead of TextFrame2, use TickLabels like following

    ActiveChart.Axes(xlCategory).ticklabels.font.color = RGB(255, 0, 0)

    Tuesday, December 20, 2011 12:07 AM
  • This, I find, is rather inefficient. I would rather not have to select an axis each time I want to format its text range. Is there a proposed change to this?
    Friday, January 10, 2014 11:46 AM
  • Thank you! I was having the same problem with recorded code not working, only I was trying to set font characteristics. Using TickLabels worked like a charm! I had spent an hour trying to figure out what was wrong. Scott

    Writer

    Tuesday, July 22, 2014 6:41 PM