record changes to a worksheet

[franklinsam]

Let me begin by saying that I still a newbie with VB so I might appear to be bit dense at first.

I want to add the date, time and user id to an excel spreadsheet when any cell in the current worksheet changes. The spreadsheet has multiple tabs but I'm only interested if one specific worksheet changes. I understand that autosave will change saved date but that isn't what I'm looking for, I'm interested knowing when any cell in a specific tab changes. I would also like a log file in the same workbook to keep track of all changes.

Thanks

 

[Joe4]

Where exactly do you want to store these changes?
On a different sheet?
What is the name of the sheet that you want to watch, the sheet where you want to log the information, and what cells do you want the logged in information stored in?

 

[franklinsam]

Where exactly do you want to store these changes?
On a different sheet?
What is the name of the sheet that you want to watch, the sheet where you want to log the information, and what cells do you want the logged in information stored in?

Let's make the assumption that I have a workbook with 2 tabs. One is called log and the other is called BOB. I would like to "watch" BOB for any changes and record the date and time of the change in BOB. In the log worksheet I would like to record the date, time and user id.

I guess now that I'm thinking this more clearly it doesn't make sense to add a change to the worksheet that I want to record changes in because then I'm the author of last change

 

[Joe4]

Are you looking to just keep track of last change (so you just keep overwriting the most recent update details), or are you wanting to log every change (so don't overwrite previous ones, just add to a running list)?

 

[franklinsam]

The worksheet BOB should have just 1 or 2 cells for time and date and 1 cell for user id that would be overwritten when the worksheet is changed. The log worksheet should be a running log of all changes. Which we might want to pare down if it becomes too big
Thank you

 

[Joe4]

OK, assuming that you have two sheets named "BOB" and "Log", and you want to add the username to the next available row in column A on the "Log" sheet and the date/time of the update to column B, use the following VBA code. This MUST be placed in the proper sheet module and MUST be named as shown.

To ensure you put it in the correct place, go to the "BOB" sheet, right-click on the sheet tab name at the bottom of the screen, select "View Code", and copy/paste this code into the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)
'   Log date/time and username to Log sheet whenever change made to BOB sheet
    
    Dim lr As Long
    
'   Find last row in column A on Log sheet with data
    lr = Sheets("Log").Cells(Rows.Count, "A").End(xlUp).Row
    
'   Put username in next available row in column A on Log sheet
    Sheets("Log").Range("A" & lr + 1).Value = Environ("username")
    
'   Put date/time in next available row in column B on Log sheet
    Sheets("Log").Range("B" & lr + 1).Value = Now
    
End Sub

This will automatically log changes on to the Log sheet as you make manual data updates to the BOB sheet.

 

[franklinsam]

OK, assuming that you have two sheets named "BOB" and "Log", and you want to add the username to the next available row in column A on the "Log" sheet and the date/time of the update to column B, use the following VBA code. This MUST be placed in the proper sheet module and MUST be named as shown.

To ensure you put it in the correct place, go to the "BOB" sheet, right-click on the sheet tab name at the bottom of the screen, select "View Code", and copy/paste this code into the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)
'   Log date/time and username to Log sheet whenever change made to BOB sheet
   
    Dim lr As Long
   
'   Find last row in column A on Log sheet with data
    lr = Sheets("Log").Cells(Rows.Count, "A").End(xlUp).Row
   
'   Put username in next available row in column A on Log sheet
    Sheets("Log").Range("A" & lr + 1).Value = Environ("username")
   
'   Put date/time in next available row in column B on Log sheet
    Sheets("Log").Range("B" & lr + 1).Value = Now
   
End Sub

This will automatically log changes on to the Log sheet as you make manual data updates to the BOB sheet.

This kind of works except that it make a record of each change in the Log worksheet when all I need is for the log file to record that a change was made and not how many. In other words, if I make 12 changes in BOB, I only want to see 1 entry in the Log worksheet, potentially as the file is being closed.

I'm assuming that it isn't possible to include a column in BOB that shows the last date and time of a change?

 

[Joe4]

OK, you seem to be contradicting yourself here. I specifically asked you if you wanted a single one or a running log, and you said this:

The log worksheet should be a running log of all changes.

Also, I don't know that it really makes sense to track "time" then, if you are only logging one record in for all changes that you make (so what would the time really represent? It would be the time the file is saved/closed, not when the changes were made).

I want to add the date, time and user id to an excel spreadsheet

This kind of works except that it make a record of each change in the Log worksheet when all I need is for the log file to record that a change was made and not how many. In other words, if I make 12 changes in BOB, I only want to see 1 entry in the Log worksheet, potentially as the file is being closed.

So what if the file is opened up two separate times in a day, and a bunch of changes were made each time?
Do you want just one entry in the Log file for that day or two?

I think perhaps you need to think this through a little more, and clarify exactly how you want this to work - maybe provide examples to show us how you want it to work.

 

[franklinsam]

If the file is opened and changes are made then there should be 1 line added to the Log worksheet. If the file is opened a second time and changes are made then 1 line needs to be added to the Log workbook. If the file is opened a third time and no changes are made no lines should be added to the Log workbook. Does that help?

 

[Joe4]

OK, I think I see what you are doing now.

I think I have a plan. Basically, when an update is made to the "BOB", we are going to put a value at the bottom of column A of our "Log" sheet that says "UPDATE".
So every time a change is made on "BOB", it will check to see if the last value in column A on the "Log" sheet is "UPDATE" and if it is not, it will add "UPDATE" to the bottom of the list.
That way, no matter how many changes you make during that session, "UPDATE" will only appear once on one line.

Then, we will add automated code that will run as the workbook is being closed. At that point, it will check the last value in that column and if it is "UPDATE", it will replace it with the username and date/time stamp, and save the file. So that should accomplish what you want.

So, we first need to update the current "Worksheet_Change" code I gave you originally to this:

Private Sub Worksheet_Change(ByVal Target As Range)
'   Log "UPDATE" to bottom of Log sheet whenever change made to BOB sheet
    
    Dim lr As Long
    
'   Find last row in column A on Log sheet with data
    lr = Sheets("Log").Cells(Rows.Count, "A").End(xlUp).Row
    
'   See if current value in that cell is "UPDATE"
    If Sheets("Log").Range("A" & lr).Value = "UPDATE" Then
'       Do nothing -- changes have already been flagged for updating
    Else
'       Put "UPDATE" in next available row in column A on Log sheet
        Sheets("Log").Range("A" & lr + 1).Value = "UPDATE"
    End If
    
End Sub

Then you will need to put the following VBA code in the "ThisWorkbook" module in the VB Editor (it MUST go there):

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim lr As Long
    
'   Find last row in column A on Log sheet with data
    lr = Sheets("Log").Cells(Rows.Count, "A").End(xlUp).Row
    
'   See if value in that cell is "Update"
    If Sheets("Log").Range("A" & lr) = "UPDATE" Then
'       Put username in next available row in column A on Log sheet
        Sheets("Log").Range("A" & lr).Value = Environ("username")
'       Put date/time in next available row in column B on Log sheet
        Sheets("Log").Range("B" & lr).Value = Now
    End If
    
'   Save workbook
    ActiveWorkbook.Save
    
End Sub