Solved

Excel Shapes Handling

Posted on 2000-04-10
12
1,077 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
ID: 2701483
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
ID: 2701487
Oops: ...Name them...
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2701524
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
ID: 2701537
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
ID: 2702137
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
ID: 2702284
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 17

Expert Comment

by:calacuccia
ID: 2702311
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
ID: 2703114
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
ID: 2703130
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
ID: 2703149
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
ID: 2703805
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
ID: 2703849
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

863 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

25 Experts available now in Live!

Get 1:1 Help Now