Updating Properties of a Control on a Worksheet
 

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

Updating Properties of a Control on a Worksheet
 

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

Updating Properties of a Control on a Worksheet
 

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

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