Over at JMT there was a strange question asked. How do you get the cells that a drawn shape lies over? I'm was amazed to see that there were methods in the OM to get this, heres some example code
''Code by Ross McLean - Methods In Excel
''Tells you the top left and bottom right cells
''www.blog.methodsinexcel.co.uk
''Use freely and at you own risk!
''Warning - this clearly give the rect of the shap not what the
''actuall shape looks like on scree!
Dim sh As Excel.Shape
Dim sText As String
sText = "Here are the shape details for the active workbook: " & vbNewLine
For Each sh In ActiveSheet.Shapes
With sh
sText = sText & sh.Name & ": (" & _
.TopLeftCell.Cells.Row & "," & .TopLeftCell.Cells.Column & "),(" & .BottomRightCell.Cells.Row & "," & .BottomRightCell.Cells.Column & ")" & vbNewLine
End With
Next
MsgBox sText, vbOKOnly, "Methods In Excel"
End Sub
I ran this and got the following answer:
As you can see this is not quite what you would expect, it's kinda drawing a rectangle - well this is windows!
One caveat is the BottomRightCell will give funny results. If the right and bottom shape edges lie within the cell, then the result is as expected. But if the shape abuts the right and bottom cell edges*, then the cell returned is one to the right and down one.
(* You can have the shape aligned to the cell edges using the snap to grid feature on the Drawing toolbar.)
Thanks Nicholas