VBA to Change Font in title

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Barb Reinhardt - 27 Oct 2006 18:01 GMT

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

Reply to this Message

Bernard Liengme - 27 Oct 2006 18:57 GMT

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

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> As this is related to charts, I thought I'd post it here instead of in the
> programming thread.
[quoted text clipped - 14 lines]
>
> Thanks

Reply to this Message

Barb Reinhardt - 27 Oct 2006 19:06 GMT

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?

> HI Barb,
> This worked for me
[quoted text clipped - 26 lines]
> >
> > Thanks

Reply to this Message

Barb Reinhardt - 27 Oct 2006 19:11 GMT

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

> HI Barb,
> This worked for me
[quoted text clipped - 26 lines]
> >
> > Thanks

Reply to this Message

Bernard Liengme - 27 Oct 2006 19:24 GMT

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

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

Reply to this Message

Andy Pope - 27 Oct 2006 19:27 GMT

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

> This is what I have. What have I done wrong?
>
[quoted text clipped - 84 lines]
>>>
>>>Thanks

Signature

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Reply to this Message

Barb Reinhardt - 27 Oct 2006 20:16 GMT

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

> Hi Barb,
>
[quoted text clipped - 94 lines]
> >>>
> >>>Thanks

Reply to this Message

Andy Pope - 27 Oct 2006 20:25 GMT

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

> I've got it to work, but now the chart titles are overwriting the chart.
> What do I need to add?
[quoted text clipped - 97 lines]
>>>>>
>>>>>Thanks

Signature

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Reply to this Message