Solved

Format part of chart titles differently using VBA

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now