Get Values of all Check Boxes in Excel using VBA

Question

I add many check boxes to the excel sheet programaticaly using the following code:

With ActiveSheet.CheckBoxes.Add(rCell.Left, rCell.Top, rCell.Width, rCell.Height)
        .Interior.ColorIndex = xlNone
        .Caption = ""
End With

Now I need a code which would parse through all the Check boxes that are present in the sheet and get their value(true or false) and also the cell at which they are present. How to do this?

Thanks...

Answer 1

Sub Add_CheckBoxes()

With ActiveSheet.CheckBoxes.Add(ActiveCell.Left, ActiveCell.Top, ActiveCell.Width, ActiveCell.Height)
    .Interior.ColorIndex = xlNone
    .Caption = ""
End With

For Each chk In ActiveSheet.CheckBoxes
    If chk Then MsgBox "Checked"
Next
End Sub

Answer 2

Once you add the checkboxes you can loop through them like this:

Sub Checkbox_Test()

Dim chk As CheckBox
Dim cell As Range

For Each chk In ActiveSheet.CheckBoxes
    If chk.Value = Checked Then        '1 is true, but the constant
        Set cell = chk.TopLeftCell     ' "Checked" avoids "magic numbers".

        'do whatever with cell
        MsgBox cell.Address
    End If
Next chk

End Sub