VBA Code for CHANGES IN EXCEL

[that_one_girl]

Hello Again,

I have a code running (code below) that is copying data from one master sheet, to one of 2 other sheets based on criteria.
What I need to do now, is add a code to this (for the whole workbook) that will track changes made. So if by example, John opens the book, and enters a new line of data, but accidentally types it over the last row of data instead of entering a new line,

I need to track
Who changed information
What information they changed (before change)
What Column and Row it was on (I prefer D9 listing vs. 9,4 if possible)
The worksheet that information was on
When they changed it
and I would like these changes to be stored on a worksheet that I will have hidden.

Here's the current code I have for the workbook:

Private Sub Workbook_Open()
Dim i, LastRow
LastRow = Sheets("ALL RECORDS").Range("A" & Rows.Count).End(xlUp).Row
Sheets("ACTIVE").Range("A2:L60869").ClearContents
Sheets("ARCHIVED").Range("A2:L60869").ClearContents
For i = 2 To LastRow
If Sheets("ALL RECORDS").Cells(i, "J").Value = "CURRENT" Then
Sheets("ALL RECORDS").Cells(i, "J").EntireRow.Copy Destination:=Sheets("ACTIVE").Range("A" & Rows.Count).End(xlUp).Offset(1)
ElseIf Sheets("ALL RECORDS").Cells(i, "J").Value = "ARCHIVE" Then
Sheets("ALL RECORDS").Cells(i, "J").EntireRow.Copy Destination:=Sheets("ARCHIVED").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next
End Sub

 

[Jeffrey Mahoney]

How many sheets are you wanting to track?

I got to thinking about how much data that is. If you're wanting to preserve, not only the new entries, but also the old data that was overwritten, you're going to have to have two copies of each sheet. The copy they see and the backup version.

Seems more prudent to make a copy of your workbook and keep that as a record for some period of time. I would log the username, sheet name, and range of cells edited. Then you could make the comparison yourself.

This code assumes you have a sheet named Log. The top left cell of the table is a named range called Log_hdr. Everytime somebody makes a worksheet change, it logs it.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)  Dim Log As Worksheet
  Dim LogHdr As Range
  Dim Cel As Range
  
  Set Log = ThisWorkbook.Sheets("Log")
  Set LogHdr = Log.Range("Log_hdr")
  Set Cel = Log.Cells(Cells.Rows.Count, LogHdr.Column).End(xlUp).Offset(1, 0)
  
  Cel.Value = Application.UserName
  Cel.Offset(0, 1).Value = Sh.Name
  Cel.Offset(0, 2).Value = Target.Address
  
End Sub

 

[that_one_girl]

Thanks. I'm using this coding now, but it is logging EVERY click and I only want to track saved changes............not sure how to do this one yet maybe you can help me with that?

I did decide to just track the sheet name, cell, what it was changed to, the user name, and the date and time........

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveSheet.Name <> "Log Details" Then
Application.EnableEvents = False
Sheets("Log Details").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & "-" & Target.Address(0, 0)
Sheets("Log Details").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = Target.Value
Sheets("Log Details").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Environ("username")
Sheets("Log Details").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Now
Sheets("Log Details").Columns("A:D").AutoFit
Application.EnableEvents = True
End If
End Sub

How many sheets are you wanting to track?

I got to thinking about how much data that is. If you're wanting to preserve, not only the new entries, but also the old data that was overwritten, you're going to have to have two copies of each sheet. The copy they see and the backup version.

Seems more prudent to make a copy of your workbook and keep that as a record for some period of time. I would log the username, sheet name, and range of cells edited. Then you could make the comparison yourself.

This code assumes you have a sheet named Log. The top left cell of the table is a named range called Log_hdr. Everytime somebody makes a worksheet change, it logs it.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)  Dim Log As Worksheet
  Dim LogHdr As Range
  Dim Cel As Range
  
  Set Log = ThisWorkbook.Sheets("Log")
  Set LogHdr = Log.Range("Log_hdr")
  Set Cel = Log.Cells(Cells.Rows.Count, LogHdr.Column).End(xlUp).Offset(1, 0)
  
  Cel.Value = Application.UserName
  Cel.Offset(0, 1).Value = Sh.Name
  Cel.Offset(0, 2).Value = Target.Address
  
End Sub

 

[Jeffrey Mahoney]

Both of our code is doing the same thing. Tracking changes is an ambiguous statement and seems like a lot of work.

This line implies that you want to save the values of the cells AFTER the changes have been made, which is what you're going to end up with anyway.

[COLOR=#333333]Sheets("Log Details").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = Target.Value[/COLOR]

My statements above were talking about keeping a backup workbook so you can do comparisons of what the values were before they made changes. Saving the address of the changed range is good for that.

Jeff