Formatting Multiple Charts

**TSMIII** · 04-26-2011, 02:25 AM

I have 12 separate databases built, each with 102 sheets and each sheet containing its own chart. I need to reformat 1) the font size on all the charts and 2) the y-axis title on all the charts. How can I do this?

**Andy Pope** · 04-26-2011, 05:55 AM

Code is probably the simplest and quickest approach.

Use the macro recorder whilst formatting the chart.
The build some simple looping around this to process all charts.

**TSMIII** · 04-26-2011, 11:36 AM

OK, I recorded the macro and tested it. It works fine, but would you mind walking me through the looping process?

**Andy Pope** · 04-26-2011, 11:39 AM

Some along these lines for all the charts in the active workbook

Sub X
Dim chtTemp as chart

for each chtTemp in activeworkbook.charts
     chttemp.activate
     ' your recorded code, which problably uses the Activechart object
     ' <your code>

Next

End Sub

**TSMIII** · 04-27-2011, 02:27 AM

Thanks a lot. That worked great. I have another question, though. What would the macro be to change the chart title on all the charts?

**Andy Pope** · 04-27-2011, 07:29 AM

Do the same thing, record a macro whilst doing the change and then add that to the code.

If you still need help you need to provide more information, like how do you know what the title for a particular chart should be.

**TSMIII** · 04-27-2011, 11:33 AM

I would like all of the charts to be title "1980 Democratic Primary"

**Andy Pope** · 04-27-2011, 11:37 AM

So if you had used the recorder you would have got code similar to

Activechart.HasTitle =True 
Activechart.ChartTitle.Text = "1980 Democratic Primary"

**TSMIII** · 05-18-2011, 03:22 PM

Thanks for all your help that worked great. Now, I have a new question. This is what I have. A database with 102 charts (each on their own sheet), one for each county in Illinois. Each sheet and chart are named for the county. I have a 14 database like this. I was asked to recreate these for all other states. Naming the sheets was the big problem. I was able to write a macro that renamed all the sheets equal to a range of cell values. SO, I was able to rename all my sheets with the new county names. However, the charts are still the old county names. Is there a way to rename all the charts with the titles pulled from a range of values like i did for the sheets?

**johnwm** · 07-07-2011, 01:28 AM

Sub Macro1()
'Format multiple charts
Dim chtTemp As Chart
For Each chtTemp In ActiveWorkbook.Charts
chtTemp.Activate
ActiveChart.ChartTitle.Select
With Selection.Format.TextFrame2.TextRange.Font.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
.Solid
End With
Next
End Sub

Why will this not work. Stepping through the code it does not enter the loop. Using V2010.

**romperstomper** · 07-07-2011, 03:16 AM

Hi, and welcome to the forum.

Unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

Please also note the forum rule about code tags.