Updating Properties of a Control on a Worksheet - THANK YOU DAVEPETERSON!
 

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

Updating Properties of a Control on a Worksheet - THANK YOU DAVE PETERSON!
 

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

Updating Properties of a Control on a Worksheet - THANK YOU DAVEPETERSON!
 

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

Updating Properties of a Control on a Worksheet - THANK YOU DAVE PETERSON!
 

It is still the case that you control (object) is now activeX, you
previously indicated it was a from control part of the shapes collection
these are different. My proposal for a shape is still true and valid! I
think you might find this definition helpful.

http://www.dailydoseofexcel.com/arch...ivex-vs-forms/

Cheers

--

Regards,
Nigel




"SteveM" wrote in message
...
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