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

 

[eric91970]

Hi,

It happens on opening the spreadsheet.

Thanks.

 

[Joe4]

Hi,

It happens on opening the spreadsheet.

Thanks.

Do you have links or "Workbook_Open" code or something else automatically making updates to the data upon opening the file?

 

[eric91970]

Hi,

Yes I have a login userform box and also a digital clock running on "Workbook_Open" code.

Thanks again!!

 

[Joe4]

What exactly does the "login userform box" do?
Is there VBA code behind it?
If so, please post the VBA code.

Also, please post the VBA code found in the "Workbook_Open" procedure.

All these interactions and automations are very important when dealing with automated code that tracks changes.

 

[eric91970]

Hi,

VBA code below as requested.

Private Sub Workbook_Open()
'
' Digital Clock Macro & Login Box

Sheets("Front Page").Range("B18").Value = Now
Application.OnTime Now + TimeValue("00:00:01"), "Digital_Clock"
Application.Visible = False
Login.Show

End Sub

Thanks

 

[Joe4]

OK, you probably do not want to log the clock, so you should probably disable the updates happening in there like this:

Private Sub Workbook_Open()
'
' Digital Clock Macro & Login Box

Application.EnableEvents = False
Sheets("Front Page").Range("B18").Value = Now
Application.EnableEvents = True
Application.OnTime Now + TimeValue("00:00:01"), "Digital_Clock"
Application.Visible = False
Login.Show

End Sub

You may also need to make similar edits to the VBA code behind your Login form, depending on what that is doing.

 

[eric91970]

Hi,

Still exactly the same error even with both disable / removed.

Thanks.

 

[Joe4]

When you get the error, does it give you a "Debug" button/option?
If so, click and tell us what line of VBA code it highlights, and if it is in a different VBA procedure, post the VBA code from that procedure.

 

[eric91970]

Joe4,

I think I have given up the code which you posted as I just cant get it to work at all.............Sorry!!

As per my original request is there anyway to update / additional extra lines so I have the bold as well.

User	Sheet	Cell	Time & Date	Previous / Old Value	Change / New Value
eric91970	Quotes	$B$4	08/03/2024 16:10

Thanks again!!!

 

[Joe4]

The part that you want is the hardest part. There is nothing inherently in Excel that stores or tells is the previous value. So you really have two options:
1. Use the application.undo feature in your code to temporarily undo the change, long enough to get the value, and then re-do the change.
2. In addition to what you have now, use the "Workbook_SheetSelectionChange" event procedure to grab and store the value (in a Global variable) of any cell you enter into, so you have the previous value.

So let's try pivoting to the second option, which requires three parts (all of which must be placed in the "ThisWorkbook" module:
1. A Global variable declaration to store the previous value
2. A "Workbook_SheetSelectionChange" procedure which captures the value of a cell as you first enter into it (select it) and stores it in our Global variable
3. A "Workbook_SheetChange" to log the values into our Log sheet

So here should be everything you need to place in the "ThisWorkbook" module:

Public OldValue As Variant


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name <> "Log" Then
        OldValue = Target.Value
    End If
End Sub


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

    Dim nr As Long

'   Get old and new values if update to any sheet other than Log sheet
    If Sh.Name <> "Log" Then
      
'       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 = Target.Value 'update previous value in column F
        End With
        Application.EnableEvents = True
  
    End If

'   Save workbook to save changes
    ActiveWorkbook.Save

End Sub