Results 1 to 14 of 14
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Synchronize Charts (Excel 2003)

    Hi,

    I have 3 embedded charts on a sheet. I would like the Y-Axis values of the 2nd two charts to sync with the Y-Axis values of the first chart. If the maximum Y-axis value of chart 1 is 5000, then the other two charts Y-axis values must also be 5000...etc. When the values of chart one changes, and affects the y-axis (which is automatic) then the other axis values must sync with chart one. This must be dynamic as values change on the sheet.

    Does anyone have a macro that I can use or edit that can do this??

    Tx
    Regards,
    Rudi

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Synchronize Charts (Excel 2003)

    A non-macro solution would be to:
    have all the charts "Auto" in Y-axis
    add all the yranges to all the charts
    "Hide" (no line, no color) the Y-ranges that you do not want displayed.

    Since all the charts would have the same ranges and are automatic, they should stay synced without doing any coding...

    Steve

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Synchronize Charts (Excel 2003)

    Hmmn...I will attempt this! I hope I understand >>> add all the yranges to all the charts. Must all the ranges be specified in the Y-axis textbox of chart properties??
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Synchronize Charts (Excel 2003)

    No, Steve means that all charts should be based on all data series.

    Let's say chart 1 has series A and series B, and chart 2 has series C and series D.
    Use Chart | Add Data to add series C and series D to chart 1, then hide the added series by setting line color and marker color to none.
    Delete the extra legend entries.
    Use Chart | Add Data to add series A and series B to chart 3, then hide the added series by setting line color and marker color to none.
    Delete the extra legend entries.
    This way, both charts will be based on the same data, so the value axes will automatically have the same scale.

    See attached example. The two charts on the right have invisible extra series.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Synchronize Charts (Excel 2003)

    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>Exactly what I meant <img src=/S/yep.gif border=0 alt=yep width=15 height=15>

    I'm lazy, why code when excel can do all the work for you directly...

    Steve

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Synchronize Charts (Excel 2003)

    Hans, tx for the example, but as the concept is new to me, it took me a while to analyse the charts and figure out what all the charts were doing. I do understand the theory behind this but will need to do it myself to fully grasp the tweeks behind it!

    Tx for giving me something to work with!
    Cheers

    PS: Do you always call your sheets "Sheeet"?? <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    Regards,
    Rudi

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Synchronize Charts (Excel 2003)

    Steve,

    Tx for the theory and solution. Your practicality is reverse to mine. Personally I will think that a macro would have been easier. But thats what makes the world such an interesting place!!!
    Cheers
    Regards,
    Rudi

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Synchronize Charts (Excel 2003)

    > Do you always call your sheets "Sheeet"??

    Sometimes I call them "Rudi". My Excel calls them "Blad".

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Synchronize Charts (Excel 2003)

    <hr>Personally I will think that a macro would have been easier<hr>

    I knonw that it would take me a lot longer to write and debug a macro to do that than it would to set up the charts. And then having the event trigger to run and make it automatic adds a complexity to the workbook that the simple approach does not require.

    Steve

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Synchronize Charts (Excel 2003)

    If you still prefer a macro: right-click the sheet tab and select View Code from the popup menu.
    Create the following event procedure (I have taken the source range of the first chart in my sample workbook as example).

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer
    If Not Intersect(Target, Range("B2:C4")) Is Nothing Then
    For i = 2 To Me.ChartObjects.Count
    Me.ChartObjects(i).Chart.Axes(xlValue).MinimumScal e = _
    Me.ChartObjects(1).Chart.Axes(xlValue).MinimumScal e
    Me.ChartObjects(i).Chart.Axes(xlValue).MaximumScal e = _
    Me.ChartObjects(1).Chart.Axes(xlValue).MaximumScal e
    Next i
    End If
    End Sub

    See attached version

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Synchronize Charts (Excel 2003)

    Strangely, though I am no expert in VBA, my brain still tends to veer that way...I guess it also has to do with how open minded and practical the mind is to excel to get solutions before it switches over to VBA... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Cheers for the non VBA solution though...It helps me to keep an open mind.
    I have always enjoyed your replies in that they challenge me to think openly!!
    Regards,
    Rudi

  12. #12
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Synchronize Charts (Excel 2003)

    Hans,

    You have again gone further than you needed to....beyond the call of WOPR duty! THANKS!!!

    I sat for an hour last night working on this and came up with this code! After comparing it to yours, I guess yours is better in that it counts and loops through all the charts. This is more generalized...

    I am happy to say that I got my code to work too....

    PS: I think all that you taught me is there too....Option Explicit, variables declared....etc! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,
    Rudi

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Synchronize Charts (Excel 2003)

    I'm glad to hear that you worked out a solution yourself. You always learn most from that.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> Congrats on using Option Explicit!
    Now, if you really want to write 'neat' code, you should indent it consistently...
    <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  14. #14
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Synchronize Charts (Excel 2003)

    yes sir... <img src=/S/salute.gif border=0 alt=salute width=15 height=20>
    Regards,
    Rudi

Posting Permissions

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