Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / January 2008

Tip: Looking for answers? Try searching our database.

Updating Properties of a Control on a Worksheet

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
SteveM - 28 Jan 2008 15:59 GMT
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 - 28 Jan 2008 16:14 GMT
shp.TextFrame.Characters.Text = "TestText"

Signature

Regards,
Nigel
nigelnospam@9sw.co.uk

>I have some Label controls placed on a Worksheet.  I want to update
> the captions using the outputs from an optimization model.  However it
[quoted text clipped - 25 lines]
>
> End Sub
SteveM - 28 Jan 2008 17:54 GMT
> shp.TextFrame.Characters.Text = "TestText"
>
[quoted text clipped - 33 lines]
>
> > 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
Nigel - 28 Jan 2008 18:19 GMT
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

Signature

Regards,
Nigel
nigelnospam@9sw.co.uk

>> shp.TextFrame.Characters.Text = "TestText"
>>
[quoted text clipped - 58 lines]
>
> SteveM
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.