set axis min and max by macro problem

[Gavin]

I have a macro to automate this process which is not working.

I am sure it is very simple and anyone with some visual basic
knowledge should see the problem immediately.

Many thanks in advance.

The file is here:
www.menudujour.co.nz/MenuEngineering.xls

Gavin

 

[Jon Peltier]

Gavin -

I include some suggestions and code on this page:

http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html

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

 

[Don Guillett]

Here is one that is automatic. The operative lines are
.MinimumScale = bottom
.MaximumScale = Top

Private Sub Chart_Activate()
Application.ScreenUpdating = False

With Sheets("Acct Risk")
xas = Application.Min(.[arcols]) - 500
If xas < 0 Then
mc = -500
Else: mc = 500
End If

bottom = Application.Floor(xas, mc)
x = Application.Max(.[arcols])
If x < 0 Then
mct = -500
Else
mct = 500
End If

Top = Application.Ceiling(x, mct)
End With

ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = bottom
.MaximumScale = Top
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
' .MajorUnit = 500
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.Deselect
Application.ScreenUpdating = True
End Sub