Username & Time Stamp When a Change is Made Anywhere in the Excel Workbook

[Logic_Is_Fun]

I have an Excel workbook (5 worksheets) that I'm posting to a share drive for my colleagues. The purpose of the Excel workbook is to gather assumptions in an organized manner for a financial model that I'm developing.

For data quality check purposes, I would like to have a VBA code that states the following whenever a change is made to the Excel workbook:

1) User name of who made the change;
2) Date & time the change was made;
3) Name of worksheet that was edited.

These 3 items will show on a 6^th worksheet.

I'm a simple person, who likes simple code.

Thank you in advance!!!

 

[EdNerd]

In the VBE, use the Worksheet_Change event and the Target range object.
There's various methods of getting the user's name - some use the Shell object and others the ENVIRON variables.

Depending on how big the original table is and how many users you have interacting with this, you could wind up with a lot of changes!!

Ed

 

[Logic_Is_Fun]

EdNerd, thank you for the prompt response!

Nothing is happening when I change a cell value in Initial_Range (a named ranged in Excel).

"Initial_Range" is the range I'm testing to see if a change was made. And, User_Initial & Date_Initial are names of the cells where I want to track time and username stamp the values.

I'm using the code just to check that the users are making updates. I don't mind that I don't track when every change was made.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Intersect(Target, Range("Initial_Range")) Then
        
        Range("User_Initial").Value = Application.UserName
        Range("Date_Initial").Value = Date + Time
        
    Else
        
        Exit Sub
        
    End If
    
    
End Sub

 

[EdNerd]

When you say "Nothing is happening", do you mean the Worksheet_Change event isn't firing?
Or do you mean your values are not being written to the worksheet?

If the event isn't firing, have you saved this as a macro-enabled file? And macros are enabled?

If the event fires, what part of your code isn't working?
Can you go to the Name Manager and verify your named ranges are where you think they are?
What happens if you take out the Intersect and test using something like Target.Address = $B$3 ?

You might want to drop in a few Debug.Print or MsgBox just to make sure your code is stepping through.

(Note: I'm not taking shots at your intelligence - but after 10+ years, I still do these!!)

Ed

 

[Steve_R]

Range("User_Initial").Value = Application.UserName

So you know:

The above method takes the username from Excel and can be accessed easily via.
File, Option, General
Using that method, it would be very easy to spoof an edit under somebody else's name.

Environ gets the username from environment variables (stored in Registry, I believe)
msgbox Environ("UserName")

Another method gets the logon name from the Network
https://support.microsoft.com/en-us/kb/161394

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(ActiveCell, Range("A:A")) Is Nothing Then
        lRow = Worksheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row

        Worksheets("Sheet3").Range("A" & lRow + 1).Value = Environ("UserName")
        Worksheets("Sheet3").Range("B" & lRow + 1).Value = Now
       
      
    End If
    
    
End Sub

 

[Logic_Is_Fun]

@EdNerd... I'm grateful for your help! Based on my little coding experience, I understand the importance of debugging techniques... Coding and Life = Debugging

I've changed my approach. I'm now Name & Time stamping every assumption on each worksheet using the code below, which is working (Yay!).

The problem I'm running into now is that I want to password protect these columns 21 & 22 in the example below, but still allow VBA code to name and time stamp the password protected columns. I've tried using the second code listed below (Example 2). However, when the VBA attempts to input the DateTime.Now value, a run time error occurs.


Functional Code (Example 1)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 5 Or Target.Column = 6 Then
        Cells(Target.Row, 21).Value = Application.UserName
        Cells(Target.Row, 22).Value = DateTime.Now
    End If
End Sub

Disfunctional Code (Example 2)

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Unprotect Password = "GreenGrid"
    If Target.Column = 5 Or Target.Column = 6 Then
        Cells(Target.Row, 21).Value = Application.UserName
        Cells(Target.Row, 22).Value = DateTime.Now
    End If
    ActiveSheet.Protect Password = "GreenGrid"
End Sub

Thank you so much!

 

[Steve_R]

Disfunctional Code (Example 2)

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Unprotect Password = "GreenGrid"
    If Target.Column = 5 Or Target.Column = 6 Then
        Cells(Target.Row, 21).Value = Application.UserName
        Cells(Target.Row, 22).Value = DateTime.Now
    End If
    ActiveSheet.Protect Password = "GreenGrid"
End Sub

Thank you so much!

Try

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Column = 5 Or Target.Column = 6 Then
        Application.EnableEvents = False 'turn of events
        ActiveSheet.Unprotect Password = "GreenGrid" 'keep this inside the If() so it does fire in different columns
        Cells(Target.Row, 21).Value = Application.UserName
        Cells(Target.Row, 22).Value = DateTime.Now
        ActiveSheet.Protect Password = "GreenGrid"
        Application.EnableEvents = True
    End If