Resize object based on formula and maintain aspect ratio

[IanA]

Hi, I have followed the YouTube tip below to create a resized object based on the results of a formula.

It works pretty well, but I want to ensure the aspect ratio of the object is maintained as a perfect square.

I've used the below but for some reason even though the width and height are referencing a value in the same cell the aspect ratio doesn't stay constant

With ActiveSheet.Shapes.Range(Array("square1"))
.Width = Range("B3").Value
.Height = Range("B3").Value
End With

Any thoughts?

 

[Anthony47]

Try controlling the "aspect ratio":

With ActiveSheet.Shapes.Range(Array("square1"))
    .LockAspectRatio = msoFalse
    .Width = Range("B3").Value
    .Height = Range("B3").Value
End With

Or, if the starting shape is a perfect square, set .LockAspectRatio = msoTrue and assign only the new Width (Height will resize maintaining the aspect ratio)

Bye

 

[IanA]

Try controlling the "aspect ratio":

With ActiveSheet.Shapes.Range(Array("square1"))
    .LockAspectRatio = msoFalse
    .Width = Range("B3").Value
    .Height = Range("B3").Value
End With

Or, if the starting shape is a perfect square, set .LockAspectRatio = msoTrue and assign only the new Width (Height will resize maintaining the aspect ratio)

Bye

Thank you. I'll give it a go

 

[IanA]

Although the control for aspect ratio provided by Anthony47 was a useful build it didn't resolve the problem.
After further investigation it appears the issue lies in the fact that objects exist in another layer on the worksheet and when zooming in and out, scaling of this layer is affected, hence the dimensions of the object appear to change. When returning to 100% zoom however height and width return to the correct aspect ratio and if saved out as a pdf relative dimensions of objects are correct.