Friday, January 08, 2010

Excel - Show empty text boxes

I've recently had some trouble with Excel sheets containing empty, but spurious text boxes. They would show up as unwanted random lines disrupting output. Copy and paste the code below into the VBA editor, it will make each of the text boxes 20x20 pixels which should make them big enough to spot and remove.

Sub ShowEmptyTextBoxes()
Dim shp As Shape
Dim sLoc As String
For Each shp In ActiveSheet.Shapes
With shp
If (.Type = msoTextBox) Then
If .Width < 2 Then
.Width = 20
.Height = 20
' .Delete
End If
End If
End With
End Sub

If you want, you could also just delete them... uncomment out the .Delete statement by removing the leading ' mark.

No comments: