How do I display textboxes without any content in a spreadsheet?

L

Leo

I have an Excel workbook that is extremely slow in opening up and saving. It
freezes up when I try to resize it and there is a time-lag when I scroll
within certain worksheets. There are about 7 sheets within the workbook.
Now, it came to my notice that some of the spreadsheets have many text boxes
without any content in them. As a result, they are invisible until you click
on them.

My question is: Is there a way where I can have the spreadsheet show all the
text boxes or a way that I could just delete all of them at once?

Or if you think that there might be another alternative please advise.

Thanks,
Leo
 
D

Dave Peterson

There's a textbox on the Drawing toolbar and a textbox on the Control toolbox
toolbar.

I think I'd delete the textboxes that are empty.

Option Explicit
Sub testme()
Dim TBox As TextBox
Dim OLEObj As OLEObject
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
For Each TBox In wks.TextBoxes
If Trim(TBox.Caption) = "" Then
TBox.Delete
End If
Next TBox

For Each OLEObj In wks.OLEObjects
If TypeOf OLEObj.Object Is MSForms.TextBox Then
If Trim(OLEObj.Object.Text) = "" Then
OLEObj.Delete
End If
End If
Next OLEObj
Next wks

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top