Hi Peter,

This will link the axistitle to the cell B1.
.Axes(xlCategory, xlPrimary).AxisTitle.Text = "=" &
Range("B1").Parent.Name & "!" & Range("B1").Address(ReferenceStyle:=xlR1C1)

Any changes in B1 will automatically be reflected in the chart.
Note that you can not append text, i.e. (lbs), when the text is linked.
Therefore this must be done within the cell B1.


peter wrote:
i'm sorry, i should have clarified that B1 has contents
that can change, thus the need for having B1 read into the
title. for example B1 might say Drive, or it might say
Force, etc....



-----Original Message-----
can anyone tell me how I can do this? cause i'm doing it
wrong....

this line of code:

..Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
Range("B1").Value & " (lbs)"

the cell contents of B1 just has the following: Drive

maybe it's not even possible to do something like this?

i

thought i'd run it by people more familiar with vba.

Thanks much in advance!
.


--

Cheers
Andy

http://www.andypope.info

Peter -

What's in cell B1?

Also, I'll suggest a slight modification to Andy's code, to put single
quotes around the sheet name, in case it contains spaces or other
error-producing characters:

..Axes(xlCategory, xlPrimary).AxisTitle.Text = "='" & _
Range("B1").Parent.Name & "'!" & _
Range("B1").Address(ReferenceStyle:=xlR1C1)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

peter wrote:
i must be doing something wrong.

copying that code exactly, it produces "Axis Title"

i'm probably missing something silly. thanks for your
help. hopefully this'll lead me on the right path

hi, B1 can store basically two values: Drive or Force

its value is copied from another workbook earlier in the
macro. that may be where my problem is.


ActiveSheet.Range("A1:B86").Value = Workbooks
("trending.xls").Worksheets("Analyze").Range
("A4:B89").Value


if i use your code, but use .AxisTitle.Characters.Text
instead, i get "='Analyze'!R1C2"

so i believe it can't use that cell then since it's a copy
from another workbook?

geez, i really suck at this stuff. this is driving me
nuts.

Andy -

That was what I was thinking, too, what's in B1? Like B1 says Axis
Title, and B2 has the real axis title.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Andy Pope wrote:
Hi Peter,

The code works for me.
If it doesn't generate an error then the only thing I can think is that
B1 contains "Axis Title" :)

peter wrote:

i must be doing something wrong.

copying that code exactly, it produces "Axis Title"

i'm probably missing something silly. thanks for your help.
hopefully this'll lead me on the right path