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;
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.
‘
‘ 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.
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.