Chart Title Length

  • Thread starter Thread starter Matt
  • Start date Start date
M

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
 
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
 
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
 
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.
Click to expand...

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
 
I tested my technique with an arbitrary string that was around 350
characters long.

- Jon
 
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
 
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
 
Back
Top