i have a sheet with over 100 labels on it. i need a statement that will set
all of their captions to blank. this is what i have, but it is not working.
can someone help

For Each Label in Sheets("PickSheet").Labels
Label.Caption = ""
Next

how can i rephrase this to make it work. TIA.

Sub test()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Type = msoFormControl Then
If shp.FormControlType = xlLabel Then
shp.TextFrame.Characters.Text = ""
End If
End If
Next
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Spencer Hutton" wrote in message
m...
i have a sheet with over 100 labels on it. i need a statement that will
set all of their captions to blank. this is what i have, but it is not
working. can someone help

For Each Label in Sheets("PickSheet").Labels
Label.Caption = ""
Next

how can i rephrase this to make it work. TIA.

that did not work, i copied the code exactly except i changed active sheet
to ("PickSheet") i did try it as ActiveSheet in the code for that sheet nd
it still did not work. protection is off, and ti tried it in design mode as
well as not.
"Rob van Gelder" wrote in message
...
Sub test()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Type = msoFormControl Then
If shp.FormControlType = xlLabel Then
shp.TextFrame.Characters.Text = ""
End If
End If
Next
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Spencer Hutton" wrote in message
m...
i have a sheet with over 100 labels on it. i need a statement that will
set all of their captions to blank. this is what i have, but it is not
working. can someone help

For Each Label in Sheets("PickSheet").Labels
Label.Caption = ""
Next

how can i rephrase this to make it work. TIA.

You didn't specify if your Labels are from the Forms or Controls toolbox
menu. But have a go with this:

Sub LabelsText()
Dim str As String
str = "Some text"
'str = ""

'Forms menu
'On Error Resume Next 'in case no labels on sheet
ActiveSheet.Labels.Text = str
On Error GoTo 0

'Controls toolbox
Dim ob As Object
For Each ob In ActiveSheet.OLEObjects
If TypeName(ob.Object) = "Label" Then
ob.Object.Caption = str
End If
Next
End Sub

Regards,
Peter

"Spencer Hutton" wrote in message
m...
i have a sheet with over 100 labels on it. i need a statement that will
set
all of their captions to blank. this is what i have, but it is not
working.
can someone help

For Each Label in Sheets("PickSheet").Labels
Label.Caption = ""
Next

how can i rephrase this to make it work. TIA.