Clear Captions from labels

[Spencer Hutton]

i posted a question last night about this and the answer was not as
effective as i would have liked. i have a sheet with ove 100 labels on it
and i am trying to clear all of their captions wih a loop. someone gave me
this code:

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

which did not do what i expected. can anyone offer anything further? see
post on 11/20 @ 12:00 AM
Thank you.

 

[smcpoland]

which did not do what i expected

Don't get me wrong but when you say something like this it does no
help anyone help you as they don't understand what you mean.

The solution as presented - did it work or did it not, and if not ca
you explain what went wrong.

I have a feeling it did do what you wanted it to do (which is clear th
labels) but it did more than what you thought it would, therefor
disrupting your spreadsheet, rather than just clearing what yo
actually wanted to specify.

Or did you really mean - Remove the labels - which is not the same a
clearing.

Sorry about the questions but you must be more specific when sayin
that it doesn't do what you want....no one is a mind reader and we ar
not in your head....

regards
Sea

 

[Spencer Hutton]

I understand what you mean now that i am reading what i posted. what i
meant by "not what i expected" is that it did not clear the captions from
the labels. i didn't want to remove the labels from my worksheet, i just
wanted their captions to be blank, so that they could later be
filled/changed by the outcome of a formula / code.
thank you.

 

[Spencer Hutton]

it is not a label from the Forms toolbar, it is from the Control Toolbox.
when i tried this code with the Forms type of label, the code worked. How
can i make it work with the other type of label. what is the difference?

 

[Bob Phillips]

Sub ClearLabels()
Dim i As Long

For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "Label" Then
ActiveSheet.OLEObjects(i).Object.Caption = ""
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Rob van Gelder]

Sorry Spencer - I didn't realise you were using the Control Toolbox.
I'd give you another solution but Bob (and others?) have an supplied
excellent solution already.