Macro To Edit Chart Elements

jebediah · Aug 7th 2008

I am writing a macro to change the font and size of the axis, axis titles, legend, etc. in my excel chart. Here is what I have so far:

Code

Sub EditPlots()
'
' EditPlots Macro
'
        'Selects Chart
            Sheets("Chart1").Select
            
        'Selects Primary Vertical Axis and changes font to Arial and size to 14
            ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Select
            Selection.Font.Name = "Arial"
            Selection.Font.Size = 14
                    
        'Selects Secondary Vertical Axis and changes font to Arial and size to 14
            ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
            Selection.Font.Name = "Arial"
            Selection.Font.Size = 14
         
        'Selects Primary Horizontal Axis and changes font to Arial and size to 14   
            ActiveChart.Axes(xlCategory).AxisTitle.Select
            Selection.Font.Name = "Arial"
            Selection.Font.Size = 14


        'Selects Legend and changes font to Arial and size to 12    
            ActiveChart.Legend.Select
            Selection.Font.Name = "Arial"
            Selection.Font.Size = 12
            
            ActiveChart.Axes(xlCategory).Select
        [B]'***Here is where it stops working, and I don't know why...***[/B]
            Selection.Font.Name = "Arial"
            Selection.Font.Size = 12
            
            ActiveChart.Axes(xlCategory, xlPrimary).Select
            Selection.Font.Name = "Arial"
            Selection.Font.Size = 12
'
End Sub

Display More

Once the code gets to the point of changing the size and font of the numbers on the horizontal axis it crashes. See code above, I have put in notation where it stops working. Any help would be greatly appreciated.

Yard · Aug 7th 2008

Re: Edit Chart Labels

Welcome to Ozgrid!

Help with what?!

Robert B · Aug 7th 2008

Re: Macro To Edit Chart Elements

Hi

it seems that there might be a bit of identification missing, based purely on previous format of your code;

Code

ActiveChart.Axes(xlCategory).Select

should there be something like

Code

ActiveChart.Axes(xlCategory.xlSecondary).Select

HTH but apologies if I have missed your point entirely

Robert

Yard · Aug 7th 2008

Re: Edit Chart Labels

Quote from Yard

Welcome to Ozgrid!

Help with what?!

Oops, I didn't see your annotation the first time. Must get me specs cleaned....

jebediah · Aug 7th 2008

Re: Macro To Edit Chart Elements

When I step through the code (using F8) I can see it select each title and change the font and size. When the code gets to the horizontal axis where I am having problems I can see it select the correct axis, but then it fails. If I break right after it selects the code I can change the font and size manually, so the select feature works. I tried your suggestion and that also selects the correct axis and also fails once I try to change the font.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Ok got it working here. I had to reference the ticklabels...Here is the final code:

Code

Sub EditPlots() 
            '
            ' EditPlots Macro


            Sheets("Chart1").Select
            
            ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Font.Name = "Arial"
            ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Font.Size = 14
            
            ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Font.Name = "Arial"
            ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Font.Size = 14
            
            ActiveChart.Axes(xlCategory).AxisTitle.Font.Name = "Arial"
            ActiveChart.Axes(xlCategory).AxisTitle.Font.Size = 14
            
            ActiveChart.Legend.Font.Name = "Arial"
            ActiveChart.Legend.Font.Size = 12
            
            ActiveChart.Axes(xlCategory).TickLabels.Font.Name = "Arial"
            ActiveChart.Axes(xlCategory).TickLabels.Font.Size = 12
            
            ActiveChart.Axes(xlValue, xlPrimary).TickLabels.Font.Name = "Arial"
            ActiveChart.Axes(xlValue, xlPrimary).TickLabels.Font.Size = 12
            
            ActiveChart.Axes(xlValue, xlSecondary).TickLabels.Font.Name = "Arial"
            ActiveChart.Axes(xlValue, xlSecondary).TickLabels.Font.Size = 12


     End Sub

Display More

Macro To Edit Chart Elements

Participate now!

Share