Uncheck multiple checkboxes (form) on a row

[Jeremic]

Im working on a project checklist. When creating new projects, I would like to insert a new row on row 10. Copy the checkboxes from row 11 below, link and set the checkbox functions to work seperatly...so far so good.
However, now I would like to reset all the "newly" created checkboxes to unchecked or "xloff" for all checkboxes on row 10. The range where checkboxes are located is L10:Y10.

As I will constantly create new checkboxes on row 10, I suppose I need class, event, msform?? Im just guessing after a few days of research....Im not really getting anywhere and I feel clueless
Appreciate all your help.
My code so far looks like below.

Option Explicit
Sub LinkCheckBoxes()
Dim chk As CheckBox
Dim lCol As Long
lCol = 62 'number of columns to the right for link
For Each chk In ActiveSheet.CheckBoxes
   With chk
      .LinkedCell = _
         .TopLeftCell.Offset(0, lCol).Address
  End With
Next chk
End Sub
Sub CheckBoxDate()
Dim ws As Worksheet
Dim chk As CheckBox
Dim lColD As Long
Dim lColChk As Long
Dim lRow As Long
Dim rngD As Range
lColD = 84 'number of columns to the right for date
Set ws = ActiveSheet
Set chk = ws.CheckBoxes(Application.Caller)
lRow = chk.TopLeftCell.Row
lColChk = chk.TopLeftCell.Column
Set rngD = ws.Cells(lRow, lColChk + lColD)
Select Case chk.Value
       Case 1   'box is checked
      rngD.Value = Date
   Case Else   'box is not checked
      rngD.ClearContents
End Select
End Sub
Sub CheckBoxDate()
Dim ws As Worksheet
Dim chk As CheckBox
Dim lColD As Long
Dim lColChk As Long
Dim lRow As Long
Dim rngD As Range
lColD = 84 'number of columns to the right for date
Set ws = ActiveSheet
Set chk = ws.CheckBoxes(Application.Caller)
lRow = chk.TopLeftCell.Row
lColChk = chk.TopLeftCell.Column
Set rngD = ws.Cells(lRow, lColChk + lColD)
Select Case chk.Value
       Case 1   'box is checked
      rngD.Value = Date
   Case Else   'box is not checked
      rngD.ClearContents
End Select
End Sub

Sub SetCheckBoxesMacro()
Dim chk As CheckBox
For Each chk In ActiveSheet.CheckBoxes
   chk.OnAction = "CheckBoxDate"
Next chk
End Sub

 

[Andrew Poulsom]

Welcome to MrExcel.

Does this work for you?

Sub Test()
    Dim chk As CheckBox
    For Each chk In ActiveSheet.CheckBoxes
        With chk
            If .Top = Rows(10).Top Then
                .Value = xlOff
            End If
        End With
    Next chk
End Sub

 

[Jeremic]

Hi Andrew,
Sorry but it does not seem to work. Im using form control and not activeX, if it matters?
BR/ Jeremic

 

[Andrew Poulsom]

How are the CheckBoxes positioned within the cell? The code assumes that the top coincides with the top of the cell. If that's not the case try:

Sub Test()
    Dim chk As CheckBox
    For Each chk In ActiveSheet.CheckBoxes
        With chk
            If Not Application.Intersect(.TopLeftCell, Rows(10)) Is Nothing Then
                .Value = xlOff
            End If
        End With
    Next chk
End Sub

 

[Jeremic]

You´re a genius! Works perfectly
Im not sure how the checkboxes are positioned, but we can assume its not with the top of the cell
Thanks so much for the help!

 

[Tanner_2004]

This is super..can the same or similar code be used to reset the checkboxes in any active range of cells? Thanks!

 

[Andrew Poulsom]

What do you mean by "any active range of cells"?

 

[Tanner_2004]

Thank you. Yes, any range that I choose to "paint" by holding down the left mouse button or selecting discontiguos cells on the same worksheet using the CTRL key.

Thank you for your help.

 

[Andrew Poulsom]

I guess you could loop around the cells in Selection to test the intersect.