Hello,
I have a chart named "VR" located on it's own tab, where a user can go and,
via drop down box, select data range A or data range B to view on the graph.
I've used this for a long time successfully, but now I have a need where the
data scale is disparate between the two, and I need to set specific ranges
for the Y axis based on the selection of view A or view B.

the data is on a separate sheet, and I have formulas in place to return the
correct Y scale range based on the selection of A or B, and have named the
output of those Y1min for the Y axis minimum value, Y1Max for the y axis
maximum value, and Munit for the major unit value.

I've attempted to write the macro below to adjust the chart ranges on the VR
chart tab. However it faults out and states that it is "unable to set the
major unit property of the axis class".

Could anyone provide any guidance on either fixing this or providing an
alternative solution? Using the automatic selection will not work for
various reasons with my graph design.

Thanks for the help,
bruce

Private Sub Worksheet_Calculate()
Sheets("VR").Select
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = Y1min
.MaximumScale = Y1max
.MinorUnitIsAuto = True
.MajorUnit = Munit
.Crosses = xlCustom
.CrossesAt = 0
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End Sub

The variables Y1min, Y1max and Munit are not populated by the routine and is
thus empty. You need to fill these with you formula results first like this
(change to reference you location of course).

Sheets("VR").Select
Y1min=Sheets("Sheet1").Range("A1")
Y1max=Sheets("Sheet1").Range("A2")
Munit=Sheets("Sheet1").Range("A3")
ActiveChart.Axes(xlValue).Select

--
If this helps, please remember to click yes.


"brumanchu" wrote:

Hello,
I have a chart named "VR" located on it's own tab, where a user can go and,
via drop down box, select data range A or data range B to view on the graph.
I've used this for a long time successfully, but now I have a need where the
data scale is disparate between the two, and I need to set specific ranges
for the Y axis based on the selection of view A or view B.

the data is on a separate sheet, and I have formulas in place to return the
correct Y scale range based on the selection of A or B, and have named the
output of those Y1min for the Y axis minimum value, Y1Max for the y axis
maximum value, and Munit for the major unit value.

I've attempted to write the macro below to adjust the chart ranges on the VR
chart tab. However it faults out and states that it is "unable to set the
major unit property of the axis class".

Could anyone provide any guidance on either fixing this or providing an
alternative solution? Using the automatic selection will not work for
various reasons with my graph design.

Thanks for the help,
bruce

Private Sub Worksheet_Calculate()
Sheets("VR").Select
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = Y1min
.MaximumScale = Y1max
.MinorUnitIsAuto = True
.MajorUnit = Munit
.Crosses = xlCustom
.CrossesAt = 0
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End Sub