futureminds
asked on
Excel Shapes Handling
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.
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.
Oops: ...Name them...
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
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
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
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
ASKER
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.
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.
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.
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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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.
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.
ASKER
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.
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.
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