Link chart axes' scale values to cells

[djzabala]

Hello Everyone. I have some sort of a problem with linking chart axe
(min and max values) to cell values in a worksheet. I am using th
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 wit
the idea of assigning to the cells (the cells that contain the scal
params) some worksheet functions, to determine the values from a set o
data. When I did this, the macro didn't work anymore. It didn't pop u
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 macr
works fine (i.e. no formulas in the cells, just numbers)

any idea on fixing this?,(without having to install Tushar Mehta'
autochart add-in).

I have attached a testfile with data and a chart, showing the macr
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=66691

 

[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]

Actually, in the example file I attached on the post, the data for th
scale values are from the same worksheet, and the cells linked to th
axes are in the same worksheet where the data is.

However, I will try your code, and see if it works.

Thanx a lot!!!

 

[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/
_______