Solved

Format part of chart titles differently using VBA

Posted on 2013-01-03

Microsoft Excel

2 solutions

498 Views

Last Modified: 2013-01-03

Dear Experts:

below macro, courtesy of ssaqibh from EE, adds the terms 'New grading system' on a new line to all existing chart titles on the active worksheet.

Is it possible to format this new line 'New grading system' different from the rest of the chart title by means of VBA. This line 'New grading system' should have Verdana, 6 pt, bold.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

Sub chttitles()
    For Each shp In ActiveSheet.Shapes
        If shp.Type = 3 Then
            shp.Chart.HasTitle = True
            shp.Chart.ChartTitle.Text = shp.Chart.ChartTitle.Text & Chr(10) & "New grading system"
        End If
    Next shp
End Sub

Open in new window

Comment

Question by:AndreasHermle

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

5 Comments

LVL 31

Expert Comment

by:gowflow

ID: 387393122013-01-03

shp.Chart.ChartTitle.Font.Name = "Verdana"
shp.Chart.ChartTitle.Font.Size = 6
shp.Chart.ChartTitle.Font.Bold = True

gowflow

LVL 85

Assisted Solution

by:Rory Archibald

Rory Archibald earned 500 total points

ID: 387393522013-01-03

I think you want:

Sub chttitles()
   Dim shp                    As Shape
   Dim lText                  As Long
   For Each shp In ActiveSheet.Shapes
      With shp
         If .Type = 3 Then
            .Chart.HasTitle = True
            With .Chart.ChartTitle
               lText = Len(.Text)
               .Text = .Text & Chr(10) & "New grading system"
               With .Characters(lText + 1).Font
                  .Name = "Verdana"
                  .Bold = True
                  .Size = 6
               End With
            End With
         End If
      End With
   Next shp
End Sub

Open in new window

for just that part of the title to be formatted

Author Comment

by:AndreasHermle

ID: 387394082013-01-03

Hi rorya:

great coding! That is exactly what I was looking for. Thank you very much for it.

On second thoughts, I wonder whether the following action is also possible:
If I wanted to change the font size, name and property on the line 'New grading system' without adding this line again?

That is, assuming 'New grading system' has been added to the chart titles before and now somebody or I would like to have just this line altered in terms of font size, font name etc.

Help is much appreciated. Thank you very much in advance.

Regards, andreas

LVL 85

Accepted Solution

by:Rory Archibald

Rory Archibald earned 500 total points

ID: 387394302013-01-03

Sure:

Sub chttitles()
   Dim shp                    As Shape
   Dim lText                  As Long
   Dim strNewText As String
   
   strNewText = "New grading system"
   
   For Each shp In ActiveSheet.Shapes
      With shp
         If .Type = 3 Then
            .Chart.HasTitle = True
            With .Chart.ChartTitle
               lText = InStr(1, .Text, strNewText, vbTextCompare)
               If lText = 0 Then
                  lText = Len(.Text) + 2
                  .Text = .Text & Chr(10) & strNewText
               End If
               With .Characters(lText, Len(strNewText)).Font
                  .Name = "Verdana"
                  .Bold = True
                  .Size = 6
               End With
            End With
         End If
      End With
   Next shp
End Sub

Open in new window

Author Closing Comment

by:AndreasHermle

ID: 387396462013-01-03

Rorya:

I truly am impressed! Thank you very much for your great, professional and swift support. I really appreciate it.

Regards, Andreas

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel - Did You Know !

Article by: Shums

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…

Spreadsheet Compare 2016 does not work with 64 Bit Office 2016

Article by: Stacy

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016

Building Probability Models in Excel Part 5: Modeling an Investment Using Discrete Random Variables

Video by: Toby

The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…