Solved

Format part of chart titles differently using VBA

Posted on 2013-01-03
5
487 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

0
Comment
Question by:AndreasHermle
  • 2
  • 2
5 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 38739312
shp.Chart.ChartTitle.Font.Name = "Verdana"
shp.Chart.ChartTitle.Font.Size = 6
shp.Chart.ChartTitle.Font.Bold = True

gowflow
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 500 total points
ID: 38739352
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
0
 

Author Comment

by:AndreasHermle
ID: 38739408
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
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 38739430
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

0
 

Author Closing Comment

by:AndreasHermle
ID: 38739646
Rorya:

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

Regards, Andreas
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
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…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question