CODEWITHSUNDEEP
vbaexcel
Thomas Breakell
Thomas Breakell

Reputation: 61

Resizing and aligning all Charts in a workbook

Trying to create a VBA sub to align and resize all charts within my workbook through multiple worksheets, I believe I have a general idea to the resizing but No idea how to align them to a certain column. Still fairly new to excel, here is the code I have so far:

Sub AllChartAlign()
    Dim works As Worksheet
    For Each works In ActiveWorkbook.Worksheets
        For i = 1 To 2 Step 1
            With ActiveSheet.ChartObjects(i)
                .Height = 234
                .Width = 360
                .Left = Range("Q:Q").Left
            End With
        Next i
    Next works
End Sub

This works only on the active sheet, but will not loop through all the sheets.

Upvotes: 1

Views: 3516

Answers (1)

hstay
hstay

Reputation: 1439

You can use the .Left property for column alignement. If you want to align them to "C" column you could use:

Sub AllChartAlign()
    Dim works As Worksheet
    Dim Chrt as ChartObject
    For Each works In ActiveWorkbook.Worksheets
        For i = 1 To works.ChartObjects.Count
            Set Chrt = works.ChartObjects (i)
            With Chrt
                .Height = 234
                .Width = 360
                .Left = Range("C:C").Left
            End With
        Next i
    Next works
End Sub

EDIT

In case you want to align the charts top to bottom as well you may use .Top property and use the following:

Sub AllChartAlign()
    Dim works As Worksheet
    Dim Chrt as ChartObject
    Dim ChrtHeight As Double

    ChrtHeight = 234
    For Each works In ActiveWorkbook.Worksheets
        For i = 1 To works.ChartObjects.Count
            Set Chrt = works.ChartObjects (i)
            With Chrt
                .Height = ChrtHeight
                .Width = 360
                .Top = ChrtHeight * (i - 1)
                .Left = Range("C:C").Left
            End With
        Next i
    Next works
End Sub

EDIT 2

(Code should work now for any number of charts in a worksheet instead of considering that there is always 2 charts)

Upvotes: 1

Related Questions