Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Aligning charts (2003)

    I have 2 chart objects that I want to line up exactly over each other (one is mainly transparent, so they should overlay exactly). I'm using the following code, but the highlighted lines cause the function to crash (can't set the relevant property). Does anyone know of a workaround to programatically set these properties?

    Function AlignGraphs()
    Dim c1 As Chart, a1 As Axis, c2 As Chart, a2 As Axis, _
    p1 As PlotArea, p2 As PlotArea, ca1 As ChartArea, ca2 As ChartArea

    Set c1 = shtModel.ChartObjects(1).Chart
    Set a1 = c1.Axes(xlValue)
    Set p1 = c1.PlotArea
    Set c2 = shtModel.ChartObjects(2).Chart
    Set a2 = c2.Axes(xlValue)
    Set p2 = c2.PlotArea
    Set ca1 = c1.ChartArea
    Set ca2 = c2.ChartArea
    a2.MaximumScale = a1.MaximumScale
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">ca2.Top = ca1.Top
    ca2.Left = ca2.Left
    ca2.Width = ca1.Width
    ca2.Height = ca1.Height</span hi>
    p2.Top = p1.Top
    p2.Left = p2.Left
    p2.Width = p1.Width
    p2.Height = p1.Height

    End Function
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

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

    Re: Aligning charts (2003)

    You can't change the position and size of the chart area of a chart, you must set the position and size of the chartobject instead. You *can* set the position and size of the plot area.

    But why don't you overlay the two graphs in one chart object?

  3. #3
    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: Aligning charts (2003)

    You might take a look at MS MVP Jon Peltier's code at Align Charts Using VBA

    Steve

  4. #4
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Aligning charts (2003)

    Awesome, does the job perfectly. Thanks Steve
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  5. #5
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Aligning charts (2003)

    Thanks Hans. The simple (and slightly shameful) answer is I don't know how to overlay the graphs in a single chart object! The solution Steve has posted solves the immediate problem anyway, but any pointers for future reference would be appreciated. Thanks again
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  6. #6
    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: Aligning charts (2003)

    Often, instead of overlaying charts, one could create 1 chart which has different types in it and not need to align them at all. But this will depend on the charts and the data...

    Steve

  7. #7
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Aligning charts (2003)

    Thanks. Hopefully the image below shows what I'm after. There are two horizontal bar charts, one of which is mainly transparent (with the green semi-transparent bars). This green graph is laid on top of the other chart, but offset slightly (so that the green bars can be clearly seen even if their value is less than that on the more colourful chart).

    The problem I had was that the number of bars is variable, so although I could line the charts up nicely by hand once, as soon as the vertical axis changed everything went awry. Now all is hunky dory!
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

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

    Re: Aligning charts (2003)

    Once you have created a chart, you can add new data series by selecting the chart and using Chart | Add Data...
    You can change the chart type for individual series in a chart by selecting it and using Chart | Chart Type...
    So you could have some series as columns, and other series as lines. (All this within reason, of course, you can't combine a line chart and a pie chart).
    Also, you can specify that some series use a secondary x-axis and/or y-axis, with a different scale setting than the primary one.

  9. #9
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Aligning charts (2003)

    Thanks Hans. I'm sure I used to know that and must have forgotten it somewhere along the line! Cheers for the reminder.
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  10. #10
    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: Aligning charts (2003)

    <hr>(All this within reason, of course, you can't combine a line chart and a pie chart).<hr>

    It may not be too practical, but it can be done easily. Add 2 lines to the chart and change the type of one into a pie...

    Steve

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

    Re: Aligning charts (2003)

    Umm yes, I meant that combining a pie chart and a line chart isn't very meaningful, but you're correct that technically it is possible

Posting Permissions

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