How to Iterate over Controls on a Sheet

[Chucko1]

I have a Calc spreadsheet on which I have used Form Design mode to add several Push-Button controls to each of two sheets. The buttons initiate Basic macro code that resides in the Standard Module of the spreadsheet itself (i.e. NOT in the My Macros Module.) I want the macro code initiated by a button-click on Sheet-1 to be able to iterate over all the Controls on Sheet-2 to find a specific button Control so I can manipulate its properties.

Using the oEvent passed to the macro by a button-click on Sheet-1, I can manipulate the properties of the clicked button:

oSource = oEvent.Source
oModel = oSource.getModel()
oModel.(Property) = value

And I can manipulate any/all Controls on the same Sheet (in the same Container as the oEvent?) using:

For Each control In oSource.Context.Controls
     control.Model.(Property) = (Value)
Next

But I want to manipulate the properties of the Controls that reside on Sheet-2. I have been unable to find a way to locate and access the Controls on a sheet different from the one on which the button-click oEvent occurred. Note that the Controls reside directly on the Sheets and NOT on a Dialog Form.

Edit #1

After further research, I discovered that

oSheet = ThisComponent.Sheets.getByName("SheetName")
oButton = oSheet.DrawPage.Forms.getByIndex(0).getByName("ButtonName")
oButton.(Property) = (Value)

will allow me to manipulate the properties of any Control on any Sheet provided I know the Name of the Sheet and the Name of the Control I want to manipulate. In my case, I know the required names, so I can use this technique as a “workaround.”

While this allows me to do what I wanted, it does not answer my original question, so I’m leaving the question un-answered for a bit longer to see if anyone can provide an answer.

[Ratslinger]

Hello,

You can just loop through sheets, forms on sheets and controls on forms until you obtain what you want. Of course you may have controls on different sheets with the same name. Care must be taken in this regard.

Sub GetSheets
    Dim oSheet        As Object
    Dim oForm         As Object
    Dim oForms         As Object
    Dim oControl      As Object
    Dim x             As Integer
    Dim y             As Integer
    Dim z             As Integer
    Dim iSheetCount   As Integer
    Dim iFormsCount As Integer
    Dim iControlCount As Integer
    iSheetCount = ThisComponent.Sheets.Count
    For x = 0 to iSheetCount -1
        oSheet = ThisComponent.Sheets.getByIndex(x)
        oForm = oSheet.DrawPage.Forms
        If oForm.Count > 0 Then
            For y = 0 to oForm.Count - 1
                oForms = oSheet.DrawPage.Forms.getByIndex(y)
                If oForms.Count > 0 Then
                    For z = 0 to oForms.Count -1
                        oControl = oForms.getByIndex(z)
                        MsgBox oControl.Name
                    Next z
                End If
            Next y
        End If
    Next x
End Sub

[Chucko1]

Excellent idea that combines my “workaround” with my initial idea. In hindsight, it was obvious. So many times I’m struck by how hard it is to see the obvious answer after you get stuck driving own a dead-end path. But that’s why I LOVE these forums. They force you to re-look at problems. Thank you for slapping me up-side my head and opening my eyes.

Note that, in my case, there are ZERO Forms on the Sheet. So, the above code probably should be adapted to deal with the case of “oForm.Count = 0”

[Ratslinger]

@Chucko1,

The solution does consider a sheet having no forms. The IF statements consider this by checking if # is greater than 0. Sheets are not checked for this since there is at least one sheet in the document.

Edit:

Your code in the question:

oButton = oSheet.DrawPage.Forms.getByIndex(0).getByName("ButtonName")

does indicate at least some of your sheets have forms. As stated though, the code already accounts for this.