Snap Object to cell borders

I'm writing an application where users will move around several
objects (Label fields) on a worksheet

1. How can I make the objects "snap" to the cell borders ?
2. Is it possible to "link" two or more objets (on different worksheets)
so that all "linked" objects are moved simultaneously if one of them
is moved?

Thanks a lot for any ideas or suggestions !

Ed

"Snapping" is accomplished by adjusting an object's Top and Left properties
to those of the underlying cell:

Dim x As Shape
Set x = Sheet1.Shapes(1)
x.Left = x.TopLeftCell.Left
'etc

No way to link the moving of objects. You'd have to write a macro to do
that.

--
Jim
"Ed Schwartz" <[email protected]> wrote in message
news:[email protected]...
|
| I'm writing an application where users will move around several
| objects (Label fields) on a worksheet
|
| 1. How can I make the objects "snap" to the cell borders ?
| 2. Is it possible to "link" two or more objets (on different worksheets)
| so that all "linked" objects are moved simultaneously if one of them
| is moved?
|
| Thanks a lot for any ideas or suggestions !
|
| Ed
|

Thanks a lot, Jim - works very well !


>
> Dim x As Shape
> Set x = Sheet1.Shapes(1)
> x.Left = x.TopLeftCell.Left
> 'etc
>
> No way to link the moving of objects. You'd have to write a macro to do
> that.
>

> 1. How can I make the objects "snap" to the cell borders ?
On Excel's Drawing toolbar, click Draw > Snap > To Grid.



> 2. Is it possible to "link" two or more objets (on different worksheets)
> so that all "linked" objects are moved simultaneously if one of them
> is moved?
If the shapes on sheet1 and sheet2 were given the same names on both sheets,
you could use the following code to update their positions.

'In Sheet1's code module
Private Sub Worksheet_Activate()
Dim shp As Shape
For Each shp In Sheets("Sheet1").Shapes
shp.Top = Sheets("Sheet2").Shapes(shp.Name).Top
shp.Left = Sheets("Sheet2").Shapes(shp.Name).Left
Next
End Sub

'In sheet2's code module
Private Sub Worksheet_Activate()
Dim shp As Shape
For Each shp In Sheets("Sheet2").Shapes
shp.Top = Sheets("Sheet1").Shapes(shp.Name).Top
shp.Left = Sheets("Sheet1").Shapes(shp.Name).Left
Next
End Sub


Regards,
Vic Eldridge