View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
SteveM SteveM is offline
external usenet poster
 
Posts: 133
Default 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