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

 

[Iridium]

Try something like adjusting your references to suit

With ActiveChart.Axes(xlCategory)
        .MinimumScale = ActiveSheet.Cells(1, 1)
        .MaximumScale = ActiveSheet.Cells(1, 2)
End With

HTH

 

[tusharm]

For someone who's using VBA for a chart for the first time, you've done a great job decoding the object model! Of course, it looks like you used the macro recorder, which is the fastest way to get the right syntax!

What kind of chart do you have the problem with? Not all chart axis have a min./max. scale property.

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
{snip}

 

[tusharm]

Umm...OK, I'm kinda lost. How does your suggestion help the OP?

Try something like adjusting your references to suit

With ActiveChart.Axes(xlCategory)
        .MinimumScale = ActiveSheet.Cells(1, 1)
        .MaximumScale = ActiveSheet.Cells(1, 2)
End With

HTH

 

[Iridium]

Tushar - I may have misunderstood the OP's request but I thought they were after a way of linking the chart axes to values on the worksheet but I bow to your greater knowledge ray: Might not be thinking straight - been a long day and its nearly midnight here - perhaps I should leave my help til after a good nights sleep!

 

[tusharm]

What greater knowledge? I basically asked him for more diagnostic information And, from what I can tell, you correctly understood his intent.

Tushar - I may have misunderstood the OP's request but I thought they were after a way of linking the chart axes to values on the worksheet but I bow to your greater knowledge ray: Might not be thinking straight - been a long day and its nearly midnight here - perhaps I should leave my help til after a good nights sleep!

 

[Iridium]

What greater knowledge? I basically asked him for more diagnostic information And, from what I can tell, you correctly understood his intent.

Tushar - I may have misunderstood the OP's request but I thought they were after a way of linking the chart axes to values on the worksheet but I bow to your greater knowledge ray: Might not be thinking straight - been a long day and its nearly midnight here - perhaps I should leave my help til after a good nights sleep!

Sorry to davers5 as we've strayed off thread here. Tushar - I only class myself as an intermediate user of xl so when a respected MVP like yourself wades in with an answer I tend not to be so confident in my reply as you queried how it would help the OP. That's what I meant by greater knowledgge

Kind Regards

Ir192

 

[davers5]

I'm not sure what you are asking when you ask what kind of chart. It is a bar graph embedded on a worksheet, pretty simple and the axis does have a min/max property because I changed them (and recorded that action with the macro recorder which is where the meat of the with block came from).

Dave

For someone who's using VBA for a chart for the first time, you've done a great job decoding the object model! Of course, it looks like you used the macro recorder, which is the fastest way to get the right syntax!

What kind of chart do you have the problem with? Not all chart axis have a min./max. scale property.

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
{snip}

 

[tusharm]

This is possibly a source for misunderstanding. My post was meant as self-deprecating humor. Might not have worked

Sorry to davers5 as we've strayed off thread here. Tushar - I only class myself as an intermediate user of xl so when a respected MVP like yourself wades in with an answer I tend not to be so confident in my reply as you queried how it would help the OP. That's what I meant by greater knowledgge

Kind Regards

Ir192

 

[tusharm]

Nothing in the code jumps out. What are the min/max values before you run the code? And, which chart are you changing and what are the new min/max values? Yeah, yeah, I can see the new min value is supposed to be zero. You might also want to insert a breakpoint at the line where you are setting the min. value and check the before values and the content of the variable sngMinScale.

I'm not sure what you are asking when you ask what kind of chart. It is a bar graph embedded on a worksheet, pretty simple and the axis does have a min/max property because I changed them (and recorded that action with the macro recorder which is where the meat of the with block came from).

Dave