VBA to Change Font in title
 

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

VBA to Change Font in title
 

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 V Liengme
www.stfx.ca/people/bliengme
remove caps from email



"Barb Reinhardt" wrote in message
...
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

VBA to Change Font in title
 

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?


"Bernard Liengme" wrote:

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 V Liengme
www.stfx.ca/people/bliengme
remove caps from email



"Barb Reinhardt" wrote in message
...
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

VBA to Change Font in title
 

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" wrote:

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 V Liengme
www.stfx.ca/people/bliengme
remove caps from email



"Barb Reinhardt" wrote in message
...
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

VBA to Change Font in title
 

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

VBA to Change Font in title
 

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 wrote:
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" wrote:


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 V Liengme
www.stfx.ca/people/bliengme
remove caps from email



"Barb Reinhardt" wrote in message
...

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






--

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

VBA to Change Font in title
 

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

"Andy Pope" wrote:

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 wrote:
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" wrote:


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 V Liengme
www.stfx.ca/people/bliengme
remove caps from email



"Barb Reinhardt" wrote in message
...

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






--

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

VBA to Change Font in title
 

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


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

"Andy Pope" wrote:


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 wrote:

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" wrote:



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 V Liengme
www.stfx.ca/people/bliengme
remove caps from email



"Barb Reinhardt" wrote in message
...


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





--

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


--

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