How to set the caption of a label

[Steve Sacks]

I've got several labels on an Excel worksheet. I'd like
to set their captions with VBA code, but Excel treats them
as shapes and shapes don't have a caption property. When
I try myLabel.caption or shapes("Label1").caption I get an
error saying this object doesn't have that property.
Can someone tell me how to reference the caption property
of a some labels ?
Thanks.

[Dave Peterson]

I could get to it in a couple of ways:

Dim oleObj As OLEObject
For Each oleObj In ActiveSheet.OLEObjects
If TypeOf oleObj.Object Is msforms.Label Then
oleObj.Object.Caption = "hi"
End If
Next oleObj

or

Dim myShape As Shape
Set myShape = ActiveSheet.Shapes("label1")
myShape.OLEFormat.Object.Object = "hi there"

or maybe the easiest:

Worksheets("sheet1").Label1.Caption = "bye there"

--

Dave Peterson
ec3...@msn.com

[John Green]

If you are talking about a label created with the Forms toolbar, it is a
Label object and you can set its caption as follows:

ActiveSheet.Labels(1).Caption = "myCaption"

or

ActiveSheet.Labels("Label 1").Caption = "myCaption"

If you are talking about a label created using the Control Toolbox toolbar,
use the following:

ActiveSheet.OLEObjects(1).Object.Caption = "myCaption"

or

ActiveSheet.OLEObjects("Label1").Object.Caption = "myCaption"

--

John Green - Excel MVP
Sydney
Australia

"Steve Sacks" <Sa...@uconn.edu> wrote in message
news:b34101c2c0ff$1315bb60$8bf82ecf@TK2MSFTNGXA05...

[Stephen Sacks]

Many thanks to Dave Peterson and John Green for solving my problem. I
now know a lot more about forms, controls, and oleObjects.
Thanks !

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!