I want to make multiple columns of checkboxes that update a timestamp cell in each relative row.

[Mattdivo523]

I am attempting to build a cadence structure for sales in excel. The whole point is that each checkbox in the row will represent a certain task that is to be completed on a certain day.

I will explain what one cell within a row should look like but ultimately I need this to happen across multiple rows, each being independent from the other.

1.) B1:K1 will all contain checkboxes.

2.) Each checkboxes purpose is to update the date in cell A1 to when the next task should be accomplished.

3.) Weekends must be excluded from consideration when setting the new date.

4.) My first task would be the B1 Check Box. After it is checked A1 should update the next workday: If I checked B1 today (3/24/2022), A1 would populate with ( 3/25/2022)

5.) My Second task would be set to the date that is in A1. So I would check the box C1, but this time I need it to be 2 days later, exlcuding weekends. So when I check box C1, cell A1 should populate with (3/29/22).

6. I need this formula to continue all the way till K1. Checking K1 won't need to do anything to A1 because it the final task.

7. Feel free to add any interval of time to D1:J1. They can all be the same I will alter the code to the correct time intervals for each.

8.) Again, I do need this to happen across multiple rows (A1:A100) each row independently doing the process above.

I am trying to figure this out on my own, but I am new to coding so anything helps.

 

[John_w]

See if the code below helps to get you started. The checkboxes are form controls checkboxes.

It's not clear if you've already added the checkboxes to cells B1:K100. I assume you haven't, therefore the Add_Checkboxes routine adds checkboxes to cells B1:K100 on the active sheet. Add_Checkboxes is meant to be run manually once. However, for testing, it first deletes every checkbox, so that the routine can be run manually multiple times to start afresh.

If you've already added the checkboxes, for example with different captions for each checkbox, then you'll need similar code to loop through each checkbox and update its OnAction property to .OnAction = "CheckBox_Click", which runs the CheckBox_Click routine when the checkbox is clicked.

Put this code in a standard module.

Option Explicit


Public Sub Add_Checkboxes()

    Dim ws As Worksheet
    Dim r As Long, c As Long
    Dim cb As CheckBox

    Application.ScreenUpdating = False
    
    With ActiveWorkbook
    
        Set ws = .ActiveSheet

        Delete_Checkboxes ws

        'Add checkboxes to rows 1:100 in columns B:K
        
        For r = 1 To 100
            For c = Range("B1").Column To Range("K1").Column
                With ws
                    Set cb = .CheckBoxes.Add(.Cells(r, c).Left, .Cells(r, c).Top, .Cells(r, c).Width, .Cells(r, c).Height)
                End With
                With cb
                    .Caption = ""
                    .Value = xlOff
                    .Display3DShading = False
                    .Name = "CB_" & r & "_" & c
                    .OnAction = "CheckBox_Click"
                End With
            Next
        Next
        
        'Clear and format column A cells as locale date format
        
        ws.Range("A1").Resize(r - 1).ClearContents
        ws.Range("A1").Resize(r - 1).NumberFormat = "m/d/yyyy"
        
    End With

    Application.ScreenUpdating = True

End Sub


Public Sub CheckBox_Click()

    Dim cb As CheckBox
    Dim colAcell As Range
    
    'Application.Caller is name of Checkbox that was clicked
    
    With ActiveSheet
        Set cb = .CheckBoxes(Application.Caller)
        If cb.Value = xlOn Then
            Set colAcell = .Cells(cb.TopLeftCell.Row, "A")
            Select Case Split(cb.TopLeftCell.Address, "$")(1)
                Case Is = "B"
                    colAcell.Value = Application.WorksheetFunction.WorkDay(Date, 1)
                Case Is = "C", "D", "E", "F", "G", "H", "I", "J"
                    colAcell.Value = Application.WorksheetFunction.WorkDay(colAcell.Value, 2)
            End Select
        End If
    End With
    
End Sub


Private Sub Delete_Checkboxes(ws As Worksheet)
    With ws.CheckBoxes
        While .count > 0
            .Item(1).Delete
        Wend
    End With
End Sub

 

[Mattdivo523]

erent captions for each checkbox, then you'll need similar code to loop through each checkbox and update its OnAction property to .OnAction = "CheckBox_Click", which runs the

See if the code below helps to get you started. The checkboxes are form controls checkboxes.

It's not clear if you've already added the checkboxes to cells B1:K100. I assume you haven't, therefore the Add_Checkboxes routine adds checkboxes to cells B1:K100 on the active sheet. Add_Checkboxes is meant to be run manually once. However, for testing, it first deletes every checkbox, so that the routine can be run manually multiple times to start afresh.

If you've already added the checkboxes, for example with different captions for each checkbox, then you'll need similar code to loop through each checkbox and update its OnAction property to .OnAction = "CheckBox_Click", which runs the CheckBox_Click routine when the checkbox is clicked.

Put this code in a standard module.

Option Explicit


Public Sub Add_Checkboxes()

    Dim ws As Worksheet
    Dim r As Long, c As Long
    Dim cb As CheckBox

    Application.ScreenUpdating = False
   
    With ActiveWorkbook
   
        Set ws = .ActiveSheet

        Delete_Checkboxes ws

        'Add checkboxes to rows 1:100 in columns B:K
       
        For r = 1 To 100
            For c = Range("B1").Column To Range("K1").Column
                With ws
                    Set cb = .CheckBoxes.Add(.Cells(r, c).Left, .Cells(r, c).Top, .Cells(r, c).Width, .Cells(r, c).Height)
                End With
                With cb
                    .Caption = ""
                    .Value = xlOff
                    .Display3DShading = False
                    .Name = "CB_" & r & "_" & c
                    .OnAction = "CheckBox_Click"
                End With
            Next
        Next
       
        'Clear and format column A cells as locale date format
       
        ws.Range("A1").Resize(r - 1).ClearContents
        ws.Range("A1").Resize(r - 1).NumberFormat = "m/d/yyyy"
       
    End With

    Application.ScreenUpdating = True

End Sub


Public Sub CheckBox_Click()

    Dim cb As CheckBox
    Dim colAcell As Range
   
    'Application.Caller is name of Checkbox that was clicked
   
    With ActiveSheet
        Set cb = .CheckBoxes(Application.Caller)
        If cb.Value = xlOn Then
            Set colAcell = .Cells(cb.TopLeftCell.Row, "A")
            Select Case Split(cb.TopLeftCell.Address, "$")(1)
                Case Is = "B"
                    colAcell.Value = Application.WorksheetFunction.WorkDay(Date, 1)
                Case Is = "C", "D", "E", "F", "G", "H", "I", "J"
                    colAcell.Value = Application.WorksheetFunction.WorkDay(colAcell.Value, 2)
            End Select
        End If
    End With
   
End Sub


Private Sub Delete_Checkboxes(ws As Worksheet)
    With ws.CheckBoxes
        While .count > 0
            .Item(1).Delete
        Wend
    End With
End Sub

Adding the clickboxes in worked.

However when I ran the code to add the code to each check box, I kept receiving:


Error 1004
application-defined or object-defined error

 

[John_w]

However when I ran the code to add the code to each check box, I kept receiving:


Error 1004
application-defined or object-defined error

It's not clear what you're doing to cause that error. If you're running CheckBox_Click then that's wrong; you don't run CheckBox_Click. It is run automatically when you click one of the checkboxes added by Add_Checkboxes.