View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Ken Johnson Ken Johnson is offline
external usenet poster
 
Posts: 1,073
Default 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