VBA to Change Font in title

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

As this is related to charts, I thought I'd post it here instead of in the
programming thread.

I want to dynamically define the title for a chart. I'd like to have
what I'm calling a Level1 title and a Level 2 title in a named range. When
I recorded a macro, this is what I got:

ActiveChart.ChartTitle.Select
Selection.Characters.Text = "Level 1 Title" & Chr(10) & "Level 2 Title"

Basically, I want to use a named range for "Level 1 Title" and another named
range for "Level 2 title".

How do I get the title to be displayed the way I want programmatically?

Thanks
 
HI Barb,
This worked for me

Sub ctitle()
mytitle = Range("Sheet1!title1") & Chr(10) & Range("Sheet1!title2")
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartTitle.Select
Selection.Text = mytitle
End Sub

I had two cells named title1 and title2 on Sheet1
cheers
 
Bernard, now I need to do something else. I want the TITLE1 part to be
formatted to 20 pt font and the TITLE2 part formatted to 12 pt font. How
would I do that?
 
This is what I have. What have I done wrong?

For Each objCht In aWS.ChartObjects
With objCht.Chart
With .ChartTitle
.AutoScaleFont = False
.Text = Range("Level_1_Title").Value & Chr(10) & _
Range("Level_2_Title").Value

len1 = Len(Range("Level_1_Title").Value)
len2 = Len(Range("Level_2_Title").Value)

Debug.Print .Text, len1, len2

Debug.Print "len1=", len1, "len2=", len2
.AutoScaleFont = False
With .Characters(Start:=1, Length:=len1).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 32
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

With .Characters(Start:=len1 + 2, Length:=len2).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With .Characters(Start:=len1 + 1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End With
End With
Next objCht
 
I recorded a macro and then 'played' with it
This seems to work

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 27/10/2006 by Bernard V Liengme
'

'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartTitle.Select
t1len = Len(Range("Sheet1!title1"))
t2len = Len(Range("Sheet1!title2"))
With Selection.Characters(Start:=1, Length:=t1len).Font
.Size = 18
End With
Selection.AutoScaleFont = False
With Selection.Characters(Start:=t1len + 1, Length:=1).Font
.Size = 4
End With
Selection.AutoScaleFont = False
With Selection.Characters(Start:=t1len + 2, Length:=t2len).Font
.Size = 14
End With
ActiveChart.ChartArea.Select
End Sub

best wishes
 
Hi Barb,

Other than setting the first part of the title to 32 instead of the
stated 20, nothing seems wrong. You code works fine for me in a test file.

What problem are you having? Error message or something not as expected?

Cheers
Andy
 
I've got it to work, but now the chart titles are overwriting the chart.
What do I need to add?
 
Back
Top