Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

Solved

Format part of chart titles differently using VBA

Posted on 2013-01-03

Microsoft Excel

2 solutions

Medium Priority

510 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 2000 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 2000 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

Freeze Panes feature

Article by: Rob

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.

Microsoft Excel

Dynamic Data Vaildation

Article by: Rob

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.

Creating a Double-Sided Bar Chart in Excel

Video by: AnnieCushing

This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

Microsoft Excel

Creating Interactive Pivot Charts in Excel

Video by: AnnieCushing

This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.