Link to home
Start Free TrialLog in
Avatar of Andreas Hermle
Andreas HermleFlag for Germany

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
Avatar of SiddharthRout
SiddharthRout
Flag of India image

Is this what you want? Please note that your sheet PieCharts has a space after it's name and hence I have introduces a space in the code as well. For example

Sheets("PieCharts ")

Sid

Code

Sub FormatCharts()
    Dim i As Long
    Dim fntSize As Double
    Dim FntName  As String
    Dim isbold As Boolean
    
    Sheets("PieCharts ").ChartObjects(1).Activate
    FntName = ActiveChart.ChartTitle.Font.Name
    fntSize = ActiveChart.ChartTitle.Font.Size
    isbold = ActiveChart.ChartTitle.Font.Bold
    
    For i = 2 To Sheets("PieCharts ").ChartObjects.Count
        Sheets("PieCharts ").ChartObjects(i).Activate
        ActiveChart.ChartTitle.Font.Name = FntName
        ActiveChart.ChartTitle.Font.Size = fntSize
        ActiveChart.ChartTitle.Font.Bold = isbold
    Next
End Sub

Open in new window

I just noticed that you wanted Italics and Underline as well. Please use this 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

Open in new window

Avatar of Andreas Hermle

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
Hmm this is strange.

I just tried this

    If ActiveChart.ChartTitle.Font.Underline = xlUnderlineStyleNone Then
        MsgBox "Not underlined"
    Else
        MsgBox "Underlined"
    End If

Open in new window


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.Font.Underline
    isUnd = ActiveChart.ChartTitle.Font.Italic


I meant it as

    isIt = ActiveChart.ChartTitle.Font.Italic
    isUnd = ActiveChart.ChartTitle.Font.Underline

Sid
I even tried this and it still gave me "underlined".

Sub Sample()
    ActiveChart.ChartTitle.Font.Underline = xlUnderlineStyleNone
    If ActiveChart.ChartTitle.Font.Underline = xlUnderlineStyleNone Then
        MsgBox "Not underlined"
    Else
        MsgBox "Underlined"
    End If
End Sub

Open in new window


Sid
Requesting other experts to join it,

Sid
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Gr8 :)

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
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
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.Font.Underline returns a Long result (1183516789) as opposed to the Integer value of xlUnderlineStyleNone (-4142)

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

Open in new window

That's why I have the repetitious code in my With block. I didn't experiment to see how much of .Format.TextFrame2.TextRange.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
In Excel 2010, I can move all but the .Font part into the With statement:
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

Open in new window


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.Font.Name

So why the extra need to use ".Format.TextFrame2.TextRange.Font.Name" has me perplexed.

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
IntelliSense doesn't present a choice of ActiveChart.ChartTitle.Font for me in Excel 2010. The Font object is reached instead through ActiveChart.ChartTitle.Format.TextFrame2.TextRange.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
Ah! I checked the intellisense of ChartTitle. It doesn't have a property called .Font, yet the line

FntName = ActiveChart.ChartTitle.Font.Name

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
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
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).Activate

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
Hi Brad,

ok. Thank you for bringing this to my attention.

Regards, Andreas