Hi all,

I need to insert some drawing objects into a worksheet. I would like
the user to be able to select the object (so locking it won't do) and
be able to move it, but not resize it. Is there any way to disable the
sizing handles on a drawing object? (I'm talking specifically about
AutoShapes here.)

While we're at it, can drawings be programmed to respond to different
kinds of events, like right click, etc?

Thank you for any help anyone might be able to offer.

Hi An,
This is the only way I can think of, applied to an autoshape
(Rectangle) named "Square" on Sheet1.
1. Select the drawn autoshape
2. With the Formula Bar visible, click in the Name Box (on the left
side of the Formula Bar) and type "Square" (not the speech marks
though) then click Enter. This attaches the name "Square" to the
autoshape so that it can be easily referred to in the code.
3.Right click the Sheet1 Sheet Tab and select View code.
4. Click on the top left drop down arrow (Just to the right of
"(General)" then select "Worksheet"
5. Paste in the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Square As Shape
Set Square = Me.Shapes("Square")
Square.Height = Application.CentimetersToPoints(5) 'resets height to
5 cm
Square.Width = Application.CentimetersToPoints(5) 'resets width to 5
cm
End Sub

When a user clicks on the Square they can move it and resize it etc,
however, as soon as they select a cell on the worksheet the code will
run and will reset the squares sides back to 5 centimeters.

Hope this helps.

I don't know of any events associated with autoshapes, however, they
can have assigned to them a macro, which I guess is equivalent to a
click event, but then moving the autoshape around the sheet is not so
easy since it would require a right click followed by left click then
drag.

Ken Johnson

Ken:

As you've more or less admitted here, this is a
far-less-than-satisfactory solution to my problem.

Still, knowing that the "ideal" solution isn't possible will get me off
this dead-end road and save me a lot of time. So, I must thank you so
much for your effort.

I'll have to try a completely different approach.

Thanks again,
Cheers.

An

You're welcome An, thanks for the feedback.
Ken Johnson

Ken,

I happen to read this thread. Saw your reply. It solved my long term
querry of naming a shape. It is as simple as selecting the shape then
changing the name which appears near top left corner had toatally
evaded me thus far. Thank you very much.

A V Veerkar


--
avveerkar
------------------------------------------------------------------------
avveerkar's Profile: http://www.excelforum.com/member.php...o&userid=30338
View this thread: http://www.excelforum.com/showthread...hreadid=503588

Hi A V,
Don't you love it when you stumble across the solution to long standing
problem.

Just a couple of things that need to be kept in mind when naming
shapes:

1. The name disappears when the shape is grouped with other shapes or,
if it was originally grouped when named, then the name disappears when
the group is ungrouped.

2. The name can be changed in the Name box, but can't be deleted.

Ken Johnson