Change Log With VBA

[eric91970]

Hi,

The below code is from another thread and works wonderfully well, is there anyway to add extra code to enable full tracking of what actual change that was made was. At the moment it only tracks the user, sheet, cell number, the time and date.

Thanks in advance.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Ar As Range
Dim LogSht As Worksheet
Dim LogUserHdr As Range
Dim Cel As Range
Dim Astr As String


Select Case Sh.Name
Case "Quotes", "Test"
Set LogSht = ThisWorkbook.Worksheets("Log")
Set LogUserHdr = LogSht.Range("LogUserHdr")
Set Cel = LogSht.Cells(LogSht.Rows.Count, LogUserHdr.Column).End(xlUp).Offset(1, 0)
Cel.Value = Application.UserName 'Username
Cel.Offset(0, 1).Value = Sh.Name 'sheet name
Cel.Offset(0, 3).Value = Now() 'Date / Time
For Each Ar In Target.Areas
If Len(Astr) > 0 Then
Astr = Astr & ", " & Ar.Address
Else
Astr = Ar.Address
End If
Next Ar
Cel.Offset(0, 2).Value = Astr
End Select

End Sub

 

[Joe4]

Welcome to the Board!

Please show us some sample data and your expected output (just be sure to "dummy up" any sensitive data). It is important for us to know the locations (columns) of the data you are trying to track.
And please let us know the addresses of the named ranges you are using in your code.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[eric91970]

Joe4,

Thank you for the response. I had a spreadsheet previously which unfortunately I have manage to lose along with the VBA code which basically tracked and logged any / all changes to multiple sheets.

It added a line per cell change and did something like the below, sorry I am probably explaining it very badly!!

User​	Sheet​	Cell Number​	Time & Date​	Previously​	Change To​
A N Other​	Sheet 1​	$D$18​	06/03/2024 18:22​	Testing 111​	xxxxxxzzzz​
A N Other​	Sheet 2​	$C$17​	06/03/2024 18:45​	Testing 222​	aaaaaa​
A N Other​	Sheet 1​	$D$18​	06/03/2024 20:15​	xxxxxxzzzz​	eeee​
A N Other​	Sheet 2​	$C$17​	06/03/2024 20:45​	aaaaaa​	sdfsdfsd​

Thanks in advance!!

 

[Joe4]

Any change to any cell on any sheet, and it showed the previous value! That gets pretty involved/a little tricky.
I have some thoughts, though I have never tried it myself.

 

[Joe4]

How many sheets exactly are in this workbook?
Do you want to attract all changes every on every sheet, except for the "Log" sheet?
If not, please detail the conditions on which sheets and ranges we are watching.

 

[Joe4]

OK, borrowing the logic from this post here: Get cell's old value on a worksheet change event, I think I was able to come up with something that isn't too bad. Just note that it only works well for when one cell is updated at a time. If you copy/paste a multi-cell range, it will tell you the whole range that you updated, but only show the Previous/New values for the first cell in that range.

This code NEEDS to be put in the "ThisWorkbook" module of your workbook in VBA:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim NewValue, OldValue
    Dim nr As Long

'   Get old and new values if update to any sheet other than Log sheet
    If Sh.Name <> "Log" Then
       
        Application.EnableEvents = False
        With Target
            NewValue = .Value
            Application.Undo
            OldValue = .Value
            .Value = NewValue
        End With

'       Find next available row on Log sheet
        nr = Sheets("Log").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
'       Update log sheet
        With Sheets("Log")
            .Cells(nr, "A").Value = Environ$("UserName") 'update user in column A
            .Cells(nr, "B").Value = Sh.Name 'update sheet name in column B
            .Cells(nr, "C").Value = Target.Address ' update address in column C
            .Cells(nr, "D").Value = Now() 'update date/time stamp in column D
            .Cells(nr, "E").Value = OldValue 'update previous value in column E
            .Cells(nr, "F").Value = NewValue 'update previous value in column F
        End With
        Application.EnableEvents = True
   
    End If

'   Save workbook to save changes
    ActiveWorkbook.Save

End Sub

 

[eric91970]

Hi,

I have inputted this into the "ThisWorkbook" module in the workbook but unfortunately it does nothing!! Are you able to upload a working version on a new / blank spreadsheet?

Thanks again.

 

[Joe4]

Do you have a sheet named "Log" in your workbook?
If you do, and you have VBA enabled, it should automatically update the Log sheet every time you make a text edit on any other sheet.

 

[eric91970]

Hi,

Yes I have VBA enabled however I get a Run-time error '1004: Method 'Undo' of object'_Application failed error message.

Thanks again.

 

[Joe4]

Can you tell me exactly what you are doing when that error message gets triggered?
Are you updating a single cell's value, or doing something else (copy/paste, deletion, etc)?
I want to see if I can recreate exactly what you are doing that is causing this error to happen.