How to insert username stamp when a cell has a specific value?

Question

I am trying to insert a username when a specific value is filled into a cell.

Example,

If Cell X14 is set to "Done" or "Skip" by a user, then I want Cell Z14 to have username, who's currently working in the workbook. And when the cell doesn't have "Done" or "Skip" anymore, username disappears too.

Can anyone of you help me out to do this? preferably only excel formula, but VBA is also good.

Thank you !

Yes, any cell down column x :) I think yes, VBA will have to be used.. thanks for your comment — Sunghwan Park, Feb 25 '19 at 12:25

score 1 · Accepted Answer · answered Feb 22 '19 at 02:43

1

Use the below if you are looking for change for any cell on Column X (which is Target.Column = 24)...

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 24 And Target.Count = 1 Then
    If Target = "Done" Or Target = "Skip" Then
        Application.EnableEvents = False
            Target.Offset(0, 2) = Application.UserName
        Application.EnableEvents = True
    End If
End If

End Sub

answered Feb 22 '19 at 02:43

urdearboy

14,439
5
28
58

1

use `Environ("Username")` instead of `Application.UserName`. You may want to see [This](https://stackoverflow.com/questions/13412418/how-can-i-display-my-windows-user-name-in-excel-spread-sheet-using-macros). Also since you are using `.EnableEvents`, you may want to use error handling. [Interesting read](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) – Siddharth Rout Feb 22 '19 at 07:16
I will try to use this :) thanks for your comment. when this works, i will give you feedback as well – Sunghwan Park Feb 25 '19 at 12:26

Ricardo Diaz · Answer 2 · 2019-02-22T11:25:45.040

As your scenario infers that you may have more than one user entering data, in my opinion, the VBA approach is the route you should take.

Copy and paste this code into your sheet module and customize the <<< Customize this >>> section

Private Sub Worksheet_Change(ByVal Target As Range)

    ' Define object variables
    Dim statusRange As Range
    Dim changedCell As Range

    ' Define variables
    Dim currentUserName As String
    Dim userNameColumn As String
    Dim statusColumnNumber As Integer
    Dim statusValuesList As Variant

    ' <<< Customize this >>>
    Set statusRange = Range("X10:X100") ' Limit the cells that will record the status
    statusValuesList = Array("Done", "Skip") ' Add more status values separated by commas and inside quotes (this is not case-sensitive)
    userNameColumn = "Z" ' Column letter where the UserName is going to be stamped

    ' Prevent from firing other events while making changes to cells
    Application.EnableEvents = False

    ' Validate if cell changed belongs to valid column and rows and if it has a valid status
    If Not Intersect(Target, statusRange) Is Nothing Then

        For Each changedCell In Target

            If Not IsError(Application.Match(changedCell.Value, statusValuesList, 0)) Then

                ' Get current username
                currentUserName = Environ("Username")

            Else

                ' Empty username string
                currentUserName = vbNullString

            End If

            ' Assign username to cell in previously defined column and same row
            Range(userNameColumn & changedCell.Row).Value = currentUserName

        Next changedCell

    End If

    ' Reenable firing events
    Application.EnableEvents = True

End Sub

very long way to get to an answer and difficult to understand. this shouldnt be the answer, below by @urdearboy is corret.. — alowflyingpig, Feb 22 '19 at 03:07
Thanks for your answers, i haven't tried them yet, will get back to you once i tried it out if it works — Sunghwan Park, Feb 27 '19 at 05:55

How to insert username stamp when a cell has a specific value?

2 Answers2