Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Aug 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA for updating Chart Axis based on cell value

    Hi I need some code to update a chart axis but cant get it to work, i'm using:



    Sub ChangeAxisScales()

    Dash2.ChartObjects("Chart 8").Activate
    With ActiveChart.Axes(xlCategory, xlPrimary)
    .MaximumScale = [Axis_Max]
    .MinimumScale = [Axis_Min]
    End With
    End Sub


    The graph is on the worksheet Dash2 and the named cells for the scale are on Sheet8. I get a runtime error 440 -Method 'MaximumScale' of object 'Axis' failed.

    Any ideas?

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 648 Times in 591 Posts
    Idarley,

    Try this:

    Code:
    Sub ChangeAxisScales()
    Worksheets("Dash2").ChartObjects("Chart 8").Activate
    With ActiveChart.Axes(xlValue)
    .MaximumScale = WorksheetFunction.Max(Worksheets("Sheet8").Range("Axis"))
    .MinimumScale = WorksheetFunction.Min(Worksheets("Sheet8").Range("Axis"))
    End With
    End Sub
    Assuming that the range of chart data on Sheet8 has been named "Axis".

    HTH,
    Maud
    Last edited by Maudibe; 2014-09-01 at 00:23.

  3. #3
    New Lounger
    Join Date
    Aug 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Maudibe, I could use the range of value but I want a bit of space between the high and low values which is why I have a formula in the named cells Axis_Min and Axis_Max. Is there any way to get it to run off those cell values?

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 648 Times in 591 Posts
    This will use your naned ranges that already have your formulas applied:

    Code:
    Sub ChangeAxisScales()
    Worksheets("Dash2").ChartObjects("Chart 8").Activate
    With ActiveChart.Axes(xlValue)
    .MaximumScale = Worksheets("Sheet8").[Axis_Max]
    .MinimumScale = Worksheets("Sheet8").[Axis_Min]
    End With
    End Sub
    Last edited by Maudibe; 2014-09-01 at 09:20.

  5. #5
    New Lounger
    Join Date
    Aug 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Works perfect. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •