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
        DoEvents
    Loop
    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
        DoEvents
    Loop
    s.Top = rTopLeft.Top
   
    Sheet1.Range(“N10″).Select
   
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
   
    ResetGrid
   
    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 Chandoo.org 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 <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: