looping through checkboxes

Question

Hi All,

I'd like to loop through checkboxes in a sheet. I know how to loop through OLEObjects and check if TypeName(OLEObject) = "CheckBox" but I wonder if a specific collection, something like ActiveSheet.OLEObjects("CheckBoxes") exist? It would seem reasonable!

Thanks,

Stefi

Answer 1

You can also use a simple function to build your own collection.

Andreas.

Sub Test()

  Dim CB As MSForms.CheckBox

  For Each CB In OLECheckBoxes

    If MsgBox(CB.Name & ": " & CB.Caption, vbOKCancel) = vbCancel Then Exit Sub

  Next

End Sub

Function OLECheckBoxes(Optional ByVal Sh As Object) As Collection

  Dim OO As OLEObject

  Set OLECheckBoxes = New Collection

  If Sh Is Nothing Then Set Sh = ActiveSheet

  If Sh Is Nothing Then Exit Function

  For Each OO In Sh.OLEObjects

    If OO.OLEType = xlOLEControl Then

      If StrComp(OO.progID, "Forms.CheckBox.1", vbTextCompare) = 0 Then

        OLECheckBoxes.Add OO.Object

      End If

    End If

  Next

End Function

Answer 2

Hello Stefi,

AFAIK you can't do that with ActiveX controls. Like you have said you need to loop through the OLEObjects and identify type. However, the following works with Form controls.

Dim chkBox As CheckBox

For Each chkBox In ActiveSheet.CheckBoxes

    MsgBox chkBox.Name

Next chkBox

Also the syntax for looping through the OLEObjects is not quite as you posted.

    Dim oleObjCtrl As OLEObject

    For Each oleObjCtrl In ActiveSheet.OLEObjects

        If TypeName(oleObjCtrl.Object) = "CheckBox" Then

            MsgBox oleObjCtrl.Name

        End If

    Next oleObjCtrl

Answer 3

Thanks to both of you!

Stefi