How do I get rid of all TextBoxes in Excel via VBA (empty)

[madocar]

Hello,

please is there any way how do I get rid of all TextBoxes in active sheet that are EMPTY via VBA?
There's a ton of them and my Excel got frozen.

Thank you for any help

 

[Monty]

Hello Madocor

You can use the following VBA code to delete all empty TextBoxes on the active sheet:

Sub RemoveEmptyTextBoxes()
    Dim shp As Shape
    
    ' Loop through all shapes in the active sheet
    For Each shp In ActiveSheet.Shapes
        ' Check if the shape is a TextBox and if its text is empty
        If shp.Type = msoTextBox And shp.TextFrame.Characters.Text = "" Then
            ' Delete the TextBox
            shp.Delete
        End If
    Next shp
End Sub

 

[madocar]

Hello @Monty

thank you for your quick answer.
Now I get this error:





Is there any way how to fix this?

Thank you very much Monty in advance

 

[Marc L]

Hello, according to your guessing challenge maybe via splitting the If in two Ifs …​

​

 

[madocar]

so instead of AND use two IF functions?
how would it look like the whole code?

thank you in advance

 

[Belleke]

Try

Sub belle()
Dim oTextBox As TextBox
For Each oTextBox In ActiveSheet.TextBoxes
    If oTextBox.Characters.Count = 0 Then oTextBox.Delete
Next oTextBox
End Sub

 

[p45cal]

so instead of AND use two IF functions?
how would it look like the whole code?

  If shp.Type = msoTextBox Then
    If shp.TextFrame.Characters.Text = "" Then
      ' Delete the TextBox:
      shp.Delete
    End If
  End If

 

[madocar]

Thank you very much, this helped me a lot!!

@p45cal
@Belleke