Align Shapes (Charts) On ActiveWorksheet

[Rajan_Verma]

Many Time we have Lot of Shapes On a Worksheets and we have to Align All those Shapes . This is a Code snippet to Make this Task easy.

Sub MakeMyShapes()

Dim intICounter         As Integer
Dim shpShape            As Shape
Dim sngHeight           As Single
Dim sngWidth            As Single
Dim IntNShapes          As Integer
Dim intleft             As Integer
Dim intTop              As Integer
Dim bytRow              As Byte
Dim intMaxH             As Integer
Dim intMaxW             As Integer
Dim intTempArr()        As Integer

bytRow = Application.InputBox("Please enter Number of Rows (0-255)")
    With ActiveSheet
        IntNShapes = .Shapes.Count
        intleft = .Shapes(1).Left
        intTop = .Shapes(1).Top
        sngHeight = .Shapes(1).Height
        sngWidth = .Shapes(1).Width
        
        ReDim intTempArr(IntNShapes)
            For intICounter = 0 To IntNShapes - 1
                intTempArr(intICounter) = .Shapes(intICounter + 1).Height
            Next
            intMaxH = WorksheetFunction.Max(intTempArr)
        
            For intICounter = 0 To IntNShapes - 1
                intTempArr(intICounter) = .Shapes(intICounter).Width
            Next
            intMaxW = WorksheetFunction.Max(intTempArr)
        
        For intICounter = 1 To IntNShapes
            .Shapes(intICounter).Left = intleft
            .Shapes(intICounter).Top = intTop
            If intICounter Mod bytRow = 0 Then
                intleft = .Shapes(intICounter + 1 - bytRow).Left + sngWidth
                intTop = .Shapes(1).Top
            Else
                intleft = .Shapes(intICounter).Left
                intTop = .Shapes(intICounter).Top + sngHeight
            End If
                .Shapes(intICounter).TextFrame.Characters.Text = intICounter
            Next
    End With
End Sub

[Junoon]

Hi,

here intMaxH & intMaxW are never used.

            If intICounter Mod bytRow = 0 Then
                intleft = .Shapes(intICounter + 1 - bytRow).Left + sngWidth

in above code, if bytRow=255, then wouldnt the shape be out of visible screen?


and in this 2nd loop it gives an error:

            For intICounter = 0 To IntNShapes - 1
                intTempArr(intICounter) = .Shapes(intICounter).Width
            Next
            intMaxW = WorksheetFunction.Max(intTempArr)