• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 516
  • Last Modified:

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
0
AndreasHermle
Asked:
AndreasHermle
  • 12
  • 6
  • 5
1 Solution
 
SiddharthRoutCommented:
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

0
 
SiddharthRoutCommented:
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

0
 
AndreasHermleAuthor Commented:
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SiddharthRoutCommented:
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
0
 
SiddharthRoutCommented:
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
0
 
SiddharthRoutCommented:
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
0
 
SiddharthRoutCommented:
Requesting other experts to join it,

Sid
0
 
byundtCommented:
The following works in Excel 2010:
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
        FntName = .Format.TextFrame2.TextRange.Font.Name
        fntSize = .Format.TextFrame2.TextRange.Font.Size
        isbold = .Format.TextFrame2.TextRange.Font.Bold
        isUnd = .Format.TextFrame2.TextRange.Font.UnderlineStyle
        isIt = .Format.TextFrame2.TextRange.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
            .Format.TextFrame2.TextRange.Font.Size = fntSize
            .Format.TextFrame2.TextRange.Font.Bold = isbold
            .Format.TextFrame2.TextRange.Font.UnderlineStyle = isUnd
            .Format.TextFrame2.TextRange.Font.Italic = isIt
        End With
    Next
End Sub

Open in new window

0
 
SiddharthRoutCommented:
Gr8 :)

It works in 2007 as well.

Sid
0
 
SiddharthRoutCommented:
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
0
 
AndreasHermleAuthor Commented:
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
0
 
byundtCommented:
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
0
 
byundtCommented:
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
0
 
SiddharthRoutCommented:
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
0
 
SiddharthRoutCommented:
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
0
 
byundtCommented:
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
0
 
SiddharthRoutCommented:
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
0
 
byundtCommented:
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.
0
 
SiddharthRoutCommented:
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
0
 
AndreasHermleAuthor Commented:
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
0
 
AndreasHermleAuthor Commented:
Great job. Thank you!
0
 
byundtCommented:
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
0
 
AndreasHermleAuthor Commented:
Hi Brad,

ok. Thank you for bringing this to my attention.

Regards, Andreas
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 12
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now