[RESOLVED] Excel, (Shape) Label on sheet - caption changing

[Ecniv]

Hi,

How do I change the caption on a label that is on a sheet (not on a form) ?



I've tried:

shp <<< defined as a shape

    For lngS = 1 To ActiveWorkbook.Sheets.Count
        Set sht = ActiveWorkbook.Sheets(lngS)
        
        On Error Resume Next
        Set shp = sht.Shapes("lblLastMonth")
        shp.Caption = "(May 2012)" 'errors
        shp.TextFrame.Characters.Text = "(May 2012)" ' errors
        On Error GoTo 0
    Next

Any links or how to fix please? (I have the same label on multiple sheets, and want the code to flip through the sheets and change the caption on the label.

Recording a macro doesn't store (the property window must not be recorded).



Thanks in advance

[westconn1]

the shape contains an oleobject that contains the lable

try like

shp.oleformat.object.object.caption = "(May 2012)"

[Ecniv]

Darn, wish I'd popped here first...


Thanks westconn1

I had the thought that maybe it was embedding a userform and I could get to it via that.

But then I noticed the oleobject bit so I experimented with that and finally got to :

    Dim lngS As Long
    Dim sht As Worksheet
    Dim shp As Shape
    Dim objOle As OLEObject
    
    For lngS = 1 To ActiveWorkbook.Sheets.Count
        Set sht = ActiveWorkbook.Sheets(lngS)
        
        On Error Resume Next
        
        Set objOle = sht.OLEObjects("lbllastmonth")
        objOle.Object = "(May 2012)"
        
        On Error GoTo 0
    Next
    
    
    Set shp = Nothing
    Set objOle = Nothing
    Set sht = Nothing

This worked and now (as usual) I check here and the answer is waiting too lol.

Thanks for posting westconn

Edit:
Sorry - cannot add to your rep - says I need to give it to others before I can... :/ So :thumbsup: