Thread
:
Updating Properties of a Control on a Worksheet
View Single Post
#
4
Posted to microsoft.public.excel.programming
Nigel[_2_]
external usenet poster
Posts: 735
Updating Properties of a Control on a Worksheet
MsgBox shp.TextFrame.Characters.Text
Works for me if you using worksheet form control labels.
For activeX objects you can get the caption as you would on a UserForm
Sheets("Sheet1").Label1.Caption
--
Regards,
Nigel
"SteveM" wrote in message
...
On Jan 28, 11:14 am, "Nigel" wrote:
shp.TextFrame.Characters.Text = "TestText"
--
Regards,
Nigel
"SteveM" wrote in message
...
I have some Label controls placed on a Worksheet. I want to update
the captions using the outputs from an optimization model. However it
looks like the method is different that updating the same control on a
form. I realize that Worksheet controls are managed in the Shapes
collection. I created a test sub to cycle through the Sheet Label
Shapes and tell me their Captions. The Caption property obviously
does not work. Any help on how to update a Label Caption on a
worksheet? I.e. just not see it, but change it.
Thanks Much,
SteveM
Sub GetControls()
Dim shp As Shape
Dim ws As Worksheet
Set ws = Worksheets("Scenario Map")
For Each shp In ws.Shapes
If shp.Type = msoFormControl Then
If shp.FormControlType = xlLabel Then
MsgBox shp.????? ' !!! need Caption
equivalent here
End If
End If
Next
End Sub
Nigel,
Thanks for the info, but...
It didn't work. Since they are Label control Objects I tried:
shp.TextFrame.Characters.Caption = "TestText"
That didn't work either.
I then essentially did a semi-random walk though the Object Classes
and declaring shp as Type OLEObject and cycling through the OLE
Objects in the workbook returned the Label names. Experimentation led
me to:
MsgBox shp.Object
Which actually spit out the labels. However the Object property is
read only. So I'm still stuck.
Any other ideas? (BTW, would it be easier to delete the Label
controls and replace them with Text Box drawing objects?
SteveM
Reply With Quote
Nigel[_2_]
View Public Profile
Find all posts by Nigel[_2_]