Errors attempting to format Excel chart axes using a Macro? (Excel 2011, Mac OS 10.6.8) -- help appreciated. RRS feed

  • Question

  • Previously posted to Office:mac

    Create a simple Line chart. Select the chart. Start Recording a Macro. Format the value axis   (change  line width and font size for the labels). Stop Recording.

    Now try to run the Macro   an error message appears   Run-time error '13'   Type mismatch
    When you click debug the line highlighted is

    In Excel 2004 (which fortunately I can still use because I haven't upgraded to Lion)  xlvalue was not in quotation marks.  (The Excel 2004 Macro works just fine in Excel 2004 -- but the properties have changed in 2011 so the macro isn't portable between versions.)

    If I remove the quotation marks from the "xlValue"  I've seen very varied behaviour.  Sometimes the whole application crashes. With this very simple example it does change the line width but then stops saying  Method 'TextFrame2' of object 'ChartFormat' failed.

    The complete macro as recorded in Excel 2011 but with the quotation marks around xlValue removed is

    Sub FormatChart2011()
    ' FormatChart2011 Macro
        ActiveSheet.ChartObjects("Chart 1").Activate
        With Selection.Format.Line
            .Visible = msoTrue
            .Weight = 2
        End With
        Selection.Format.TextFrame2.TextRange.Font.Size = 14
    End Sub

    Compared to Excel 2004 this is very unfriendly.  Can anyone give me any advice on how to get round this type of difficulty?
    Answers on the earlier forum: 1) suggested posting here  2) confirmed error and noted there are two -- Mac specific error involving the quotation marks and an error apparently in common with Excel 2007 for Windows concerned with TextFrame2.
    It really should be straightforward to format an axis so why is there apparently something wrong?
    Sunday, November 20, 2011 4:28 PM