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