Automatically set chart axis labels from cell contents
 

I have lots of scatter charts plotting data in columns. At the top of each
column is a cell containing the label I want to appear on the axis. I would
like the axis to be re-labeled whenever the label cells change automatically.
I am currently doing this with a little macro:

Sub setlabel()
Dim x As String
x = Cells(1, 1)
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
With ActiveChart
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = x
End With
End Sub

This is not automatic. Is there a way to perform this automatically without
resorting to VBA trickery??
--
Gary's Student

Just to clarify, this is the axis title. The (tick) labels occur at each
major tick along the axis.

You can link the text of an axis title to a particular cell. Select the
axis title, press the equals key, and select the cell.

This also works with the chart title, individual data labels, and text
boxes.

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


Gary's Student wrote:

I have lots of scatter charts plotting data in columns. At the top of each
column is a cell containing the label I want to appear on the axis. I would
like the axis to be re-labeled whenever the label cells change automatically.
I am currently doing this with a little macro:

Sub setlabel()
Dim x As String
x = Cells(1, 1)
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
With ActiveChart
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = x
End With
End Sub

This is not automatic. Is there a way to perform this automatically without
resorting to VBA trickery??

Your suggestion works perfectly. Thank you very much
--
Gary's Student


"Jon Peltier" wrote:

Just to clarify, this is the axis title. The (tick) labels occur at each
major tick along the axis.

You can link the text of an axis title to a particular cell. Select the
axis title, press the equals key, and select the cell.

This also works with the chart title, individual data labels, and text
boxes.

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


Gary's Student wrote:

I have lots of scatter charts plotting data in columns. At the top of each
column is a cell containing the label I want to appear on the axis. I would
like the axis to be re-labeled whenever the label cells change automatically.
I am currently doing this with a little macro:

Sub setlabel()
Dim x As String
x = Cells(1, 1)
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
With ActiveChart
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = x
End With
End Sub

This is not automatic. Is there a way to perform this automatically without
resorting to VBA trickery??