On Jan 28, 1:39 pm, "Nigel" wrote:
See my reply to your earlier post, your OP stated you were using form
(shape) objects! OLEObjects need to be treated differently. Glad it is now
sorted.

--

Regards,
Nigel


"SteveM" wrote in message

...

Nigel, et al.

Thanks Nigel for the ideas, but they did not seem to work. I did a
search on msoOLEControlObject in this group and came across something
Dave Peterson contributed back in 2001. The syntax is a little more
complicated but it works. Here are the guts:

Sub GetControls()
Dim shp As Shape
Dim ws As Worksheet, str As String

Set ws = Worksheets("Scenario Map")

For Each shp In ws.Shapes
If shp.Type = msoOLEControlObject Then
If TypeOf shp.OLEFormat.Object.Object Is MSForms.Label
Then
shp.OLEFormat.Object.Object.Caption = "Label Test"
End If
Next
End Sub

Nigel,

I went back to see what you wrote. I had already tried that but it
did not work. Here's a slight mod to your simple but effective
approach that does:

Sub GetControls()
Dim shp As Shape
Dim ws As Worksheet, str As String
Dim ole As OLEObject

Set ws = Worksheets("Scenario Map")
Set ole = ws.OLEObjects("Label01")
ole.Object.Caption = "Label Text"

Again, I'm a hacker. Using the Range analogy I tried:

ws.OLEObjects("Label01").Caption = "Label Text"

But that did not work either so I stepped away. Why the syntax works
with the ole variable is beyond me.

Again, I really appreciate the effort you and the others make to
iterate through these online queries.

Regards,

SteveM