VBA - Change font size during string

Posted on 2012-09-09
Medium Priority
Last Modified: 2012-09-24
Hi Guys,

I have a chart title which I neet to change the font size for the 2nd line in the title so the first line appears in a large font and the 2nd line which will be a date appears in a smaller font. I have the code below which puts the correct text on both lines but I'm unsure how to change the font size for the 2nd line. Any ideas would be very appreaciated

ActiveChart.ChartTitle.Text = "Quality Gates - September" & Chr(10) & "From: " & Format(now(),"dd/mm/yyy")


Question by:victoriaharry
  • 2
LVL 50

Expert Comment

by:Martin Liss
ID: 38381454
You can't have more than one font size, color etc in a textbox. If Excel has a richtextbox you can use that but you should probably consider using two textboxes and splitting the text between the two.

Author Comment

ID: 38381471
Thanks for the reply. I'm al little confused though. If I create a chart manually with two lines of text in the title I can highlight the 2nd line of text and change the font size to whatever I want so that the size differs from the first line. I was hoping there was a way to replicate this action using VBA
LVL 50

Expert Comment

by:Martin Liss
ID: 38381473
Try creating and then editing a macro while you are achieving the effect that you want.
LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 38381479
This works in Excel 2010.  Haven't tested it in other versions:

Dim TitleText As String
Dim BreakPoint As Long

TitleText = "Quality Gates - September" & Chr(10) & "From: " & _
    Format(Now, "dd/mm/yyyy")
BreakPoint = InStr(1, TitleText, Char(10))

With ActiveChart.ChartTitle
    .Text = "Quality Gates - September" & Chr(10) & "From: " & _
    .Characters(1, BreakPoint - 1).Font.Size = 18
    .Characters(TitleText + 1, Len(TitleText)).Font.Size = 10
End With

Open in new window


Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Introduction to Processes

830 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