PDA

View Full Version : [SOLVED] The proper way



Airborne
11-14-2004, 08:32 AM
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:blush .

Zack Barresse
11-14-2004, 09:19 AM
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
11-14-2004, 09:54 AM
:yes Thanks firefytr.

Airborne
11-14-2004, 10:01 AM
:wot Sorry firefytr, I was to quick. I get run-time error 438, object doesn't support this property or method.

mdmackillop
11-14-2004, 10:44 AM
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
11-14-2004, 11:06 AM
:yes :thumb thanks mdmackillop. Changed Dim sh to Dim tb and all textboxes were cleared.


Regards.

mdmackillop
11-14-2004, 11:15 AM
Oops!

Code changed for future use.

Thanks, MD

Airborne
11-14-2004, 11:36 AM
No problem, I have to keep thinking for myself too. It was the code that helped me:) .


Regards.