Re: Link chart axes' scale values to cells

[Debra Dalgleish]

If the values are being changed on a different worksheet, you could use
the Worksheet_Activate code to change the axis scale parameters. For
example:

'=========================
Private Sub Worksheet_Activate()
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _
.MaximumScale = Range("$E$2").Value
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _
.MinimumScale = Range("$E$3").Value
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _
.MajorUnit = Range("$E$4").Value
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _
.MaximumScale = Range("$F$2").Value
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue). _
MinimumScale = Range("$F$3").Value
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _
.MajorUnit = WorksheetFunction.Max(Range("$F$4").Value, 1)
End Sub
'=========================

djzabala < wrote:
> Hello Everyone. I have some sort of a problem with linking chart axes
> (min and max values) to cell values in a worksheet. I am using the
> macro that I found in Mr Peltier' site:
>
> http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html
>
> It worked just fine for a simple test I was doing.. BUT, I came up with
> the idea of assigning to the cells (the cells that contain the scale
> params) some worksheet functions, to determine the values from a set of
> data. When I did this, the macro didn't work anymore. It didn't pop up
> any errors or such things, it just didn't adjust the chart anymore.
>
> I believe the macro doesn't take the "displayed" value of the cells,
> because, when I input the values manually into the cells, the macro
> works fine (i.e. no formulas in the cells, just numbers)
>
> any idea on fixing this?,(without having to install Tushar Mehta's
> autochart add-in).
>
> I have attached a testfile with data and a chart, showing the macro
> doesn't fix the X axis maximum value.
>
> Thanx in advance :cool:
>
> Attachment filename: minmaxaxes.xls
> Download attachment: http://www.excelforum.com/attachment.php?postid=666914
> ---
> Message posted from http://www.ExcelForum.com/
>

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[Jon Peltier]

A quick peek at the help files for the Worksheet_Change event tells me this:

"This event doesn't occur when cells change during a recalculation. Use
the Calculate event to trap a sheet recalculation."

So updating your formula doesn't count as a change the way typing
something different in the cell does. You need to adjust the code as
follows:

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _

.MaximumScale = ActiveSheet.Range(E2).Value

ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _

.MinimumScale = ActiveSheet.Range(E3).Value

ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _

.MajorUnit = ActiveSheet.Range(E4).Value

ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _

.MaximumScale = ActiveSheet.Range(F2).Value

ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue). _

MinimumScale = ActiveSheet.Range(F3).Value

ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _

.MajorUnit = ActiveSheet.Range(F4).Value
End Sub

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