clear captions from all labels

  • Thread starter Thread starter Spencer Hutton
  • Start date Start date
S

Spencer Hutton

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
 
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.
 
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
 
Back
Top