Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    117
    Thanks
    11
    Thanked 1 Time in 1 Post

    VBA to set y-axis min and max via values in worskheet cells?

    Sorry, I've tried unsuccessfully adapting code posted elsewhere for this...I would like to use VBA to be able to have three named cells determine the min, max and major unit on the y-axis of each of 3 line charts on a worksheet.

    In the simple example attached, I have data for three charts, and the charts themselves. The y-axis for each is currently set to auto-scaling.

    There are also three named cells, Axis_min, Axis_max, and Major_unit, in which the user would enter the desired values. The user would then click a button and have each of the three charts use the specified y-axis min, max and major unit.


    Before posting this I found an explanation of how to do this on John Peltier's site


    http://peltiertech.com/Excel/Charts/...nkToSheet.html


    which I have tried to adapt, without success.


    The adapted code -- which if I can ever get to work -- will be triggered by a button on the worksheet, and is as follows:


    ------------------------------------------

    Option explicit

    Sub ChangeAxisScales()

    With ActiveSheet.ChartObjects("Chart 1").Chart

    ' Value (Y) Axis

    With .Axes(xlValue)
    .MaximumScale = ActiveSheet.Range("Axis_max").Value
    .MinimumScale = ActiveSheet.Range("Axis_min").Value
    .MajorUnit = ActiveSheet.Range("Unit").Value
    End With

    End With

    End Sub
    --------------------------------------------

    When I run the macro, however, I get the error message, "Unable to get the ChartObjects property of the worksheet class".


    I've no idea what this means. I guess this is what happens when you make a good faith attempt to adapt code when you don't really understand VBA :-(


    If anyone could help me make this work I would be super grateful.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,910
    Thanks
    0
    Thanked 89 Times in 85 Posts
    Your charts are numbered from 2-4 hence the problem (no Chart1). Try this:
    Code:
    Sub ChangeAxisScales()
       Dim objCht As ChartObject
       For Each objCht In ActiveSheet.ChartObjects
          With objCht.Chart
             ' Value (Y) Axis
             With .Axes(xlValue)
                .MaximumScale = ActiveSheet.Range("Axis_max").Value
                .MinimumScale = ActiveSheet.Range("Axis_min").Value
                .MajorUnit = ActiveSheet.Range("Unit").Value
             End With
          End With
       Next objCht
    End Sub
    Regards,
    Rory
    Microsoft MVP - Excel.

  3. The Following User Says Thank You to rory For This Useful Post:

    lingyai (2011-06-22)

  4. #3
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    117
    Thanks
    11
    Thanked 1 Time in 1 Post
    Works like a charm. Awesome. Thank you very much, Rory!

  5. #4
    New Lounger
    Join Date
    Aug 2013
    Posts
    9
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi all,

    Sorry to bump linyai's thread, but it describes exactly what I've been trying to do for ages. I've replicated rory's final solution, but - for my purposes - made it a Worksheet Change event, as I'd need this macro to run without the need for activating it by pressing a button. So, I've got that to work and it's an excellent piece of code.

    Where I'm struggling though - and where the code falls over - is that, in my workbook, I'd need to take the imbedded Chart object, and move it to its own tab. Once you do that, using this code, the x/y axis variables no longer update

    I'd be most grateful if anyone could advise on how to change the code, so that the Chart tab would, for instance, know that it had to look at Sheet 1, to locate the variables. I've tried altering the code myself, but unfortunately not had much luck. I'm guessing it can't be that difficult, but alas my VBA knowledge is very rusty (last dabbled in 2004)

    I really would be grateful if someone could give me a steer on this,

    Best.

  6. #5
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,515
    Thanks
    30
    Thanked 177 Times in 172 Posts
    Hi

    Welcome to the Lounge!

    ..so, instead of:
    .MaximumScale = ActiveSheet.Range("Axis_max").Value
    .MinimumScale = ActiveSheet.Range("Axis_min").Value
    .MajorUnit = ActiveSheet.Range("Unit").Value

    ..you could try this:
    .MaximumScale = Sheets("zzz").Range("Axis_max").Value
    .MinimumScale = Sheets("zzz").Range("Axis_min").Value
    .MajorUnit = Sheets("zzz").Range("Unit").Value

    ..where you change "zzz" to your sheet name, e.g "Sheet1", "Fred" etc etc.
    OR, perhaps you could just use this form:
    .MaximumScale = [Axis_max]
    .MinimumScale = [Axis_min]
    .MajorUnit = [Unit]
    ..where the square brackets refer directly to named cells

    zeddy

  7. #6
    New Lounger
    Join Date
    Aug 2013
    Posts
    9
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi Zeddy - thank you for the welcome! I've been looking through over threads, and the coding is bringing back memories

    Thank you for the suggestion - I've tired that variation, but it doesn't seem to work either? I've amended it to:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
       Dim objCht As ChartObject
       For Each objCht In ActiveSheet.ChartObjects
          With objCht.Chart
             ' Value (Y) Axis
             With .Axes(xlValue)
                .MaximumScale = Sheets("Sheet1").Range("Axis_max").Value
                .MinimumScale = Sheets("Sheet1").Range("Axis_min").Value
                .MajorUnit = Sheets("Sheet1").Range("Unit").Value
             End With
          End With
       Next objCht
    
    End Sub
    I go back to Sheet1, amend cells E15(Axis_min) or E16(Axis_max) and, while the two charts I've left embedded on Sheet1 change accordingly, the one I've moved to create the Chart1 tab still doesn't

    Any ideas?

  8. #7
    New Lounger
    Join Date
    Aug 2013
    Posts
    9
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Attaching sample file, to illustrate what I'm trying to do/where I'm going wrong!

    y axis scaling problem for forum_v1.1.xls

  9. #8
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,280
    Thanks
    46
    Thanked 250 Times in 230 Posts
    Thedamo,

    Here is a revised version that updates the chart using the Worksheet_Activate event and calls the ChangeAxisScales subroutine in module 1.

    HTH,
    Maud
    Attached Files Attached Files

  10. The Following User Says Thank You to Maudibe For This Useful Post:

    thedamo (2013-08-12)

  11. #9
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,515
    Thanks
    30
    Thanked 177 Times in 172 Posts
    Hi

    Maudibe provides a solution to update the chart sheet "Sheet2".
    But the button on "Sheet1" now won't update the charts on "Sheet1".

    My attached version updates both.
    And uses the shorter vba coding I suggested earlier.

    zeddy
    Attached Files Attached Files

  12. The Following User Says Thank You to zeddy For This Useful Post:

    sosinovitch (2014-09-27)

  13. #10
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,280
    Thanks
    46
    Thanked 250 Times in 230 Posts
    Thedamo,

    In your file, there were 3 instances of the same routine with the same name Sub ChangeAxisScales. In the file that I posted, I purposely removed 2 of them to demonstrate the Worksheet_Activate event

  14. #11
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,515
    Thanks
    30
    Thanked 177 Times in 172 Posts
    Hi Maudibe

    ..you were correct to purposely remove 2 of them from the sheet codemodules.

    ..but in [Module1], you changed
    For Each objCht In ActiveSheet.ChartObjects
    to..
    For Each objCht In Worksheets("Sheet2").ChartObjects
    ..whereas if you left it as is, the button would still work

    zeddy

  15. #12
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,280
    Thanks
    46
    Thanked 250 Times in 230 Posts
    As Thedamo states
    ...in my workbook, I'd need to take the imbedded Chart object, and move it to its own tab. Once you do that, using this code, the x/y axis variables no longer update

  16. #13
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,515
    Thanks
    30
    Thanked 177 Times in 172 Posts
    ..whereas if you left it as is, the button would still work
    ..and the Chart on Sheet2 would update as well

    zeddy

  17. #14
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,280
    Thanks
    46
    Thanked 250 Times in 230 Posts
    ...whereas,
    Once you do that, using this code, the x/y axis variables no longer update....
    on the chart that was just moved. Unless I am reading it wrong, the OP wants to move the chart to another workbook and not use the chart on the original sheet.
    Last edited by Maudibe; 2013-08-11 at 11:41.

  18. #15
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,515
    Thanks
    30
    Thanked 177 Times in 172 Posts
    Hi Maudibe

    ..maybe I'm reading it wrong.
    ..the chart object was already moved to its own tab (no mention of another workbook).
    I go back to Sheet1, amend cells E15(Axis_min) or E16(Axis_max) and, while the two charts I've left embedded on Sheet1 change accordingly, the one I've moved to create the Chart1 tab still doesn't
    In your file, when you change the values on [Sheet1] and then switch to [Sheet2], the Chart updates automatically, which is what thedamo wanted. Top marks for fixing that. I was just pointing out that clicking the button on [Sheet1] no longer updated the two charts on [Sheet1].

    zeddy

Page 1 of 2 12 LastLast

Posting Permissions

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