View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Ken Johnson Ken Johnson is offline
external usenet poster
 
Posts: 1,073
Default Make Drawing Object Unsizeable

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