Thread: 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??
>