Chart Title Length

[Matt]

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

 

[Ken]

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

 

[Jon Peltier]

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

 

[Peter T]

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

 

[Jon Peltier]

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

- Jon

 

[Peter T]

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]

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