Arrange Objects on Worksheet

  • Thread starter Thread starter WA
  • Start date Start date
W

WA

Hello,

I have 8 objects on a worksheet (all charts). Is there a function or VB code
to arrange those charts so that they line up and are the same length and
width?

Thanks for your assistance.
 
This will line them up in exactly the same position.

I'm not sure how they should be arranged (horizontal/vertical/or whatever...)

Option Explicit
Sub testme()

Dim myChart As ChartObject
Dim MstrChart As ChartObject
Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

With wks
Set MstrChart = .ChartObjects("Chart 1")

For Each myChart In .ChartObjects
If myChart.Name = MstrChart.Name Then
'skip it
Else
myChart.Top = MstrChart.Top
myChart.Left = MstrChart.Left
myChart.Width = MstrChart.Width
myChart.Height = MstrChart.Height
End If
Next myChart
End With

End Sub

Maybe you can tweak the code to do what you want (add a little to the top for
each chart...

Option Explicit
Sub testme()

Dim myChart As ChartObject
Dim MstrChart As ChartObject
Dim wks As Worksheet
Dim iCtr As Long

Set wks = Worksheets("Sheet1")

With wks
Set MstrChart = .ChartObjects("Chart 1")

iCtr = 0
For Each myChart In .ChartObjects
If myChart.Name = MstrChart.Name Then
'skip it
Else
iCtr = iCtr + 1
'the 3 is just a small gap
myChart.Top = MstrChart.Top + iCtr * (MstrChart.Height + 3)
myChart.Left = MstrChart.Left
myChart.Width = MstrChart.Width
myChart.Height = MstrChart.Height
End If
Next myChart
End With

End Sub
 
Back
Top