Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel Shapes Handling

Posted on 2000-04-10
12
Medium Priority
?
1,240 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

604 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