Programming Checkboxes...

**Platem** · 07-05-2011, 03:41 PM

I have a checkbox at the end of each row in the sheet, e.g.:

For Each cell In Range("J5:J20")
    With Sheets(1).CheckBoxes.Add(cell.Left, cell.Top, 15, cell.Height)
        .LinkedCell = cell.Offset(, 0).Address(External:=True)
        .Interior.ColorIndex = xlClear
        .Caption = ""
    End With
Next

How do I assign an action to the boxes that will affect only the (Active?) row of that box, without having to write separate code for each individual checkbox? (For example: When a box is checked, the background color of the first cell in the row changes to red, and reverts if unchecked).

Thank you.

**tigeravatar** · 07-05-2011, 03:59 PM

Platem,

The method you showed adds Form Control checkboxes. You can assign the following macro to all of them. This macro simply turns the corresponding cell in column A red if checked, and clears the red if unchecked:

Sub Chk_Click()
    
    Dim chk As CheckBox: Set chk = ActiveSheet.CheckBoxes(Application.Caller)
    If chk.Value = xlOn Then
        Cells(chk.TopLeftCell.Row, 1).Interior.ColorIndex = 3
    Else
        Cells(chk.TopLeftCell.Row, 1).Interior.ColorIndex = 0
    End If
    
End Sub

Hope that helps,
~tigeravatar

**Platem** · 07-05-2011, 04:45 PM

Thanks tigeravatar.

How do I assign the macro automatically to each checkbox as it is created? My worksheet may have up to 200 checkboxes!

**tigeravatar** · 07-05-2011, 04:48 PM

Platem,

In the macro code you posted, add the following line after .Caption = "" and before End With:

    .Caption = ""
    .OnAction = "Chk_Click"
End With

Hope that helps,
~tigeravatar

**Platem** · 07-05-2011, 05:06 PM

I only had to change that to:

.OnAction = "ThisWorkbook.Chk_Click"

Again, thanks a million!

**Platem** · 07-06-2011, 04:36 PM

I've tried to move the above snippet into my project but it is causes this error:

Run-time error '1004': Unable to set the ColorIndex property of the Interior class

The following segment I place in the 'ThisWorkbook Workbook_Open()' module (should run only at start):

    .
    .
	 		
    'add some checkboxes
    For Each cell In Range("O3:O71,O77:O144")
       With Sheets(1).CheckBoxes.Add(cell.Left, cell.Top, 15, cell.Height)
           .LinkedCell = cell.Offset(, 0).Address(External:=True)
           .Interior.ColorIndex = xlClear
           .Caption = ""
           .OnAction = "Chk_Click"
       End With
    Next
    .
    .
    .

...and this segment appears in the Modules section (as Module3):

Sub Chk_Click()
    Dim chk As CheckBox: Set chk = ActiveSheet.CheckBoxes(Application.Caller)
    If chk.Value = xlOn Then
        Cells(chk.TopLeftCell.Row, 1).Interior.ColorIndex = 3
    Else
        Cells(chk.TopLeftCell.Row, 1).Interior.ColorIndex = 0
    End If
End Sub