Checklist with multiple users - want to add date and username when checkbox marked

[fuzzy_noodle]

Im newer to VBA.

I have a simple workbook with multiple tabs for each day. I want to set it as a template so I can create a workbook for each month.

I want to have checkboxes that when a task is completed the two adjacent cells auto populate with the date and the username of who did it. And lock the changes in so they cant be changed.



I do have it set up to link to a cell for the checkbox (form controls). format control on each check box - True/false then date column has a formula =IF(A2=TRUE,TODAY(),"")

 

[NoSparks]

Form CheckBoxes have an OnAction property which can be used to call an existing macro each time the check box is clicked.

 

[My Aswer Is This]

I suggest you use a sheet change event code or a sheet double click event
If you modify a cell in a certain column a script runs which can put date and username in the columns you want.
Or use double click like
So say you double click on any cell in column A your script runs putting todays date in column B same row and user name in column C same row

Doing things this way does not require a lot of buttons on your sheet.
And only one short script is needed.
If your interested let me know with the specifics and I will write you the needed code

 

[fuzzy_noodle]

NoSparks

I acutally modified your code from here. The OP had very similar requirements that I did, just not as many check boxes.

How to make username and today static.

Good day, I have created a task tracking sheet used by multiple users. I have two columns that are causing issues. The first one is a username column that when a user checks a checkbox to mark the task as complete, it automatically puts the username in using the following VB code in a...

www.mrexcel.com

I created two modules.

Module 1 - assigned this macro to the check box

Sub ApplyOnAction()
    Dim chk As CheckBox
    
For Each chk In ActiveSheet.CheckBoxes
    With chk
        .OnAction = "Write_User_and_Date"
    End With
Next chk
    
End Sub

Then I created Module 2 - modified your code

Sub Write_User_and_Date()
    
    Dim whoCalled As Range
    
Set whoCalled = Range(ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address(0, 0))

Select Case whoCalled.Column
    Case 3
        If whoCalled = True Then
            whoCalled.Offset(, 1) = Date
            whoCalled.Offset(, 2) = Environ$("UserName")
        Else
            whoCalled.Offset(, 1) = ""
            whoCalled.Offset(, 2) = ""
        End If
End Select

End Sub

Case 3 is the column that the checkbox and the True/False would show when I link the checkbox to a cell. I then hid the contents of the cells with ( ;;; )

Its working great. I have tested it with another user openeing it and the cell values stay to what the original data was.

 

[My Aswer Is This]

If your going to have one sheet for each day of the Month.
And a workbook for each month of the year this will take a lot of checkboxes

 

[fuzzy_noodle]

Yes. But I did find a VBA script to set the check boxes to set the true/false

Sub LinkCheckBoxes()
Dim chk As CheckBox
Dim lCol As Long
lCol = 0 '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

Im looking on how to assign all check boxes to the same macro.

** I set my workbook with the same columns on each sheet**

 

[NoSparks]

With the first sub above each check box does get the same macro assigned

I have tested it with another user openeing it and the cell values stay to what the original data was.

for test purposes, put Stop as the first line in the Write_User_and_Date sub and verify that the sub is actually being called.

Edit: added below
If you're curious, when I put check boxes on a sheet I just adjust this macro to do what/where I want things.

 Private Sub Insert_Form_Checkboxes()
     Dim myCell As Range, myRng As Range
     Dim CBX As CheckBox

With ActiveSheet
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'    'delete ALL existing checkboxes from sheet, links are NOT cleared.
'    .CheckBoxes.Delete  '<~~~~~ comment out to NOT delete existing checkboxes
'    'Exit Sub           '<~~~~~ uncomment when deleting checkboxes only.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Set myRng = .Range("A2:A20")    '<~~~~~ enter the range to have checkboxes
End With
Application.ScreenUpdating = False
    For Each myCell In myRng.Cells
        With myCell
            Set CBX = .Parent.CheckBoxes.Add _
                (Top:=.Top, Left:=.Left, _
                 Width:=.Width, Height:=.Height) 'click area same size as cell
            CBX.Name = "CBX_" & .Address(0, 0)
            CBX.Caption = "Label goes here"         'whatever you want, "" for none
            CBX.Value = xlOff                       'initial value unchecked
            CBX.LinkedCell = .Offset(0, 0).Address  '<~~~~~ offset to linked cell
            CBX.OnAction = "macro to call"          'each time clicked
        End With
    Next myCell
Application.ScreenUpdating = True
End Sub

 

[fuzzy_noodle]

NoSparks,

Thanks. I will look to this when Ihave other task lists.