VBA to resize shape incrementally

[Lil Stinker]

Just like how you can resize a shape using the up and down arrow buttons for height and width, how would you write this in VBA? I'm trying to mimic the inherent ability of Excel to increase or decrease the size of a shape incrementally using my own button shapes on the worksheet. Each time you click the Up button, I want the shape to increase height and width by 0.02" until it reaches 1" and stops. Similar for decreasing until it reaches 0.1" then stops.

I tried using the macro recorder but between .ScaleHeight, .ScaleWidth or .Height, .Width, I only seem to get one size adjustment. Haven't been able to find anything on the web that seems to work for what I want to do either.

Does anyone have any suggestions?

 

[KOKOSEK]

something like this:

Sub Resize(z As Integer)
    Set mySh = ActiveSheet.Shapes.Range(Array("myShape"))
    mySh.Top = mySh.Top - (z / 2)
    mySh.Left = mySh.Left - (z / 2)
    
    mySh.Height = mySh.Height + z
    mySh.Width = mySh.Width + z
End Sub

'assign into buttons
Sub Bigger()
    Resize (1)
End Sub
Sub Smaller()
    Resize (-1)
End Sub

add some IFs to min / max size.

 

[Lil Stinker]

This works brilliantly!
Forgive me but my IFs don't seem to stop anything from running. Could you provide an example or tell me what I'm doing wrong?

Sub Smaller()
   If Sheet1.Shapes.Range(Array("myShape")).Height <= 0.1 Then Exit Sub
   Resize (-1)
End sub

I can't seem to get anywhere unless I change the <= to >=, but then it Exits every time.

 

[Lil Stinker]

Okay, I fixed my IFs situation. I didn't realize the numbers I was seeing in the Height and Width fields as I was running through the code do not directly correlate in VBA. Apparently, you can't use decimals with .Height & .Width (even though that's how Excel displays it in the dialog box)? I finally got where I wanted to be using whole numbers.

Thanks again!