Solved

Excel Shapes Handling

Posted on 2000-04-10
12
1,086 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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
 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
My experience with Windows 10 over a one year period and suggestions for smooth operation
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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…

772 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