Clear textbox along with cell contents

[RonNCmale]

This works clearing the cell contents but I have a textbox called textbox631 on all worksheets and need to add code to clear this also.


Sub Clear()
On Error Resume Next
With Worksheets("wk1")
.Unprotect
.Range("C6:I16,B19,A20,A21,C27").SpecialCells(xlCellTypeConstants).ClearContents
End With

see attached example

[Kevin#]

Hi Ron
Try this.
This assumes that there is a Text Box 631 in each sheet in the range.




Sub ClearTextBox()
For i = 2 To 5
Sheets(i).Shapes("Text Box 631").TextFrame.Characters.Text = ""
Next i
end sub

[SamT]

There are many so named TextBoxes on each sheet except Sheets(Sheet1.Name)

[Kevin#]

@RonNCmale - I will have another look at this (tomorrow) to take account of @SamT's helpful comment.

[Kevin#]

This takes account of multiple textboxes (in same sheet) with same name



Sub ClearText_Box_631()
For i = 2 To ThisWorkbook.Worksheets.Count
For Each shp In Sheets(i).Shapes
If shp.Name = "Text Box 631" Then shp.TextFrame.Characters.Text = ""
Next
Next i
End Sub

[RonNCmale]

Thanks Kevin# for your solution. Works great.

[snb]

or

Sub M_snb()
For Each sh In Sheets
For Each it In sh.Shapes
If it.Type = 17 Then it.TextEffect.Text = ""
Next
Next
End Sub

[Kevin#]

@snb - so does every shape type=17 have the same name (within the same workbook)? Does this apply to other "types"

[snb]

In the given example all textboxes (type=17) should be 'cleared'. So there is no need to check any name. Only the check for checkboxes (type=8) is required.