Using VBA, I'm making a graph title dynamic. I am having trouble with
the length of the characters. Within the code for the Chart Title, is
there a maximum number of characters that you can have in a title
cart? Also, how do I start a new line and continue the title? What I
have so far is putting the Customer Name, Address, City, State Zip all
on one line. What I would like is for example,

Customer Name
Address
City, State Zip


Thanks in advance,
Matt

Matt

This should help you get started. Select an embedded Chart to which
you want to add a title, and run this code. It will put the contents
of A1 on the first line, A2 on the second line and A3 on the third
line.

Sub Macro3()

t = Range("a1").Value & Chr(10) & Range("a2").Value & Chr(10) &
Range("a3").Value

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = t
End With

End Sub

I get an error when the total length of the three cells is over 253.
I suppose it counts the chr(10)'s as characters, but, I thought you
could go up to 256 total.

Good luck.

Ken
Norfolk, Va



On Jan 30, 4:13 pm, "Matt" wrote:
Using VBA, I'm making a graph title dynamic. I am having trouble with
the length of the characters. Within the code for the Chart Title, is
there a maximum number of characters that you can have in a title
cart? Also, how do I start a new line and continue the title? What I
have so far is putting the Customer Name, Address, City, State Zip all
on one line. What I would like is for example,

Customer Name
Address
City, State Zip

Thanks in advance,
Matt

An alternative is to put a formula into another cell that builds this string
in the cell

=A1&CHAR(10)&A2&CHAR(10)&A3

Then use this line to link the chart title to the cell with this formula (in
this example it's cell E3, and it must be in R1C1 notation):

activechart.ChartTitle.Text = "=Sheet1!R3C5"

If you don't want it linked, follow with this:

activechart.ChartTitle.Text = activechart.ChartTitle.Text

You can't directly use

activechart.ChartTitle.Text = t

if t is longer than some limited string length, but the two step process
above works just fine.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Ken" wrote in message
oups.com...
Matt

This should help you get started. Select an embedded Chart to which
you want to add a title, and run this code. It will put the contents
of A1 on the first line, A2 on the second line and A3 on the third
line.

Sub Macro3()

t = Range("a1").Value & Chr(10) & Range("a2").Value & Chr(10) &
Range("a3").Value

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = t
End With

End Sub

I get an error when the total length of the three cells is over 253.
I suppose it counts the chr(10)'s as characters, but, I thought you
could go up to 256 total.

Good luck.

Ken
Norfolk, Va



On Jan 30, 4:13 pm, "Matt" wrote:
Using VBA, I'm making a graph title dynamic. I am having trouble with
the length of the characters. Within the code for the Chart Title, is
there a maximum number of characters that you can have in a title
cart? Also, how do I start a new line and continue the title? What I
have so far is putting the Customer Name, Address, City, State Zip all
on one line. What I would like is for example,

Customer Name
Address
City, State Zip

Thanks in advance,
Matt

I get an error when the total length of the three cells is over 253.
I suppose it counts the chr(10)'s as characters, but, I thought you
could go up to 256 total.

I've always found the limit of characters in a chart title is limited to
255, no way to increase with any method or combination of methods manually
or with vba AFAIK. Only alternative would be a textbox.

I suppose unlikely name + address would be more than 255.

Regards,
Peter T

"Ken" wrote in message
oups.com...
Matt

This should help you get started. Select an embedded Chart to which
you want to add a title, and run this code. It will put the contents
of A1 on the first line, A2 on the second line and A3 on the third
line.

Sub Macro3()

t = Range("a1").Value & Chr(10) & Range("a2").Value & Chr(10) &
Range("a3").Value

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = t
End With

End Sub

I get an error when the total length of the three cells is over 253.
I suppose it counts the chr(10)'s as characters, but, I thought you
could go up to 256 total.

Good luck.

Ken
Norfolk, Va



On Jan 30, 4:13 pm, "Matt" wrote:
Using VBA, I'm making a graph title dynamic. I am having trouble with
the length of the characters. Within the code for the Chart Title, is
there a maximum number of characters that you can have in a title
cart? Also, how do I start a new line and continue the title? What I
have so far is putting the Customer Name, Address, City, State Zip all
on one line. What I would like is for example,

Customer Name
Address
City, State Zip

Thanks in advance,
Matt

I tested my technique with an arbitrary string that was around 350
characters long.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Peter T" <peter_t@discussions wrote in message
...
I get an error when the total length of the three cells is over 253.
I suppose it counts the chr(10)'s as characters, but, I thought you
could go up to 256 total.

I've always found the limit of characters in a chart title is limited to
255, no way to increase with any method or combination of methods manually
or with vba AFAIK. Only alternative would be a textbox.

I suppose unlikely name + address would be more than 255.

Regards,
Peter T

"Ken" wrote in message
oups.com...
Matt

This should help you get started. Select an embedded Chart to which
you want to add a title, and run this code. It will put the contents
of A1 on the first line, A2 on the second line and A3 on the third
line.

Sub Macro3()

t = Range("a1").Value & Chr(10) & Range("a2").Value & Chr(10) &
Range("a3").Value

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = t
End With

End Sub

I get an error when the total length of the three cells is over 253.
I suppose it counts the chr(10)'s as characters, but, I thought you
could go up to 256 total.

Good luck.

Ken
Norfolk, Va



On Jan 30, 4:13 pm, "Matt" wrote:
Using VBA, I'm making a graph title dynamic. I am having trouble with
the length of the characters. Within the code for the Chart Title, is
there a maximum number of characters that you can have in a title
cart? Also, how do I start a new line and continue the title? What I
have so far is putting the Customer Name, Address, City, State Zip all
on one line. What I would like is for example,

Customer Name
Address
City, State Zip

Thanks in advance,
Matt

I didn't see your earlier post when I posted mine.

I tried to replicate your technique exactly but I'm afraid it didn't work
for me. In case I've missed something this is what I did -

Sub Test()
Range("A1").Formula = "=REPT(""A"",100)"
Range("A2").Formula = "=REPT(""B"",100)"
Range("A3").Formula = "=REPT(""C"",100)"
Range("E3").Formula = "=A1&CHAR(10)&A2&CHAR(10)&A3"

With ActiveSheet.ChartObjects(1).Chart
.HasTitle = True
.ChartTitle.Text = "=Sheet1!R3C5"
.ChartTitle.Text = .ChartTitle.Text ' this won't increase length

Debug.Print Len(Range("E3")), Len(.ChartTitle.Text) ' 302 & 255
End With

End Sub

Stuck on 255, can't even manually paste more than that into a title.

Regards,
Peter T


"Jon Peltier" wrote in message
...
I tested my technique with an arbitrary string that was around 350
characters long.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Peter T" <peter_t@discussions wrote in message
...
I get an error when the total length of the three cells is over 253.
I suppose it counts the chr(10)'s as characters, but, I thought you
could go up to 256 total.

I've always found the limit of characters in a chart title is limited to
255, no way to increase with any method or combination of methods
manually
or with vba AFAIK. Only alternative would be a textbox.

I suppose unlikely name + address would be more than 255.

Regards,
Peter T

"Ken" wrote in message
oups.com...
Matt

This should help you get started. Select an embedded Chart to which
you want to add a title, and run this code. It will put the contents
of A1 on the first line, A2 on the second line and A3 on the third
line.

Sub Macro3()

t = Range("a1").Value & Chr(10) & Range("a2").Value & Chr(10) &
Range("a3").Value

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = t
End With

End Sub

I get an error when the total length of the three cells is over 253.
I suppose it counts the chr(10)'s as characters, but, I thought you
could go up to 256 total.

Good luck.

Ken
Norfolk, Va



On Jan 30, 4:13 pm, "Matt" wrote:
Using VBA, I'm making a graph title dynamic. I am having trouble with
the length of the characters. Within the code for the Chart Title,
is
there a maximum number of characters that you can have in a title
cart? Also, how do I start a new line and continue the title? What
I
have so far is putting the Customer Name, Address, City, State Zip
all
on one line. What I would like is for example,

Customer Name
Address
City, State Zip

Thanks in advance,
Matt

I stand corrected. When I did my test, I was using gibberish text, like
"adsaf;atowaagnl;agio" and must have only thought the entire blob was
inserted into the chart title.

I agree with your earlier observation, of course, that most self-respecting
titles will not be anywhere near this length.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Peter T" <peter_t@discussions wrote in message
...
I didn't see your earlier post when I posted mine.

I tried to replicate your technique exactly but I'm afraid it didn't work
for me. In case I've missed something this is what I did -

Sub Test()
Range("A1").Formula = "=REPT(""A"",100)"
Range("A2").Formula = "=REPT(""B"",100)"
Range("A3").Formula = "=REPT(""C"",100)"
Range("E3").Formula = "=A1&CHAR(10)&A2&CHAR(10)&A3"

With ActiveSheet.ChartObjects(1).Chart
.HasTitle = True
.ChartTitle.Text = "=Sheet1!R3C5"
.ChartTitle.Text = .ChartTitle.Text ' this won't increase length

Debug.Print Len(Range("E3")), Len(.ChartTitle.Text) ' 302 & 255
End With

End Sub

Stuck on 255, can't even manually paste more than that into a title.

Regards,
Peter T


"Jon Peltier" wrote in message
...
I tested my technique with an arbitrary string that was around 350
characters long.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Peter T" <peter_t@discussions wrote in message
...
I get an error when the total length of the three cells is over 253.
I suppose it counts the chr(10)'s as characters, but, I thought you
could go up to 256 total.

I've always found the limit of characters in a chart title is limited
to
255, no way to increase with any method or combination of methods
manually
or with vba AFAIK. Only alternative would be a textbox.

I suppose unlikely name + address would be more than 255.

Regards,
Peter T

"Ken" wrote in message
oups.com...
Matt

This should help you get started. Select an embedded Chart to which
you want to add a title, and run this code. It will put the contents
of A1 on the first line, A2 on the second line and A3 on the third
line.

Sub Macro3()

t = Range("a1").Value & Chr(10) & Range("a2").Value & Chr(10) &
Range("a3").Value

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = t
End With

End Sub

I get an error when the total length of the three cells is over 253.
I suppose it counts the chr(10)'s as characters, but, I thought you
could go up to 256 total.

Good luck.

Ken
Norfolk, Va



On Jan 30, 4:13 pm, "Matt" wrote:
Using VBA, I'm making a graph title dynamic. I am having trouble
with
the length of the characters. Within the code for the Chart Title,
is
there a maximum number of characters that you can have in a title
cart? Also, how do I start a new line and continue the title? What
I
have so far is putting the Customer Name, Address, City, State Zip
all
on one line. What I would like is for example,

Customer Name
Address
City, State Zip

Thanks in advance,
Matt