Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Drawing Object Unsizeable
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Drawing Object Unsizeable
You're welcome An, thanks for the feedback.
Ken Johnson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Drawing Object Unsizeable
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Drawing Object Unsizeable
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Drawing Object Unsizeable
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" wrote in message oups.com... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Drawing Object Unsizeable
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 InsertNameDefine... in relation to naming a shape. Say I add a ractangular AutoShape to a sheet, select it then InsertNameDefine... 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 AddOK. 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 InsertNameDefine... 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 InsertNameDefine...? Ken Johnson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Drawing Object Unsizeable
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" wrote in message ups.com... 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 InsertNameDefine... in relation to naming a shape. Say I add a ractangular AutoShape to a sheet, select it then InsertNameDefine... 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 AddOK. 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 InsertNameDefine... 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 InsertNameDefine...? Ken Johnson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Drawing Object Unsizeable
Thanks Tom,
Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Re-number drawing object references | Excel Programming | |||
Inserting a drawing object into a cell | Excel Programming | |||
Controlling the movement of a drawing object | Excel Programming | |||
Drawing Object | Excel Programming | |||
Drawing object references | Excel Programming |