Deleting Shapes in a worksheet
 

I have worksheets that contain 1 or more text boxes. I need to make copies
of these worksheets but then delete the text boxes.
I have looked at the shapes collection but I can't figure out how to
identify the number of text boxes, select them, and then delete them.

Need help.

Thanks
Glen

Deleting Shapes in a worksheet
 

Hi Glen,

Glen Mettler wrote:
I have worksheets that contain 1 or more text boxes. I need to make
copies of these worksheets but then delete the text boxes.
I have looked at the shapes collection but I can't figure out how to
identify the number of text boxes, select them, and then delete them.

Something like this should work to remove all ActiveX TextBox controls from
a Worksheet:

Sub RemoveActiveXTextBoxes()
Dim ole As OLEObject

For Each ole In Sheets("Sheet1").OLEObjects
If TypeOf ole.Object Is MSForms.TextBox Then
ole.Delete
End If
Next ole
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Deleting Shapes in a worksheet
 

not ActiveX. Native Excel textbox

Glen

"Jake Marx" wrote in message
...
Hi Glen,

Glen Mettler wrote:
I have worksheets that contain 1 or more text boxes. I need to make
copies of these worksheets but then delete the text boxes.
I have looked at the shapes collection but I can't figure out how to
identify the number of text boxes, select them, and then delete them.

Something like this should work to remove all ActiveX TextBox controls
from a Worksheet:

Sub RemoveActiveXTextBoxes()
Dim ole As OLEObject

For Each ole In Sheets("Sheet1").OLEObjects
If TypeOf ole.Object Is MSForms.TextBox Then
ole.Delete
End If
Next ole
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Deleting Shapes in a worksheet
 

Hi Glen,

Try:

ActiveSheet.TextBoxes.Delete

---
Regards,
Norman



"Glen Mettler" wrote in message
...
I have worksheets that contain 1 or more text boxes. I need to make copies
of these worksheets but then delete the text boxes.
I have looked at the shapes collection but I can't figure out how to
identify the number of text boxes, select them, and then delete them.

Need help.

Thanks
Glen

Deleting Shapes in a worksheet
 

Glen Mettler wrote:
not ActiveX. Native Excel textbox

Then something like this should work:

Sub RemoveFormsTextBoxes()
Dim shp As Shape

For Each shp In Sheets("Sheet1").Shapes
If shp.Type = msoFormControl Then
If shp.FormControlType = xlEditBox Then
shp.Delete
End If
End If
Next shp
End Sub

However, Norman's suggestion seems to work, too. I don't recall seeing it
done that way before, and it doesn't come up in intelisense, but it's
simpler and probably faster than looping.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
]

Deleting Shapes in a worksheet
 

You don't really need to count them:
Dim MyShape as Shape
For Each MyShape in Sheets("SheetName").Shapes
MyShape.Delete
Next MyShape

"Glen Mettler" wrote:

I have worksheets that contain 1 or more text boxes. I need to make copies
of these worksheets but then delete the text boxes.
I have looked at the shapes collection but I can't figure out how to
identify the number of text boxes, select them, and then delete them.

Need help.

Thanks
Glen

Deleting Shapes in a worksheet
 

I may have misinterpreted your question in my previous answer: Are there
also other shapes on the sheet (other than textboxes?). If so, you need to
find out what the shape is before deleting it:

Dim MyShape as Shape
For Each MyShape in Sheets("SheetName").Shapes
If MyShape.Type = msoTextBox Then MyShape.Delete
Next MyShape

"Glen Mettler" wrote:

I have worksheets that contain 1 or more text boxes. I need to make copies
of these worksheets but then delete the text boxes.
I have looked at the shapes collection but I can't figure out how to
identify the number of text boxes, select them, and then delete them.

Need help.

Thanks
Glen

Deleting Shapes in a worksheet
 

Norman Jones provided what I need.

ActiveSheet.TextBoxes.Delete

Soooo simple. Thanks

Glen

"Jake Marx" wrote in message
...
Hi Glen,

Glen Mettler wrote:
I have worksheets that contain 1 or more text boxes. I need to make
copies of these worksheets but then delete the text boxes.
I have looked at the shapes collection but I can't figure out how to
identify the number of text boxes, select them, and then delete them.

Something like this should work to remove all ActiveX TextBox controls
from a Worksheet:

Sub RemoveActiveXTextBoxes()
Dim ole As OLEObject

For Each ole In Sheets("Sheet1").OLEObjects
If TypeOf ole.Object Is MSForms.TextBox Then
ole.Delete
End If
Next ole
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Deleting Shapes in a worksheet
 

Hi Jake Marx,

Thanks very much. Its very simple and easy

Initially my file size was about 16 MB :mad: due to unwanted
textboxes. Now its reduced to 1.2 mb:).

Thanks again


--
sriram_rj
------------------------------------------------------------------------
sriram_rj's Profile: http://www.excelforum.com/member.php...o&userid=25097
View this thread: http://www.excelforum.com/showthread...hreadid=380150