How to Place a Drawing Object on a Worksheet as a Control?

[SteveM]

I've built an optimization model and I would like to create a map-like
interface on a worksheet in which selected sites in the solution are
indicated on the map canvas by unhiding drawing objects. So I need to
place the drawing objects on the worksheet as Controls (i.e. Shapes I
think) but how to do that is not clear since the Control tool box
contains pictures but not shape objects.

Any ideas?


Thanks Much,

SteveM

 

[Ken Johnson]

I've built an optimization model and I would like to create a map-like
interface on a worksheet in which selected sites in the solution are
indicated on the map canvas by unhiding drawing objects. So I need to
place the drawing objects on the worksheet as Controls (i.e. Shapes I
think) but how to do that is not clear since the Control tool box
contains pictures but not shape objects.

Any ideas?

Thanks Much,

SteveM

Do the shapes have to be Control objects?
Shapes can be assigned to macros and can be hidden/unhidden by code.

Ken Johnson

 

[SteveM]

Do the shapes have to be Control objects?
Shapes can be assigned to macros and can be hidden/unhidden by code.

Ken Johnson

Ken,

Can you explain? A control has a project name associated with the
sheet, but a drawing object inserted from the drawing toolbar does
not. How would I refer to the drawing shape object in VB code without
a reference name?

SteveM

 

[Ken Johnson]

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

 

[Ken Johnson]

Oops!
The last line should have been...
ActiveSheet.Shapes(Application.Caller).Visible = True

Ken Johnson

 

[SteveM]

Oops!
The last line should have been...
ActiveSheet.Shapes(Application.Caller).Visible = True

Ken Johnson

Ken,

That works! Thanks!

BTW, the Shape.Visible property options only presented msoFalse and
msoTrue when inputing the code which are new to me and apparently do
nothing in this simple case. But True and False do work like desired.

SteveM

 

[Ken Johnson]

Ken,

That works! Thanks!

BTW, the Shape.Visible property options only presented msoFalse and
msoTrue when inputing the code which are new to me and apparently do
nothing in this simple case. But True and False do work like desired.

SteveM

Hi Steve,

You're welcome.
I don't know of any difference between msoTrue (or msoFalse) and True
(or False).

Ken Johnson