[Solved] Additional VBA for working with Excel Shapes

[Sam6284]

I'm referring to Musiha Mahfuza Mukta excellent article "How to Perform Drawing of Objects with VBA in Excel (2 Easy Examples)" posted 12/24/23.

Context: I've created a right triangle shape, filled it with a pattern, and named it "Mountain". I now only need to refer to it in VBA as ActiveSheet.Shapes("Mountain") followed by a dot for methods/properties.

I'm confused with some of the answers I've found.

I want to do two things with this shape in VBA using values in named cells:

1) Set it's height named Gain_M (single) and width named AB (for it's trig id, also single)
2) Move the lower left corner to cell "D11". I'll give it a name, too, eventually.

If there's more examples on sizing and positioning Excel shapes that can be squeezed in, that'd be a plus. All I need is 1 and 2.

 

[shamimarita]

Hello Sam6284,

Here's how you can set the height and width of the shape and move its lower-left corner to cell D11.

To set the Height and Width use the following VBA code.

With ActiveSheet.Shapes("Mountain")
    .Height = Range("Gain_M").Value
    .Width = Range("AB").Value
End With

To Move the Lower Left Corner to D11 use the following VBA code.

With ActiveSheet.Shapes("Mountain")
    .Top = Range("D11").Top + Range("D11").Height - .Height
    .Left = Range("D11").Left
End With

This code sets the shape's height and width based on the values in the named cells Gain_M and AB and then moves the shape so its lower-left corner aligns with cell D11.