views:

810

answers:

2

Hi,

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

+1  A: 
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
Geoffrey Van Wyk
Thanks for the reply! It works! But is there any way I can get the cell in which the check box is present?
Manoj
Not sure if you can find it directly. The checkbox has both a Left property and a Top property which represent its position. You could compare chk.Left with the Left property of each column in turn - ActiveSheet.Columns(1).Left etc - until you found the nearest match and then repeat for the Top property and each row in turn
barrowc
+1  A: 

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 = 1 Then '1 is true
        Set cell = chk.TopLeftCell

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

End Sub

Fink