Formating chart object (plot) in VBA using 2010 Excel macro

  • I've used the macro recorder to format a plot area in a chart (e.g. fonts, font size, position) to test. To test the code, I rearranged the chart objects of the original chart and changed the font, etc. When I run the macro on the very same chart I recorded the macro on, I get a 'Run-time error "2147467259 (80004005)': Method 'TextFrame2' of object 'ChartFormat' failed". I'm not sure why the same code generated through the macro feature fails when I use the same code that the macro generated while recording my desired output. This error occurs even when I've kept it simple as just changing the font and size of the x axis.


    I'm really new to VBA, but am finding that using the macro recorder is helping me to understand the basics at the very least - just confusing when the very function used to generate a code fails to run it's own creation. I'm finding that formatting the plot area with VBA has been a little bit enigmatic for me, as it seems to take any code only arbitrarily (macro can change the font for everything, but won't change the axes fonts or sizes and won't alter plot dimensions without giving an error or not executing) - and I'm using strictly the macro recorder to generate each test on one single chart. The code as generated by the Excel 2010 macro recorder function follows:


    Regards,
    Zach

  • Re: Formating chart object (plot) in VBA using 2010 Excel macro


    Apparently, it's a bug. Try the following instead...


    Code
    ActiveChart.Axes(xlValue).TickLabels.Font.Name = "Arial"


    and


    Code
    ActiveChart.Axes(xlValue).TickLabels.Font.Bold = msoTrue


    Hope this helps!

  • Re: Formating chart object (plot) in VBA using 2010 Excel macro


    Thank you. The code runs but now the tick labels disappear from the graph. Excel says they are being displayed but I can't get them back, even by going into Layout.

  • Re: Formating chart object (plot) in VBA using 2010 Excel macro


    When I test the code in Excel 2010, it works fine.


    You say that you've tried the following?


    Code
    Layout > Axes > Primary Vertical Axis > Show Default Axis


    Also, by chance, is the font color for the axis is the same color as the background color for the chart?

  • Re: Formating chart object (plot) in VBA using 2010 Excel macro


    I have tried your recommendation. The tick labels and the entire axis "disappear" and I can't get them back going through layout and enabling default axis so there is no font or axis to select. Excel shows them as enabled. Even disabling and enabling produces no element, but the graph does shift, so it's doing something - just not visually displaying the elements. It is run on an existing xy scatter graph with default axes and labels, but I wouldn't think that would conflict since it is such a simple command. I've deleted a legend in Excel graphs before and have had the same problem getting it back trying to enable it in layout. If the below code looks acceptable, I'll have to go to the original code that produced the graph and take a look at that, but like I say it's just a default setting and the only code written to alter the axis was what I did to the Format Axis (min, max, units, reverse order).


    The entire code for changing axis label fonts:

  • Re: Formating chart object (plot) in VBA using 2010 Excel macro


    You need to enclose the font name within quotes. Try...


    Code
    With ActiveChart.Axes(xlCategory).TickLabels.Font
        .Name = "Arial"
        .Size = 8
        .Bold = msoTrue
    End With
     
    With ActiveChart.Axes(xlValue).TickLabels.Font
        .Name = "Arial"
        .Size = 8
        .Bold = msoTrue
  • Re: Formating chart object (plot) in VBA using 2010 Excel macro


    Hi
    I have also a question about formating chart object as follows;


    I recorded macro with mouse gesture, but it doesn't works. I cannot understand why ? This macro recorded by excel 2013 but it does not works.
    Could you please help me ?


    ActiveSheet.ChartObjects("Grafik 1").Activate
    ActiveChart.Axes(xlCategory).AxisTitle.Select
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Sales)"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "Sales)"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 6).ParagraphFormat
    .TextDirection = msoTextDirectionLeftToRight
    .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 6).Font
    .BaselineOffset = 0
    .Bold = msoTrue
    .NameComplexScript = "Arial"
    .NameFarEast = "Arial"
    .Fill.Visible = msoTrue
    .Fill.ForeColor.RGB = RGB(0, 0, 0)
    .Fill.Transparency = 0
    .Fill.Solid
    .Size = 9
    .Italic = msoFalse
    .Kerning = 12
    .Name = "Arial"
    .UnderlineStyle = msoNoUnderline
    .Strike = msoNoStrike
    End With
    Range("N49").Select

  • Re: Formating chart object (plot) in VBA using 2010 Excel macro


    Welcome to Ozgrid, ongbey.


    Please start your own thread for your question. When you do, please follow the board rules and give it a concise title that accurately describes your issue. If you post code, use code tags (click the Rules link under your name in the post sidebar for a quick description).


    It also helps if you describe the issue, where it occurs and any error message. Saying "I recorded this macro and it didn't work" is pointless.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!