Placing Shapes with VBA

I always enjoy Daniel Ferry’s (and Hui’s) Excel illusions. I tried the Curry Paradox one and it didn’t work for me. I’m not sure why. I think it’s either because my screen resolution is different or because I ran it on a secondary monitor, but the shapes weren’t moving to their rightful places.

I generally try to avoid using coordinates when I can. Sometimes they’re unavoidable, but if I can anchor a shape on a cell rather than a specific coordinate, I do it. I rewrote some of the code in the workbook to do that. Here’s the code to move the shapes;

Sub MoveIt(Obj As String, rTopLeft As Range)

    Dim s As Shape
    Dim dSteps As Double
    Const dCloseEnough As Double = 1.5
    Set s = Sheet1.Shapes(Obj)
    s.ZOrder msoBringToFront
    If s.Left < rTopLeft.Left Then dSteps = 1 Else dSteps = -1
    Do While Abs(s.Left – rTopLeft.Left) > dCloseEnough
        s.IncrementLeft dSteps
    s.Left = rTopLeft.Left
    If s.Top < rTopLeft.Top Then dSteps = 1 Else dSteps = -1

    Do While Abs(s.Top – rTopLeft.Top) > dCloseEnough
        s.IncrementTop dSteps
    s.Top = rTopLeft.Top
End Sub

And here’s an example of the code that calls the MoveIt procedure.

Sub Original()

‘ Setup Original Triangle

    Sheet1.Shapes(“Line Callout 1 7″).Visible = False
    MoveIt “Orange_L”, Sheet1.Range(“K9″)
    MoveIt “Green_L”, Sheet1.Range(“K10″)
    MoveIt “Red_Triangle”, Sheet1.Range(“C9″)
    MoveIt “Green_Triangle”, Sheet1.Range(“K7″)

End Sub

This one really lends itself to anchoring to cells rather than coordinates because the Excel sheet is setup in a square grid. I pass the top left cell for the shape into MoveIt. In MoveIt, I execute a Do Loop until the shape gets pretty close to where I want it, then I snap it into place.

One Comment

  1. Hui... says:

    Thanx for the comments Dick
    I wrote that in Excel 2010 and tested on 2007 in both cases it worked fine in full screen and in windowed modes ?
    I agree with your comments about use of co-ordinates. I wrote the original code for somebody from who wanted to know the co-ordinates of some shapes, I just spiced it up a bit.

Posting code or formulas in your comment? Use [cc] tags!

  • [cc_vb]Block of code goes here[/cc_vb]
  • [cci_vb]Inline code goes here[/cci_vb]
  • [cc]Formula goes here[/cc]

Leave a Reply