Find Group Box and Iterate through controls

mattmontero

New Member
Joined
Sep 29, 2016
Messages
2
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,008
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...

Code:
    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.

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

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,792
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.

Code:
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.
Code:
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
 

mattmontero

New Member
Joined
Sep 29, 2016
Messages
2
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!
 

Forum statistics

Threads
1,082,017
Messages
5,362,695
Members
400,686
Latest member
Aakash

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top