Share via

Distribute Method [Excel 2003 VBA Language Reference]

  • Article

Horizontally or vertically distributes the shapes in the specified range of shapes.

expression.Distribute(DistributeCmd, RelativeTo)

expression Required. An expression that returns one of the objects in the Applies To list.

MsoDistributeCmd

MsoDistributeCmd can be one of these MsoDistributeCmd constants.
msoDistributeHorizontally
msoDistributeVertically

MsoTriState

MsoTriState can be one of these MsoTriState constants.
msoCTrue
msoFalse
msoTriStateMixed
msoTriStateToggle
msoTrue

Example

This example defines a shape range that contains all the AutoShapes on myDocument and then horizontally distributes the shapes in this range. The leftmost shape retains its position.

Set myDocument = Worksheets(1)
With myDocument.Shapes
    numShapes = .Count
    If numShapes > 1 Then
        numAutoShapes = 0
        ReDim autoShpArray(1 To numShapes)
        For i = 1 To numShapes
            If .Item(i).Type = msoAutoShape Then
                numAutoShapes = numAutoShapes + 1
                autoShpArray(numAutoShapes) = .Item(i).Name
            End If
        Next
        If numAutoShapes > 1 Then
            ReDim Preserve autoShpArray(1 To numAutoShapes)
            Set asRange = .Range(autoShpArray)
            asRange.Distribute msoDistributeHorizontally, False
        End If
    End If
End With

Applies to | ShapeRange Collection