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

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

Code:

---

    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

the shape contains an oleobject that contains the lable

try like

vb Code:

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

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


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 :

Code:

---

    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: :)