VBA to Change Font in title

[Barb Reinhardt]

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

 

[Bernard Liengme]

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

 

[Barb Reinhardt]

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?

 

[Barb Reinhardt]

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

 

[Bernard Liengme]

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

 

[Andy Pope]

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

 

[Barb Reinhardt]

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

 

[Andy Pope]

By 'overwriting' do you mean the chart title extends down into the plotarea?