Make Drawing Object Unsizeable

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

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

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

Is there something that can be deleted by using a name box? Is there any
type of name that can be deleted without assigning a different name?

--
Regards,
Tom Ogilvy

"Ken Johnson" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Hi Tom,
I think I know what you mean?.....The names of shapes, ranges, or what
ever (can any other objects be named in the Name box?), cannot be
straight deleted using the Name box, they can only be given a new name.
Is this right?
I must admit I became a trifle confused when looking into the use of
the Name box versus Insert>Name>Define... in relation to naming a
shape. Say I add a ractangular AutoShape to a sheet, select it then
Insert>Name>Define... I see in the Refers to: box = "Rectangle 1". In
the Names in workbook: box I can type a suitable name, which must
follow the Names rules, so I can type (minus the speech marks)
"MyRectangle" but not "My Rectangle". Say I type (minus the speech
marks) "MyRectangle" then click Add>OK. Then, in the Name box, with
that rectangle still selected, I still see "Rectangle 1". Furthermore,
I can click in the Name box and change it to (minus the speech marks)
"My Rectangle". Now, if I go Insert>Name>Define... I see in the Refers
to: box ="My Rectangle". Also, If I use the Name box to name more than
one shape, I never see a list of the named shapes when I click on the
Name box's drop down arrow, just a list of the named ranges.
All that I can conclude from this is that the name I used in the Name
box ("My Rectangle" ie with the space) is not really a name, since...
1) it violates Excel's naming rule and...
2) when you click on the Name box's drop down arrow you don't see a
list of all the named shapes, only a list of named ranges.
So, if I'm correct, and it isn't a name then what should I call it?

Also, say I want to write code to manipulate a shape on Sheet1 that I
have "named" via the Name box "My Rectangle" and I want to give
it a completely different variable name, say "Oblong" then I would
write...

Dim Oblong as Shape
Set Oblong = Sheet1.Shapes("My Rectangle")
rest of code...

now how would I write code to set Oblong using the "real" name if I
had named the shape MyRectangle using Insert>Name>Define...?

Ken Johnson

When you go do Insert=>Name=>Define with a shape selected, you do see

="Rectangle 1"
in the refers to range as an example.

Go ahead and assign a name that has that refers to.

No go back to the sheet and rename the shape something else using the name
box.

Now go back to insert name define and you will see that the name you
created, still refers to the text string "Rectangle 1". So there is no link
between the shape and the defined name other than it refers to a string that
contains the name of the shape at the time you created it.

the dropdown box only displays names of ranges that are hard coded in refers
to because the action of selecting the name selects the refered to range.
You can type in the name of a calculated range and hit enter and get the
same action. You can type in the name of the shape and hit enter and the
shape will be selected. Why it isn't offered in the dropdown, I can't
say. Insert=>Names=>Define is just for defining Names (name objects).

the name box is more equivalent to the Edit=>Goto box although the name
box is more robust in terms of support for shapes.

Your final point/question is moot. You can't name a shape using
Insert=>Name =>Define. As I previously stated, you created a name that
refers to a string value.

--
Regards,
Tom Ogilvy



"Ken Johnson" <[email protected]> wrote in message
news:[email protected]...
> Hi Tom,
> I think I know what you mean?.....The names of shapes, ranges, or what
> ever (can any other objects be named in the Name box?), cannot be
> straight deleted using the Name box, they can only be given a new name.
> Is this right?
> I must admit I became a trifle confused when looking into the use of
> the Name box versus Insert>Name>Define... in relation to naming a
> shape. Say I add a ractangular AutoShape to a sheet, select it then
> Insert>Name>Define... I see in the Refers to: box = "Rectangle 1". In
> the Names in workbook: box I can type a suitable name, which must
> follow the Names rules, so I can type (minus the speech marks)
> "MyRectangle" but not "My Rectangle". Say I type (minus the speech
> marks) "MyRectangle" then click Add>OK. Then, in the Name box, with
> that rectangle still selected, I still see "Rectangle 1". Furthermore,
> I can click in the Name box and change it to (minus the speech marks)
> "My Rectangle". Now, if I go Insert>Name>Define... I see in the Refers
> to: box ="My Rectangle". Also, If I use the Name box to name more than
> one shape, I never see a list of the named shapes when I click on the
> Name box's drop down arrow, just a list of the named ranges.
> All that I can conclude from this is that the name I used in the Name
> box ("My Rectangle" ie with the space) is not really a name, since...
> 1) it violates Excel's naming rule and...
> 2) when you click on the Name box's drop down arrow you don't see a
> list of all the named shapes, only a list of named ranges.
> So, if I'm correct, and it isn't a name then what should I call it?
>
> Also, say I want to write code to manipulate a shape on Sheet1 that I
> have "named" via the Name box "My Rectangle" and I want to give
> it a completely different variable name, say "Oblong" then I would
> write...
>
> Dim Oblong as Shape
> Set Oblong = Sheet1.Shapes("My Rectangle")
> rest of code...
>
> now how would I write code to set Oblong using the "real" name if I
> had named the shape MyRectangle using Insert>Name>Define...?
>
> Ken Johnson
>

Thanks Tom,
Ken Johnson