Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating Properties of a Control on a Worksheet | Excel Programming | |||
How to control chart properties from worksheet | Excel Discussion (Misc queries) | |||
How to control chart properties from worksheet | Charts and Charting in Excel | |||
Updating range properties is so sloooow!!! | Excel Programming | |||
Active X not updating properties on Excel Sheet | Excel Programming |