Find Group Box and Iterate through controls

[mattmontero]

Hello all!
I hope I'm posting in the correct section, and that there are no duplicate questions. This is my first post, and I've been searching for the past hour to find this answer.

I have been programming for 4 years now but I'm new to VBA. I'm trying to make a simple interface of options.
In step 1 there will be a group box (Group Box 1) of 2 buttons (Button 1 and Button 2). There is also another group box (Group Box 2) which contains 3 more buttons (Buttons 3, 4 and 5).
If Button 1 is clicked, then I want to show Buttons 3 and 4. If Button 2 is clicked, I want to show Buttons 4 and 5.

Currently I have it set up where, if a button from Group Box 1 is clicked, the entire Group Box 2 shows.

With that being said, my question is how can I iterate through all the buttons within Group Box 2, that way I can select which buttons to show or hide?

Even more general, how can I iterate through all group boxes in my worksheet?

I'm not sure if the version of Excel and VBA makes a difference, but I am working in MS Office 16

Thanks for the help!

 

[Domenic]

First, while it's possible to iterate through group boxes, there doesn't seem to be a way of referring to the controls within the group box. Nevertheless, in case you're curious, one would iterate through group boxes as follows...

    Dim oGroupBox As GroupBox
    For Each oGroupBox In Worksheets("Sheet1").GroupBoxes
        Debug.Print oGroupBox.Name
    Next oGroupBox

So, alternatively, place the following code in the code module for the sheet containing the buttons (right-click the sheet tab, and select View Code). Then assign the macro to Button 1 and Button 2. In assigning the macro, though, you'll need to qualify the reference with the code name for the sheet. So, for example, if the code name for your sheet is Sheet1, the macro name used to assign the macro to the buttons would be Sheet1.UpdateButtons.

Private Sub UpdateButtons()
    Select Case Application.Caller
        Case "Button 1"
            Me.Shapes.Range(Array("Button 3", "Button 4")).Visible = True
            Me.Shapes("Button 5").Visible = False
        Case "Button 2"
            Me.Shapes("Button 3").Visible = False
            Me.Shapes.Range(Array("Button 4", "Button 5")).Visible = True
    End Select
End Sub

Hope this helps!

 

[mikerickson]

If we are talking about a GroupBox from the forms menu, then it doesn't contain other controls. There may be other controls within its boundaries, but they aren't "in" it. One can move the Group box and the other controls will stay put. One has to test the position of each control to see if it is inside the Group Box.

Dim oneShape As Shape
Dim groupShape As Shape

Set groupShape = ActiveSheet.Shapes("Group Box 1")
For Each oneShape In ActiveSheet.Shapes
    If oneShape.Name <> groupShape.Name Then
        With oneShape
            If groupShape.Top < .Top And .Top < groupShape.Top + groupShape.Height Then
                If groupShape.Left < .Left And .Left < groupShape.Left + groupShape.Width Then
                    MsgBox oneShape.Name
                End If
            End If
        End With
    End If
Next oneShape

If we are talking about controls that have been Grouped (select many objects and the Group via that Format tab) then that is a different scenario.

Dim oneShape As Shape
Dim groupShape As Shape

Set groupShape = ActiveSheet.Shapes("Group 1")

For Each oneShape In groupShape.GroupItems
    MsgBox oneShape.Name
Next oneShape

 

[Domenic]

Nice one Mike!

 

[mattmontero]

Thank you both for your answer! Domenic iterating through all the group boxes is something that I will also be needing so much appreciated! And Mike, thanks for clarifying the difference between grouping and group box. I did mean grouping, though I am using group boxes. Grouping seems to be much cleaner. Thanks a bunch!