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