Sleeper: Grouping/Positioning Charts

[jamescol]

I have a need to place 3 charts on the same Excel WS/Chart sheet (doesn't matter which). Can someone post or point me to some code that will size each chart equally and align them (centered horizontally and vertically) on a single sheet?

Thanks,
James

[tommy bak]

Hi
Put the 3 charts on a chart-sheet and make it the active sheet.
Then run this macro. You may have to change the charts names
Adjust lngUserWidth to fill your needs.

Sub SetCharts()
    Dim lngWidth, lngHeight, lngUserWidth, lngLeft
    lngWidth = ActiveChart.ChartArea.Width
    'set lngUserWidth = lngWidth for full width
    lngUserWidth = 700
    lngLeft = lngWidth / 2 - lngUserWidth / 2
    lngHeight = ActiveChart.ChartArea.Height \ 3
    With ActiveSheet.ChartObjects("Chart 1")
        .Top = 1
        .Left = lngLeft
        .Height = lngHeight
        .Width = lngUserWidth
    End With
    With ActiveSheet.ChartObjects("Chart 2")
        .Top = lngHeight
        .Left = lngLeft
        .Height = lngHeight
        .Width = lngUserWidth
    End With
    With ActiveSheet.ChartObjects("Chart 3")
        .Top = lngHeight * 2
        .Left = lngLeft
        .Height = lngHeight
        .Width = lngUserWidth
    End With
End Sub

BR
Tommy Bak

[clvestin]

Here's a slight variation that places chartobjects 2 wide equally spaced down the sheet. A few tweeks involved for horizontal and vertical spacing.

for k=0 to Ubound(myArray)
Set ch = ActiveSheet.ChartObjects.Add(10 + Application.Width / 2.2 * (k Mod 2), 10 + spce * (Int(k / 2)), Application.Width / 2.3, 135)