Status: Solved
Priority: Medium
Security: Public
Views: 635
Last Modified: 2013-01-03

Format part of chart titles differently using VBA

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

Andreas Hermle

Asked: 2013-01-03

Advertise Here

Microsoft Excel

2 Solutions

LVL 31

gowflowCommented: 2013-01-03

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

gowflow

LVL 85

Rory ArchibaldCommented: 2013-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

Andreas HermleTeam leaderAuthor Commented: 2013-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

Rory ArchibaldCommented: 2013-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

Andreas HermleTeam leaderAuthor Commented: 2013-01-03

Rorya:

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

Regards, Andreas

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Format part of chart titles differently using VBA

Join & Write a Comment Already a member? Login.

Featured Post