mercredi 24 août 2016

Excel VBA: GroupName property from grouped ActiveX Checkbox in Worksheet

I am trying to loop through a ActiveX checkboxes that are grouped together in a frame on a worksheet. I have been able to find all the checkboxes, but I am not able to get the GroupName properties through the VBA code. To figure out the script I have been just using a simple workbook that has two checkboxes grouped in a frame that are simply named Checkbox1 and Checkbox2 and they have the same GroupName. This is what I have so far

    Sub test2()
    Dim i As Integer
    Dim cb As Object
    Dim countItems As Integer
    Dim checkBox As Object

    For Each cb In ActiveSheet.Shapes
        If cb.Name Like "Group*" Then
    countItems = cb.GroupItems.Count
    For i = 1 To countItems
                If cb.GroupItems(i).Name Like "Check*" Then
                    Debug.Print cb.GroupItems(i).Name
                End If
            Next i
        End If
    Next cb

    End Sub

I have been searching around the internet for solutions, but the ones that I have seen do not seem to fit because my checkboxes are grouped together.

    Sub test4()
        Dim ole As OLEObject

        For Each ole In ActiveSheet.OLEObjects
            If TypeName(ole.Object) = "CheckBox" Then
                Debug.Print ole.Object.GroupName
                If ole.Object.GroupName = Group And ole.Object.Value = True Then
                    Debug.Print ole.Object.GroupName
                End If
            End If
        Next ole
        GroupClear = True

    End Sub

This seemed to work to find the checkboxes that were in the worksheet just fine but not grouped together.

Thank you for the help




Aucun commentaire:

Enregistrer un commentaire