The proper way

[Airborne]

Hi, I have a macro that clears all my textboxes on sheet "Data". It looks like this

Sub ClearText()
ActiveSheet.Shapes("Text 5").Select
Selection.Characters.Text = ""
ActiveSheet.Shapes("Text 22").Select
Selection.Characters.Text = ""
ActiveSheet.Shapes("Text 23").Select
Selection.Characters.Text = ""
ActiveSheet.Shapes("Text 24").Select
Selection.Characters.Text = ""
ActiveSheet.Shapes("Text 25").Select
Selection.Characters.Text = ""
ActiveSheet.Shapes("Text 26").Select
Selection.Characters.Text = ""
Range("A8").Select
End Sub

My question is...is this the proper way or is there a shorter way?

I know I should take the course and believe me I'm saving for it .

[Zack Barresse]

Hi,

No need for all the selections ...

Sub ClearText() 
ActiveSheet.Shapes("Text 5").Characters.Text = ""
    ActiveSheet.Shapes("Text 22").Characters.Text = ""
    ActiveSheet.Shapes("Text 23").Characters.Text = ""
    ActiveSheet.Shapes("Text 24").Characters.Text = ""
    ActiveSheet.Shapes("Text 25").Characters.Text = ""
    ActiveSheet.Shapes("Text 26").Characters.Text = ""
End Sub

[Airborne]

Thanks firefytr.

[Airborne]

Sorry firefytr, I was to quick. I get run-time error 438, object doesn't support this property or method.

[mdmackillop]

Hi Airborne, try

Sub ClearTB()
Dim tb
With ActiveSheet
For Each tb In .Shapes
If tb.Type = 17 Then
tb.TextFrame.Characters.Text = ""
End If
Next
End With
End Sub

[Airborne]

thanks mdmackillop. Changed Dim sh to Dim tb and all textboxes were cleared.


Regards.

[mdmackillop]

Oops!

Code changed for future use.

Thanks, MD

[Airborne]

No problem, I have to keep thinking for myself too. It was the code that helped me .


Regards.