change chart scale in vba

[davers5]

I'm trying to change the value scale of a chart using VBA but I get the error "unable to set MinimumScale property of axis class". This is my first time manipulating a chart in VBA and I'm a little confused about the object classes. My charts are embedded in a worksheet and the code should scroll through each chart and change it's maximum value to the value of a cell on the worksheet. Everything works great until I get to actually assigning the values. Any advice? My code is below.

Thanks,

Dave

Private Sub ReScaleCharts()
    Dim c As Object
    Dim wksYTD As Worksheet
    Dim sngMaxScale As Single
    Dim sngMinScale As Single
        
    'assign worksheet to variable
    Set wksYTD = Worksheets("Rev_Exp")
    
    'loop through charts
    For Each c In wksYTD.ChartObjects
        Select Case c.Name
            Case "Rev_YTD"
                sngMaxScale = Range("revmaxscale")
                sngMinScale = 0
            Case "Exp_YTD"
                sngMaxScale = Range("expmaxscale")
                sngMinScale = 0
            Case "EBITDA_YTD"
                sngMaxScale = Range("ebitmaxscale")
                sngMinScale = 0
            Case Else
                MsgBox c.Parent.Name
        End Select
    
    'assign min and max scale values
        With c.Chart.Axes(xlValue)
            .MinimumScale = sngMinScale
            .MaximumScale = sngMaxScale
            .MinorUnitIsAuto = True
            .MajorUnitIsAuto = True
            .Crosses = xlAutomatic
            .ReversePlotOrder = False
            .ScaleType = xlLinear
            .DisplayUnit = xlNone
        End With
    Next c
End Sub

 

[mjbeam]

Is it possible that you are trying to set the max scale lower than the min scale?


-Mike

 

[Jon Peltier]

What's the error message, and what line is it on? What's in the relevant cells?

A few comments:
Dim c As ChartObject
Dim your scale values as Double
Reference your range appropriately:
_ MyValue = wksYTD.Range("whatever").Value
Make sure you don't apply a 0 to a log scale.
Maybe you need sthg like:
_ MyMax = WorksheetFunction.Max(0, wksYTD.Range("whatever").Value)
_ MyMin = WorksheetFunction.Min(0, wksYTD.Range("whatever").Value)


- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______