Can VBA interact with check boxes that were placed without VBA-CodePudding

I am trying to write a macro that will scrape data from a huge set of already existing excel files. These files have the same format just with different info. I am able to pull any data written in a cell, even if it's in the form of a drop down. However, since checkboxes are not tied to a cell range, I'm not sure how to interact with them in VBA.

I've tried using

? Activesheet.checkbox1.value
? Activesheet.checkbox(1).value

but it's not giving me anything.

Is there a way to reference these? I'm guessing they were placed using "developer tab -> insert objects" so there is no userform area in the project tree in VBA

Thanks,

CodePudding user response：

Try this code to find checkboxes on the worksheet:

Sub InspectCheckBoxes()
    Dim sh As Shape
    
    For Each sh In ActiveSheet.Shapes
        Select Case sh.Type ' see MsoShapeType enumeration
            Case msoFormControl
                If sh.FormControlType = xlCheckBox Then ' see XlFormControl enumeration
                    Debug.Print "Found CheckBox as FormControl named '" & sh.Name & "' at left "; sh.Left & "; top " & sh.Top
                End If
            Case msoOLEControlObject
                If InStr(1, sh.OLEFormat.progID, "CheckBox", vbTextCompare) > 0 Then
                    Debug.Print "Found CheckBox as OLEControlObject named '" & sh.Name & "' at left "; sh.Left & "; top " & sh.Top
                End If
        End Select
    Next
End Sub

Prints:

Found CheckBox as FormControl named 'Check Box 2' at left 207; top 78
Found CheckBox as OLEControlObject named 'CheckBox1' at left 244,5; top 142,5

CodePudding user response：

You didn't specify which type of checkboxes...

Dim cbx As Excel.CheckBox
Dim msfCbx As msforms.CheckBox
Dim ole As OLEObject

Debug.Print "Forms type checkboxes"
For Each cbx In ActiveSheet.CheckBoxes
    Debug.Print cbx.Value, cbx.Name, cbx.Caption, cbx.TopLeftCell.Address
Next

Debug.Print "ActiveX checkboxes"
For Each ole In ActiveSheet.OLEObjects
    If ole.progID = "Forms.CheckBox.1" Then
        Set msfCbx = ole.Object
        Debug.Print msfCbx.Value, ole.Name, msfCbx.Caption, ole.TopLeftCell.Address
    End If
Next