CODEWITHSUNDEEP
excelvba
Andrew Pettit
Andrew Pettit

Reputation: 37

Using Cells to change max/min on existing Chart

Using cells to Change x/y Min/Max on an existing Chart

I have tried a few websites that give a generic code but none of them work for me 

Private Sub ChartDimension()
    With ActiveChart.Axes(xlCategory, xlPrimary)
    .MinimumScale = .Range("B72").Value
    .MaximumScale = .Range("B71").Value
    End With
    With ActiveChart.Axes(xlValue, xlPrimary)
    .MinimumScale = .Range("B70").Value
    .MaximumScale = .Range("B69").Value
    End With
End Sub

"Object Variable or With Block variable not set" Is the error message i am receiving

Upvotes: 0

Views: 47

Answers (2)

Shai Rado
Shai Rado

Reputation: 33672

You should try to set-up all your Worksheet and ChartObjects, that will create a shorter and clearer piece of code:

Modified Code

Option Explicit

Private Sub ChartDimension()

Dim ChtObj As ChartObject
Dim Sht As Worksheet

' set the Worksheet object
Set Sht = ThisWorkbook.Sheets("ORI -PERFO-SONIC-NOZZLES")

' set the Chart-Object
Set ChtObj = Sht.ChartObjects("Chart 1")  ' rename "Chart 1" to your chart's name
With ChtObj
    .Chart.Axes(xlValue, xlPrimary).MaximumScale = Sht.Range("B72").Value
    .Chart.Axes(xlValue, xlPrimary).MaximumScale = Sht.Range("B71").Value

    .Chart.Axes(xlValue, xlPrimary).MaximumScale = Sht.Range("B70").Value
    .Chart.Axes(xlValue, xlPrimary).MaximumScale = Sht.Range("B69").Value
End With

End Sub

Upvotes: 1

SJR
SJR

Reputation: 23081

You can reference a chart using its name or index, viz. If you are creating the chart in code too you can build that in.

Private Sub ChartDimension()

With ActiveSheet
    With .ChartObjects(1).Chart.Axes(xlCategory, xlPrimary) 'or .ChartObjects("Chart 1")
    .MinimumScale = Worksheets("Sheet1").Range("c1").Value
    .MaximumScale = Range("c2").Value
    End With
    With .ChartObjects(1).Chart.Axes(xlValue, xlPrimary)
    .MinimumScale = Range("c3").Value
    .MaximumScale = Range("c4").Value
    End With
End With

End Sub

Upvotes: 2

Related Questions