CODEWITHSUNDEEP
vbaexcel
jtrousd
jtrousd

Reputation: 33

Excel VBA - Insert Username ONLY when cell is changed

Here's my problem: I have working code to insert a username and timestamp when a user makes a change anywhere in a row. Great! So my code works and I answered my own question, right? Nope! There's a tiny issue which, while it doesn't break the code, does lead to a user having their username input as having made a change when a change was not made.

Here's my code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    ThisRow = Target.Row
    'protect Header row from any changes
    If (ThisRow = 1) Then
           Application.EnableEvents = False
           Application.Undo
           Application.EnableEvents = True
           MsgBox "Header Row is Protected."
           Exit Sub
    End If
    For i = 1 To 61
        If Target.Column = i Then
               ' time stamp corresponding to cell's last update
               Range("BK" & ThisRow).Value = Now
               ' Windows level UserName | Application level UserName
               Range("BJ" & ThisRow).Value = Environ("username")
           Range("BJ:BK").EntireColumn.AutoFit
        End If
    Next i
End Sub

Here's how it happens: A user decides they want to make a change to a cell, so they double click the cell. Now, if they push the escape key, nothing happens and everything is hunky dory. But, if they double click the cell, then click outside of the cell to another cell to leave that cell, the system logs that as a change even though no change was made and the user's username is put into column 62. This is no bueno, because someone could be held responsible for a mistake that another individual has made if they're incorrectly put down as the last person to change something in that row.

Conversely - it might be worthwhile to create a comment in a cell which is changed by a user, but I reckon I'd have the same issue with double-clicking a cell, so I'd still have to account for it.

Thoughts?

Edit: Full disclosure, I found this code elsewhere and adapted it to my purposes.

Upvotes: 1

Views: 3485

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27249

You can test to see if the old value and the new value are the same. I use "new" loosely, meaning excel things that the cell was edited so it's a "new" value in terms of the Worksheet_Change event understanding.

I also got rid of your For loop as it seemed very unnecessary. If I am mistaken, I apologize.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    Dim ThisRow As Long ' make sure to declare all the variables and appropiate types
    ThisRow = Target.Row

    'protect Header row from any changes
    If (ThisRow = 1) Then

           Application.EnableEvents = False
           Application.Undo
           Application.EnableEvents = True
           MsgBox "Header Row is Protected."
           Exit Sub

    End If

    If Target.Column >= 1 And Target.Column <= 61 Then

        Dim sOld As String, sNew As String
        sNew = Target.Value 'capture new value

        With Application
            .EnableEvents = False
            .Undo
        End With

        sOld = Target.Value 'capture old value
        Target.Value = sNew 'reset new value

        If sOld <> sNew Then

            ' time stamp corresponding to cell's last update
            Range("BK" & ThisRow).Value = Now
            ' Windows level UserName | Application level UserName
            Range("BJ" & ThisRow).Value = Environ("username")
            Range("BJ:BK").EntireColumn.AutoFit

        End If

        Application.EnableEvents = True

    End If

End Sub

Upvotes: 2

Related Questions