Search code examples
vbaformattingresizeshapes

Trying to adjust size of shapes in VBA script but cannot get to size precisely


I am finding adjusting the size of shapes in Excel incredibly frustrating. For some reason, when I resize the height, the width adjusts to some random number, and vice versa. I do not have aspect ratio locked, and have Don't move or size with cells ticked on. The shapes are circles and I am trying to format them to have equal height and width (12 x 12)

Thinking this was due to some bug in the Format Shape controls, I wrote a very simple script to loop over all the shapes in my sheet and correct their properties, but the same problem persists.

Sub FixButFormat()
    Dim sh As Shape
    For Each sh In ActiveSheet.Shapes
        sh.Select
        Selection.ShapeRange.LockAspectRatio = msoFalse
        Selection.ShapeRange.Height = 12
        Selection.ShapeRange.Width = 12
        Selection.Placement = xlMove
        Selection.ShapeRange.LockAspectRatio = msoTrue
    Next sh
End Sub

Solution

  • Try the next code, please:

    Sub FixButFormat()
     Dim sh As Shape
        For Each sh In ActiveSheet.Shapes
            With sh
                .LockAspectRatio = msoFalse
                .height = 12
                .width = 12
                .placement = xlMove
                .LockAspectRatio = msoTrue
            End With
        Next sh
    End Sub
    

    But, if the shapes are circles, is it a possibility to not be circles, anymore? I mean having the same dimension on both axes. Otherwise, it is enough to modify only one dimension, keeping LockAspectRatio = msoTrue...