Arrange forms in a m*n grid-CodePudding

I would like to arrange forms (in this case circles) in a m*n grid. E.g.: 6 rows with 12 circles each.

The distance between each row and each circle in row should be the same, which is a lot of work doing it manually. How can I automate this with VBA? The count of rows and elements per row is to be specified. Optionally the size of the whole set of elements in the document.

CodePudding user response：

Yes, this can be done. I made a little example for you. We start of with a random positioning of shapes

I2 = space between shapes in X direction
J2 = space between shapes in Y direction
I3 = matrix element width
J3 is matrix elemen height

The Knop1 is a button with Macro code:

Sub Knop1_Klikken()
    Dim currentSheet As Worksheet
    Dim xOffset As Double
    Dim yOffset As Double
    Dim matrixW As Integer
    Dim matrixH As Integer
    
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Set currentSheet = ActiveSheet
    
    With currentSheet
        xOffset = .Cells(2, 9)
        yOffset = .Cells(2, 10)
        matrixW = .Cells(3, 9)
        matrixH = .Cells(3, 10)
        
        For i = 1 To matrixW
            For j = 1 To matrixH
                k = i   (j - 1) * matrixW
                If k <= .Shapes.Count Then
                    .Shapes(k).Left = i * xOffset
                    .Shapes(k).Top = j * yOffset
                End If
            Next
        Next
    End With
End Sub

End result after pressing button:

You can enhance the code if you want the shapes to fall in the middle of the cells, in this case you do not need the input fields anymore.