How to Place a Drawing Object on a Worksheet as a Control?
Hi Steve,
You could give the shape a meaningful name by selecting the shape then
clicking in the name box, which is on the left side of the Formula
Bar, type the name ( eg MyShape1 or whatever, just something that
indicates the shape's role in your project) then press Enter.
After giving the Shape a meaningful name you would refer to it in your
code using...
ActiveSheet.Shapes("MyShape1")
eg ActiveSheet.Shapes("MyShape1").Top = 100 will position it 100
points from the top of the sheet.
If you needed to refer to it often in your code you could use an
Object variable...
Dim Thingy as Shape
Set Thingy = ActiveSheet.Shapes("MyShape1")
Thingy.Top = 100
If you assign a macro to a shape, you can refer to the shape in its
macro using Application.Caller
eg say after clicking the shape you want the shape to be hidden, then
reappear after the code has finished, then the first line of the macro
could be...
ActiveSheet.Shapes(Application.Caller).Visible = False
and the final line...
ActiveSheet.Shapes("MyShape1").Visible = True
Ken Johnson
|