Solved

Excel Shapes Handling

Posted on 2000-04-10
12
1,057 Views
Last Modified: 2008-03-10
I put a "text" text box on a worksheet (not a VB control text box I mean). However, when I want to change the size of the shape text box, it does not appear in the properties list, and I cannot seem to find where I can get an object handle so as to manipulate it.

If I insert a piece of investigative code into the worksheet, such as numshapes = sheet(1).shapes.count, then I get an answer of eleven; but I have 8 command buttons on the sheet, plus the two shapes (one of which is this textbox), so Excel evidently knows the shapes are there. I am puzzled as to why I cant find their references, and also as to why Excel recognises command buttons as shapes?

Thanks for any ideas.
 
0
Comment
Question by:futureminds
  • 5
  • 4
  • 3
12 Comments
 
LVL 13

Expert Comment

by:cri
Comment Utility
Name then the moment you add them.

Here an excerpt of
http://msdn.microsoft.com/library/officedev/office97/web/010.htm#CH010H101


Each shape is assigned a default name (for example, "Rectangle 3") when you add it to the Shapes collection. To give the shape a more useful, meaningful name, use the Name property. The following example adds a rectangle to myDocument and gives the rectangle the name "Red Square."

myDocument.Shapes.AddShape(msoShapeRectangle,144, 144, 72, 72).Name = "Red Square"
0
 
LVL 13

Expert Comment

by:cri
Comment Utility
Oops: ...Name them...
0
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
Hi FutureMinds

This little sub will loop through all shapes in sheets(1) and control if it's a textbox (=msoTextBox), then change width and height to 100.

Command Buttons inserted via the Forms menu are also considered as shapes.

This is a list from the types of shapes recognised by Excel (extract from VBA helpfile on Find for shapes:

Shape, ShapeRange      Shape type. Can be one of the following MsoShapeType constants: msoAutoShape, msoCallout, msoChart, msoComment, msoEmbeddedOLEObject, msoFormControl, msoFreeform, msoGroup, msoLine, msoLinkedOLEObject, msoLinkedPicture, msoMedia, msoOLEControlObject, msoPicture, msoPlaceholder, msoShapeTypeMixed, or msoTextEffect. Read-only Long. In Microsoft Excel, this property cannot be msoMedia or msoPlaceholder (these constants are used with shapes in other Microsoft Office applications)..

Hope this helps

Calacuccia
0
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
More...

Go to Excel VBA help and look for help for the Shape object (member of shapes collection), and look at the properties there to find an exhaustive list of shape properties.

In did not pay enough attention to the list of mso..types, strange enough msoTextBox is not included on the helpfile.

Good Luck
Calacuccia
0
 

Author Comment

by:futureminds
Comment Utility
Hi cri:

I was looking really to find a shape that I had created cold on the sheet, not from code. I didnt try your code yet, but how would I obtain a reference to a shape that I had put onto the sheet "manually"?

Hi calacuccia too:

I only seem to be able to track down the text box shape if I insert a piece of code and debug the proc so that I can see what the variable holds as it cycles through the shapes. This approach picked up a text box shape (called Text Box 26), but when I tried to manipulate that shape using that name, nothing happened. It was only when I used a shape called "Group 35" that I was able to hide and show the text box in question. Strange. I have nowhere near 35 groups on my sheet - only one.
0
 
LVL 13

Expert Comment

by:cri
Comment Utility
Re-read your question and your comment. Do not understand anymore. What do you mean with "...where I can get an object handle so as to manipulate it..." Focusing it and right clicking it would fit this bill, but you certainly know this.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
Hi FutureMinds,

I see now I forgot to post the code :-) Silly me.

Here it is

Sub TextBoxFormat()
Dim s As Shape
For Each s In Sheets(1).Shapes
If s.Type = msoTextBox Then
s.Height = 100
s.Width = 100
End If
Debug.Print s.Name; Tab(20) ; s.Type
Next s
End Sub

The Debug.Print s.Name ; Tab(20); s.Type line will print all shapes with their name and type number in the immediate window in VBEditor. To view the results, press Ctrl+G in the VB Editpr.

Cheers
Calacuccia
0
 

Author Comment

by:futureminds
Comment Utility
Hi cri:

When I right-click on the shape, I get a window for sure, allowing me to "format" and set deafults, etc, but there is no choice to get the Properties, like there is for othre control objects. For this reason I cannot set any of the shapes features at all. But, as I said in my last comment, I can hide and show (that's all I wanted to do and it;s all I've tried) using code. But the code has to focus on a group of shapes called "Group 35", and this is astonishing, since I only have eleven shapes on the sheet, and as far as I know, only one group with this text box shape and a small .bmp icon.


Hi calacuccia:

Yes, I got to that point already, as it seemed to be the only way to get the informaiton form Excel was to cycle through all possible shapes (that's how I found out that Excel treats command buttons as shapes). So thanks for that, but I really need to get tot he bottom of why I cant see this box shape and its properties outside of any code.
0
 
LVL 17

Accepted Solution

by:
calacuccia earned 50 total points
Comment Utility
Hi FutureMinds,

When you select the text box, the name of this object, and the only visible property will be on top left of the sheet, in the names box, on the same height as the formula window.

You can just double-click in there and type  to change the name, or just read it.

Then you can change the properties in VBA like this:
Sheets(1).Shapes("YourName").Height = 100


Is that what you where looking for ?

Calacuccia
0
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
Regarding group 35, I think that is because Excel automatically adds a number to each shape you create.

For example, imagine you make on a blank sheet 3 text boxes. They will get names TextBox1, TextBox2 and TextBox3.

Thne you group them. Their name will now be group4, although its the only shape on your sheet.

Even deleting the forms/Shapes won't have any effect.

Cheers
Calacuccia
0
 

Author Comment

by:futureminds
Comment Utility
Hi cri:

When I right-click on the shape, I get a window for sure, allowing me to "format" and set deafults, etc, but there is no choice to get the Properties, like there is for othre control objects. For this reason I cannot set any of the shapes features at all. But, as I said in my last comment, I can hide and show (that's all I wanted to do and it;s all I've tried) using code. But the code has to focus on a group of shapes called "Group 35", and this is astonishing, since I only have eleven shapes on the sheet, and as far as I know, only one group with this text box shape and a small .bmp icon.


Hi calacuccia:

Yes, I got to that point already, as it seemed to be the only way to get the informaiton form Excel was to cycle through all possible shapes (that's how I found out that Excel treats command buttons as shapes). So thanks for that, but I really need to get tot he bottom of why I cant see this box shape and its properties outside of any code.
0
 

Author Comment

by:futureminds
Comment Utility
Hi Calacuccia

Sorry about the resend; apparently when the browser does a refresh it resends the previous question/comment, which I don't understand. Anyway, yes I see that Excel/Windows increments the name of a shape or group every time one is created or shapes are regrouped. I cant see into OOP principles - if that's what's going on - enough to make sense of this. It seems odd to me that into this textbox shape object, I can type hard-wired text, yet the object can be renamed when it is regrouped. This means that if I perform a manual re-layout of the two grouped shapes, then when the code to hide or resize the shapes is run, it encounters a runtime error because Excel cannot find the groupname (instead of group35 it is now group36 for eg).

Let me have a bit more time to assimilate this info and I'll get back.

Thanks for now
Futureminds.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Have you ever had the experience that you had to follow 10 steps over and over again every time when you need to nicely forward an important email to your manager? Fear no more! With the help of the Quick Steps feature in Outlook 2010, your old chor…
Article by: dandraka
There are several quick shortcuts that can make your life easier in Microsoft Programs.  These simple tips and tricks will your work more productive and you faster at completing your tasks! MS Word (1) Creating Re-usable Scraps You can create s…
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now