How do I set Chart Titles all to the same font size in VBA
- Thread starter ianb
- Start date
IanB
Try the following VBA Code
IanH
[pre]
[/pre]
Try the following VBA Code
IanH
[pre]
Code:
Sub Set_Chart_Titles()
Dim ws As Worksheet
Dim ch As ChartObject
Dim Fnt As String
Dim FntSz As Double
Dim FntR As Integer
Dim FntG As Integer
Dim FntB As Integer
Fnt = "Arial" 'Set Font type
FntSz = 18 'Set Font Size
FntR = 25 'Set Font Color Red
FntG = 100 'Set Font Color Green
FntB = 50 'Set Font Color Blue
For Each ws In Worksheets
For Each ch In ws.ChartObjects
ch.Activate
ch.Chart.ChartTitle.Select
Selection.Format.TextFrame2.TextRange.Font.Name = Fnt
Selection.Format.TextFrame2.TextRange.Font.Size = FntSz
With Selection.Format.TextFrame2.TextRange.Font.Fill
.ForeColor.RGB = RGB(FntR, FntG, FntB)
.Transparency = 0
.Solid
End With
Next ch
Next ws
End Sub
ianb
Member
Hui,
This has been adapted to keep the formula's I would like to also set the font size of the charts axis to 8 and the titles to 14.
Can you advise please ?
Sub ChartTitlesLight()
Dim ws As Worksheet
Dim ch As ChartObject
For Each ws In Worksheets
For Each ch In ws.ChartObjects
ch.Activate
ActiveChart.ClearToMatchStyle
ActiveChart.ChartStyle = 18
ActiveChart.ClearToMatchStyle
Next ch
Next ws
'Font Size and Colour in Charts
'Dim ws As Worksheet
'Dim ch As ChartObject
Dim Fnt As String
Dim FntSz As Double
Dim FntR As Integer
Dim FntG As Integer
Dim FntB As Integer
Fnt = "Rockwell (Body)" 'Set Font type
FntSz = 14 'Set Font Size
'Black Text
FntR = 0 'Set Font Color Red
FntG = 0 'Set Font Color Green
FntB = 0 'Set Font Color Blue
For Each ws In Worksheets
For Each ch In ws.ChartObjects
ch.Activate
ch.Chart.ChartTitle.Select
With Selection.Font.Name = Fnt
Selection.Font.Size = FntSz
Selection.Font.Color = RGB(FntR, FntG, FntB)
End With
Next ch
Next ws
End Sub
This has been adapted to keep the formula's I would like to also set the font size of the charts axis to 8 and the titles to 14.
Can you advise please ?
Sub ChartTitlesLight()
Dim ws As Worksheet
Dim ch As ChartObject
For Each ws In Worksheets
For Each ch In ws.ChartObjects
ch.Activate
ActiveChart.ClearToMatchStyle
ActiveChart.ChartStyle = 18
ActiveChart.ClearToMatchStyle
Next ch
Next ws
'Font Size and Colour in Charts
'Dim ws As Worksheet
'Dim ch As ChartObject
Dim Fnt As String
Dim FntSz As Double
Dim FntR As Integer
Dim FntG As Integer
Dim FntB As Integer
Fnt = "Rockwell (Body)" 'Set Font type
FntSz = 14 'Set Font Size
'Black Text
FntR = 0 'Set Font Color Red
FntG = 0 'Set Font Color Green
FntB = 0 'Set Font Color Blue
For Each ws In Worksheets
For Each ch In ws.ChartObjects
ch.Activate
ch.Chart.ChartTitle.Select
With Selection.Font.Name = Fnt
Selection.Font.Size = FntSz
Selection.Font.Color = RGB(FntR, FntG, FntB)
End With
Next ch
Next ws
End Sub
SirJB7
Excel Rōnin
Hi, ianb!
Give a look at this file:
https://dl.dropbox.com/u/60558749/How%20do%20I%20set%20Chart%20Titles%20all%20to%20the%20same%20font%20size%20in%20VBA%20%28for%20inab%20from%20Hui%20at%20chandoo.org%29.xlsm
I just created a new workbook, added silly tables in each of three worksheets, inserted more silly charts for each table, then I created a new module from the VBA editor, copied and pasted Hui's code, and run the macro.
The result? What you were asking for and what Hui's posted 2 months ago at the top of this topic.
If your new question is a continuation of the above post:
I didn't check your last code, but if it's an update of some other code and doesn't work, I'd humbly suggest you to stick to the above Hui's code which in fact works, and then with the old method of try&error slowly advance one step at a time, so as to not get involved with code that you wouldn't be able to handle.
If it isn't:
Then start a new topic as indicated in the green sticky topics at this forums home page.
Regards!
Give a look at this file:
https://dl.dropbox.com/u/60558749/How%20do%20I%20set%20Chart%20Titles%20all%20to%20the%20same%20font%20size%20in%20VBA%20%28for%20inab%20from%20Hui%20at%20chandoo.org%29.xlsm
I just created a new workbook, added silly tables in each of three worksheets, inserted more silly charts for each table, then I created a new module from the VBA editor, copied and pasted Hui's code, and run the macro.
The result? What you were asking for and what Hui's posted 2 months ago at the top of this topic.
If your new question is a continuation of the above post:
I didn't check your last code, but if it's an update of some other code and doesn't work, I'd humbly suggest you to stick to the above Hui's code which in fact works, and then with the old method of try&error slowly advance one step at a time, so as to not get involved with code that you wouldn't be able to handle.
If it isn't:
Then start a new topic as indicated in the green sticky topics at this forums home page.
Regards!
ianb
Member
Thanks. It is a great program and had to be edited to keep the formula is the title option.
The part I can not solve is how if I set
FntSz = 14 'Set Font Size
does the title set to 14 size and the lower part of the chart x and y axis is set to 10 automatically.
I would like the lower part to be set to 8 not 10 ?
The part I can not solve is how if I set
FntSz = 14 'Set Font Size
does the title set to 14 size and the lower part of the chart x and y axis is set to 10 automatically.
I would like the lower part to be set to 8 not 10 ?
SirJB7
Excel Rōnin
Hi, ianb!
As you stated firstly and at topic's title, and as Hui's posted, this code works only for titles. I don't remember exactly the names of the properties for other parts of the chart, but there's nothing than couldn't be found into built-in Excel help.
Do you think you can handle that search and manage to update the above code? If yes, then give a try; if not, well, you'll have to wait a little until somebody that remember that read this and answer or that I found a little of time to dig into things that I don't like of Excel, like charts.
Regards!
As you stated firstly and at topic's title, and as Hui's posted, this code works only for titles. I don't remember exactly the names of the properties for other parts of the chart, but there's nothing than couldn't be found into built-in Excel help.
Do you think you can handle that search and manage to update the above code? If yes, then give a try; if not, well, you'll have to wait a little until somebody that remember that read this and answer or that I found a little of time to dig into things that I don't like of Excel, like charts.
Regards!
NARAYANK991
Excel Ninja
Hi Ian ,
You can try including this code in your existing procedure ; I have put all the elements of the code together , but when you include this in your procedure , you can put the different elements ( such as the declarations , assignments and the code proper ) in their correct places within your procedure.
[pre]
[/pre]
If you want the same action to take place for the value axis also , add one more statement within the IF statement :
ch.Chart.Axes(xlValues).TickLabels.Font.Size = 8
Narayan
You can try including this code in your existing procedure ; I have put all the elements of the code together , but when you include this in your procedure , you can put the different elements ( such as the declarations , assignments and the code proper ) in their correct places within your procedure.
[pre]
Code:
Dim typelist As Variant, mr As Variant
Dim ct As Integer
typelist = Array(xl3DPie, xl3DPieExploded, xlPie, xlPieExploded, xlPieOfPie)
ct = ch.Chart.ChartType
On Error Resume Next
mr = Null
mr = Application.WorksheetFunction.Match(ct, typelist, 0)
On Error GoTo 0
If IsNull(mr) Then
ch.Chart.Axes(xlCategory).TickLabels.Font.Size = 8
End IfIf you want the same action to take place for the value axis also , add one more statement within the IF statement :
ch.Chart.Axes(xlValues).TickLabels.Font.Size = 8
Narayan