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" <MattSonn...@gmail.com> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <MattSonn...@gmail.com> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <MattSonn...@gmail.com> 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
news:ez%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <MattSonn...@gmail.com> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> news:ez%(E-Mail Removed)...
> >> 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> 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" <MattSonn...@gmail.com> 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
news:(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>> news:ez%(E-Mail Removed)...
>> >> 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" <(E-Mail Removed)> wrote in message
>> > news:(E-Mail Removed)...
>> >> 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" <MattSonn...@gmail.com> 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
>> >>
>> >>
>> >
>> >
>>
>>
>
>