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]

One way to fix the values is to change the formulas to values before saving the workbook. However I would not use this, it may have negative performance impact at least.

I don't know if it is the best approach, but in such situation I would use only 4 comboboxes and VBA. 5 if you count the last Flag Column, but it is a bit different.
So 4 CBs (let's call them CB1, 2, 3 and 4), one for each of the cells that have to be filled. Normally the CBs will be hidden out of sight. Once you select a cell the CBs will be moved to the current row, position themselves over the relevant 4 cells. If the cell below a CB is empty - show the CB. Once You click the CB the cell below it will be filled with the necessary value, not a formula and the CB disappears.
You may even control which CBs to be shown depending on which cells are filled for example you don't show CBs 2, 3 and 4 if CB1 is not clicked.In this way you will also need 4 less columns.

Thank you for your suggestion. Please bear with me as I try to work through it. I should note that the tasks are not always dependent meaning a task that appears on row 18 may be completed before or after row 19. Does this affect your solution? You mention that there would be a rule if the cell below is empty, show.

I like that this approach as it will produce less columns! I love efficiency!

When you say comboboxes, I think of a list. What would I put in this list? A predetermined set of usernames? What about the date?

I will also say that I got feedback today from the users who absolutely love the one click approach. In the old version of this spreadsheet that I had no part in creating, the user was expected to enter their initials and then the date manually. There wasn't even formatting applied to the dates so it was all over the place. They are really hoping I can find a way to keep the ease of use approach.

 

[bobsan42]

Thank you for your suggestion. Please bear with me as I try to work through it. I should note that the tasks are not always dependent meaning a task that appears on row 18 may be completed before or after row 19. Does this affect your solution? You mention that there would be a rule if the cell below is empty, show.

I like that this approach as it will produce less columns! I love efficiency!

When you say comboboxes, I think of a list. What would I put in this list? A predetermined set of usernames? What about the date?

I will also say that I got feedback today from the users who absolutely love the one click approach. In the old version of this spreadsheet that I had no part in creating, the user was expected to enter their initials and then the date manually. There wasn't even formatting applied to the dates so it was all over the place. They are really hoping I can find a way to keep the ease of use approach.

Aaargh, I guess I am tired and managed to confuse you. I meant CHECKBOXES.
You still use checkboxes, but only four of them, with some code behind each one to fill the cells. You move them to the active row and show only the ones that are needed.
About the ease of use - you will need to select the necessary row to show the checkboxes for clicking. So Two click instead of One.

 

[NoSparks]

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

 

[bobsan42]

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

This is applicable for sheet controls, yes. The only drawback being that only about 600 calls to this sub have to be written. And their number probably changes from one month into the next.
I, personally would go for 4 checkboxes moving around, playing "now.you see me, now you don't" and calling your sub on clicking.

 

[J_W]

Aaargh, I guess I am tired and managed to confuse you. I meant CHECKBOXES.
You still use checkboxes, but only four of them, with some code behind each one to fill the cells. You move them to the active row and show only the ones that are needed.
About the ease of use - you will need to select the necessary row to show the checkboxes for clicking. So Two click instead of One.

Ok. I get it. This sounds promising! So possible but now I need to figure out how to code this. Any chance you've done this before and have some sample code I could start with?

 

[bobsan42]

Yes I have, but for command buttons.
I will try to adapt it to your case and post it here.
You can stll keep the True/False columns for filtering, if you deem it necessary.

 

[NoSparks]

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

 

[J_W]

Yes I have, but for command buttons.
I will try to adapt it to your case and post it here.
You can stll keep the True/False columns for filtering, if you deem it necessary.

Thank you bobsan42. Keeping the true/false values would be a life saver so I don't have to redo the crazy amount of conditional formatting I've put into these rows.

 

[bobsan42]

@*J_W, Now is the time to decide which way you want to go
NoSparks's solution seems to be feasible.

 

[bobsan42]

Thank you bobsan42. Keeping the true/false values would be a life saver so I don't have to redo the crazy amount of conditional formatting I've put into these rows.

Check the proposed solution from NoSparks.