How to make username and today static.

[J_W]

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 module:

Public Function UserName()
UserName = Environ$("UserName")
End Function

The problem is that the username is overwritten based on whoever opens it. I need the username to be recorded based on who checked the checkbox and then be static and not get overwritten.

My second issue is the column that records the current date using the today() formula. For each task, the date is recorded when the task was completed, but obviously this result is overwritten each time the sheet is opened on a new date. I also need this value to be recorded based on when the checkbox is checked but then be static.

Is this wishful thinking or actually possible to accomplish?

Thanks in advance!

 

[J_W]

This will apply the same .OnAction to each check box on the sheet

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

Thank you NoSparks! I will see if I can implement this. I am excited to see how this works.

 

[J_W]

I would use the .OnAction property of the check boxes so they all call a macro like this

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 6, 10
        If whoCalled = True Then
            whoCalled.Offset(, 1) = Environ$("UserName")
        Else
            whoCalled.Offset(, 1) = ""
        End If
    Case 8, 12
        If whoCalled = True Then
            whoCalled.Offset(, 1) = Date
        Else
            whoCalled.Offset(, 1) = ""
        End If
End Select

End Sub

NoSparks - this is genius!!! And extremely easy to implement. I have never seen such efficient code. It applied it to all check boxes at once! Once the macro is complete, it makes cell D4 the active cell so it's hard to know where I just clicked. Do you have any suggestions to make the active cell the same as the checkbox that was just clicked (or at least the same row)?

 

[bobsan42]

NoSparks - this is genius!!! And extremely easy to implement. I have never seen such efficient code.

I think it is only right to mark NoSparks's suggestion as solution.

Once the macro is complete, it makes cell D4 the active cell so it's hard to know where I just clicked.

The code does not make any changes to the selection. Did you make any modifications?

Do you have any suggestions to make the active cell the same as the checkbox that was just clicked (or at least the same row)?

Try to add this to the end of Write_User_and_Date:

whoCalled.Select

 

[NoSparks]

Glad you liked my solution.
Like bobsan42 said... There are many ways to skin a cat

add 2 lines to the macro and see if that's satisfactory

Select Case whoCalled.Column
    Case 6, 10
        If whoCalled = True Then
            whoCalled.Offset(, 1) = Environ$("UserName")
        Else
            whoCalled.Offset(, 1) = ""
        End If
        whoCalled.Activate
    Case 8, 12
        If whoCalled = True Then
            whoCalled.Offset(, 1) = Date
        Else
            whoCalled.Offset(, 1) = ""
        End If
        whoCalled.Activate
End Select

 

[NoSparks]

Actually, I'll go with bob's suggestion, as it will look after the column O check boxes too, thanks bob

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 6, 10
        If whoCalled = True Then
            whoCalled.Offset(, 1) = Environ$("UserName")
        Else
            whoCalled.Offset(, 1) = ""
        End If
    Case 8, 12
        If whoCalled = True Then
            whoCalled.Offset(, 1) = Date
        Else
            whoCalled.Offset(, 1) = ""
        End If
End Select

whoCalled.Activate

End Sub

 

[J_W]

Glad you liked my solution.
Like bobsan42 said... There are many ways to skin a cat

add 2 lines to the macro and see if that's satisfactory

Select Case whoCalled.Column
    Case 6, 10
        If whoCalled = True Then
            whoCalled.Offset(, 1) = Environ$("UserName")
        Else
            whoCalled.Offset(, 1) = ""
        End If
        whoCalled.Activate
    Case 8, 12
        If whoCalled = True Then
            whoCalled.Offset(, 1) = Date
        Else
            whoCalled.Offset(, 1) = ""
        End If
        whoCalled.Activate
End Select

That did it! Seriously brilliant! Thank you so much.

 

[bobsan42]

Just one more thing. I am not 100% certain, but i think you'll have to run the .onaction assignment code every time the workbook is opened or the vb project state is lost.
NoSparks should confirm it though, I can't test it at present.

 

[J_W]

Thank you Bobsan42 and NoSparks. This is my first time posting in an Excel forum and this has been a really great experience. You were both so helpful and really nice. I am use to posting in SAS forums and getting my head chewed off if I don't format something right. This has been a smooth enjoyable time. Thank you so much for taking the time to help me today. I hope you both have an amazing week because you just saved me a week of pulling my hair out.

 

[J_W]

Just one more thing. I am not 100% certain, but i think you'll have to run the .onaction assignment code every time the workbook is opened or the vb project state is lost.
NoSparks should confirm it though, I can't test it at present.

I just tested that and everything worked the same when I reopened it.

 

[bobsan42]

? Great. Glad it worked. Have a nice rest of your day or night
And please change the accepted solution if possible.