Vbscript get all sheet objects and find by part of ID

[Anonymous]

Dear Community:

In each sheet, I need to find object based on part of ID. E.g. in each sheet, I will have a text object with ID like "HEADER_XX". I intend to assign a trigger on every sheet activation, which will change the text of the object to the name of the sheet.

What I have in mind (mock-up code):

set s = ActiveDocument.ActiveSheet
title = s.GetProperties.Name

for i = 1 to ActiveDocument.ActiveSheet.SheetObjects.Count '<< NOT WORKING OF COURSE

     if find(ActiveDocument.GetSheetObject(i).Name, "HEADER") then

          set mytext = s.SheetObjects("TX30")

            mytext.SetText(title)

     end if

next i

What I have now is:

Sub ChangeTitle()

set s = ActiveDocument.ActiveSheet
title = s.GetProperties.Name

' text object in the sheet is hard-coded
set mytext = s.SheetObjects("TX30")      ' this to be defined dynamically
                                                                      ' e.g. find(ObjectID, "HEADER") <> 0
mytext.SetText(title)

End Sub

Sub ChangeTitle()

set s = ActiveDocument.ActiveSheet
title = s.GetProperties.Name

' text object in the sheet is hard-coded
set mytext = s.SheetObjects("TX30")      ' this to be defined dynamically
                                        ' e.g. find(ObjectID, "HEADER") <> 0
mytext.SetText(title)

End Sub

[Anonymous]

UPD: This is NoOfSheetObjects

Took some effort to find it in the QV API reference:

move all objects on active sheet 60 docunits down and 45 right

set sh = ActiveDocument.ActiveSheet

for i = 0 to sh.NoOfSheetObjects-1

    set obj = sh.SheetObjects(i)

    set fr = obj.GetFrameDef

    pos = fr.Rect

    pos.Top = pos.Top + 60

    pos.Left = pos.Left + 45

    obj.SetFrameDef fr

next

[marcus_sommer]

I think this code-snippet will be helpful for you to loop through all sheets and all sheet-objects:

sub x

for i = 0 to ActiveDocument.NoOfSheets - 1

    set s = ActiveDocument.GetSheet(i).GetProperties

    set t = ActiveDocument.GetSheet(i)

    'msgbox(s.name)

    t.Activate

    Objects = ActiveDocument.ActiveSheet.GetSheetObjects

    For j = lBound(Objects) To uBound(Objects)

        set obj = Objects(j)

          if obj.GetObjectType = 6 then          'text boxes

              str = str & obj.GetObjectid & chr(10)

          end if

    next

next

'msgbox str

end sub

But I think you didn't need it for your goal to add the sheet to the caption from a textbox. For this you could use:

= 'Header ' & getactivesheetid()

- Marcus