Andreas Hermle
asked on
Apply chart title properties of the currently selected chart to all the charts of the active worksheet
Dear Experts:
I wonder whether it is possible to re-format the chart title of ALL charts so that they match the chart title of the currently selected chart with respect to ...
... font size of the chart title
....font type (Arial, Calibri etc.)
....font property such as underline, bold or italic
I have attached a sample file for your convenience. In this file I have re-formatted the chart title of chart 1 to ...
.... Arial font
....font size of 12
....font property bold.
Is it possible by means of a macro to select chart 1 (the one with the re-formatted chart title) and apply above properties automatically to the chart titles of the other charts.
Help is much appreciated. Thank you very much in advance.
Regards, Andreas
ChartTitle-Change-ForAll-Charts.xlsm
I wonder whether it is possible to re-format the chart title of ALL charts so that they match the chart title of the currently selected chart with respect to ...
... font size of the chart title
....font type (Arial, Calibri etc.)
....font property such as underline, bold or italic
I have attached a sample file for your convenience. In this file I have re-formatted the chart title of chart 1 to ...
.... Arial font
....font size of 12
....font property bold.
Is it possible by means of a macro to select chart 1 (the one with the re-formatted chart title) and apply above properties automatically to the chart titles of the other charts.
Help is much appreciated. Thank you very much in advance.
Regards, Andreas
ChartTitle-Change-ForAll-Charts.xlsm
I just noticed that you wanted Italics and Underline as well. Please use this code.
Sid
Code
Sid
Code
Sub FormatCharts()
Dim i As Long
Dim fntSize As Double
Dim FntName As String
Dim isbold As Boolean, isIt As Boolean, isUnd As Boolean
Sheets("PieCharts ").ChartObjects(1).Activate
FntName = ActiveChart.ChartTitle.Font.Name
fntSize = ActiveChart.ChartTitle.Font.Size
isbold = ActiveChart.ChartTitle.Font.Bold
isIt = ActiveChart.ChartTitle.Font.Underline
isUnd = ActiveChart.ChartTitle.Font.Italic
For i = 2 To Sheets("PieCharts ").ChartObjects.Count
Sheets("PieCharts ").ChartObjects(i).Activate
With ActiveChart
.ChartTitle.Font.Name = FntName
.ChartTitle.Font.Size = fntSize
.ChartTitle.Font.Bold = isbold
.ChartTitle.Font.Italic = isIt
.ChartTitle.Font.Underline = isUnd
End With
Next
End Sub
ASKER
Hi Siddharth:
thank you very much for your swift support. I guess we are almost there.
The changed font name and font size is applied correctly to the other charts
... however the italic, and underline properties are sometimes applied to the other charts although they do not exist as properties of the first chart's chart title.
Any idea why?
Regards, Andreas
thank you very much for your swift support. I guess we are almost there.
The changed font name and font size is applied correctly to the other charts
... however the italic, and underline properties are sometimes applied to the other charts although they do not exist as properties of the first chart's chart title.
Any idea why?
Regards, Andreas
Hmm this is strange.
I just tried this
And it showed me "Underlined" even when the title is not underlined. Let me probe further.
Sid
I just tried this
If ActiveChart.ChartTitle.Font.Underline = xlUnderlineStyleNone Then
MsgBox "Not underlined"
Else
MsgBox "Underlined"
End If
And it showed me "Underlined" even when the title is not underlined. Let me probe further.
Sid
Also in the code in my first post I have it in reverse
isIt = ActiveChart.ChartTitle.Fon t.Underlin e
isUnd = ActiveChart.ChartTitle.Fon t.Italic
I meant it as
isIt = ActiveChart.ChartTitle.Fon t.Italic
isUnd = ActiveChart.ChartTitle.Fon t.Underlin e
Sid
isIt = ActiveChart.ChartTitle.Fon
isUnd = ActiveChart.ChartTitle.Fon
I meant it as
isIt = ActiveChart.ChartTitle.Fon
isUnd = ActiveChart.ChartTitle.Fon
Sid
I even tried this and it still gave me "underlined".
Sid
Sub Sample()
ActiveChart.ChartTitle.Font.Underline = xlUnderlineStyleNone
If ActiveChart.ChartTitle.Font.Underline = xlUnderlineStyleNone Then
MsgBox "Not underlined"
Else
MsgBox "Underlined"
End If
End Sub
Sid
Requesting other experts to join it,
Sid
Sid
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Gr8 :)
It works in 2007 as well.
Sid
It works in 2007 as well.
Sid
Brad, I am curious that the code that I gave, why doesn't it give the right results for underline and italics as it doesn't give any error as well?
Sid
Sid
ASKER
Hi Sid and Brad,
thank you very much for your quick and great support. Will check your answers tomorrow morning and give you feedback. Thank you again.
Regards, Andreas
thank you very much for your quick and great support. Will check your answers tomorrow morning and give you feedback. Thank you again.
Regards, Andreas
Sid,
It may be that your approach is consistent with Excel 2003 even though the chart object model has changed in Excel 2007 and 2010.
If you step through your code, you'll see that ActiveChart.ChartTitle.Fon t.Underlin e returns a Long result (1183516789) as opposed to the Integer value of xlUnderlineStyleNone (-4142)
What bugged me is that I got an error with
ge.Font could be left in the With statement as I was more keen to see if somebody else could test my code in Excel 2007 (Thanks for doing so).
Brad
It may be that your approach is consistent with Excel 2003 even though the chart object model has changed in Excel 2007 and 2010.
If you step through your code, you'll see that ActiveChart.ChartTitle.Fon
What bugged me is that I got an error with
With Sheets("PieCharts ").ChartObjects(1).Chart.ChartTitle.Format.TextFrame2.TextRange.Font
FntName = .Name 'Got an error on this statement in Excel 2010
That's why I have the repetitious code in my With block. I didn't experiment to see how much of .Format.TextFrame2.TextRanBrad
In Excel 2010, I can move all but the .Font part into the With statement:
Brad
Sub FormatCharts()
Dim i As Long
Dim fntSize As Double
Dim FntName As String
Dim isbold As Boolean, isIt As Boolean
Dim isUnd As Integer
Dim cht As Chart
With Sheets("PieCharts ").ChartObjects(1).Chart.ChartTitle.Format.TextFrame2.TextRange
FntName = .Font.Name
fntSize = .Font.Size
isbold = .Font.Bold
isUnd = .Font.UnderlineStyle
isIt = .Font.Italic
End With
For i = 2 To Sheets("PieCharts ").ChartObjects.Count
Sheets("PieCharts ").ChartObjects(i).Activate
With Sheets("PieCharts ").ChartObjects(i).Chart.ChartTitle.Format.TextFrame2.TextRange
.Font.Name = FntName
.Font.Size = fntSize
.Font.Bold = isbold
.Font.UnderlineStyle = isUnd
.Font.Italic = isIt
End With
Next
End Sub
Brad
Your code works just fine in 2007. I just tested it. :)
I am curious that this line in my code works just fine
FntName = ActiveChart.ChartTitle.Fon t.Name
So why the extra need to use ".Format.TextFrame2.TextRa nge.Font.N ame" has me perplexed.
Sid
I am curious that this line in my code works just fine
FntName = ActiveChart.ChartTitle.Fon
So why the extra need to use ".Format.TextFrame2.TextRa
Sid
If I look at it in a different way then what are the different components of a Chart Title? It looks like TextFrame is one as your code works with that. What else is included in the chart title?
Sid
Sid
IntelliSense doesn't present a choice of ActiveChart.ChartTitle.Fon t for me in Excel 2010. The Font object is reached instead through ActiveChart.ChartTitle.For mat.TextFr ame2.TextR ange.Font
I assume this is a change in the object model. If the old way continues to work, count yourself lucky.
The reason I have the cht variable in my code is so I can use the IntelliSense to complete my statements. Once I have it figured out, I then revert back to the With block (and delete the statement involving cht).
Brad
I assume this is a change in the object model. If the old way continues to work, count yourself lucky.
The reason I have the cht variable in my code is so I can use the IntelliSense to complete my statements. Once I have it figured out, I then revert back to the With block (and delete the statement involving cht).
Brad
Ah! I checked the intellisense of ChartTitle. It doesn't have a property called .Font, yet the line
FntName = ActiveChart.ChartTitle.Fon t.Name
doesn't give any error. So I am lucky after all :-D
Thanks Brad for the explanation. Learnt something new today!
Sid
FntName = ActiveChart.ChartTitle.Fon
doesn't give any error. So I am lucky after all :-D
Thanks Brad for the explanation. Learnt something new today!
Sid
The ChartTitle object has 25 methods and properties in Excel 2010. Of those, the only ones that looked useful were .Characters, .Format and .Text. In the end, .Format was working for me.
Makes sense.
BTW, Saw your seminar on EE. Amazing Job! :)
Andreas: Sorry for hijacking this thread but we had a pretty interesting conversation about the properties of CharTitle above. :)
Sid
BTW, Saw your seminar on EE. Amazing Job! :)
Andreas: Sorry for hijacking this thread but we had a pretty interesting conversation about the properties of CharTitle above. :)
Sid
ASKER
Hi Brad and Sid:
Brad: great, works like a charm. Thank you very much for your professional support. I really appreciate it!
Sid: I sure like this kind of hijacking. I also would like to express my gratitude to you for your great support.
Regards, Andreas
Brad: great, works like a charm. Thank you very much for your professional support. I really appreciate it!
Sid: I sure like this kind of hijacking. I also would like to express my gratitude to you for your great support.
Regards, Andreas
ASKER
Great job. Thank you!
Andreas,
I wrote the code to avoid the need to Activate each chart, so you can delete the following statement:
Sheets("PieCharts ").ChartObjects(i).Activat e
And as previously discussed, I don't need the variable cht once I have debugged my code. So you can also delete:
Dim cht As Chart
I missed making those changes before posting.
Brad
I wrote the code to avoid the need to Activate each chart, so you can delete the following statement:
Sheets("PieCharts ").ChartObjects(i).Activat
And as previously discussed, I don't need the variable cht once I have debugged my code. So you can also delete:
Dim cht As Chart
I missed making those changes before posting.
Brad
ASKER
Hi Brad,
ok. Thank you for bringing this to my attention.
Regards, Andreas
ok. Thank you for bringing this to my attention.
Regards, Andreas
Sheets("PieCharts ")
Sid
Code
Open in new window