VBA code to track changes in workbook

[Fwiz]

hi,

I have a specific range within my worksheet ie d9:v20

when any of these cells within this range are changed - ie changed being defined as changed the content of the cell and saved the workbook.

if the content of cells are changed then I'd like my code to write up the event in a separate worksheet (in same workbook) showing name, date of change and what the cell was changed to and from.

is this possible?



thanks

 

[SydneyGeek]

Not sure how to capture the old value in Excel; the code only fires once the cell has changed. Maybe you need to start by populating all the original values into the log sheet to give a start point, then log the changes.

To do the changes, select the range that you want to track and name it AuditRange.

Name the sheet where changes are logged Log Sheet.
Headings are Address, New Value, TimeStamp.

On the sheet that you are tracking right-click the sheet tab, View Code, and paste this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strAddress As String
    Dim val
    Dim dtmTime As Date
    Dim Rw As Long
    
    If Intersect(Target, Range("AuditRange")) Is Nothing Then Exit Sub
    
    dtmTime = Now()
    val = Target.Value
    strAddress = Target.Address
    
    Rw = Sheets("Log Sheet").Range("A" & Rows.Count).End(xlUp).Row + 1
    With Sheets("Log Sheet")
        .Cells(Rw, 1) = strAddress
        .Cells(Rw, 2) = val
        .Cells(Rw, 3) = dtmTime
    End With
End Sub

Denis

 

[Ruddles]

Create a worksheet called Audit, then save and close the workbook. (You can make it hidden later, when the code is tested and working to your satisfaction.)

In the Workbook_Open event, we to check whether there are any differences between the saved range and the range in the 'live' worksheet. If there are, we log them to Audit and then make a new copy of the range in Audit before releasing control to the user, logging his name and the date/time before we do so. Next time someone opens the workbook, the changes from the current session will be logged under the name of the previous user.

Something along these lines:-

Option Explicit
 
Const LiveWS As String = "Sheet1"
Const AuditWS As String = "Audit"
 
Private Sub Workbook_Open()
 
  Dim iRow As Integer
  Dim iCol As Integer
  Dim iLastRow As Long
 
  For iRow = 9 To 20
    For iCol = 4 To 22
      If Sheets(AuditWS).Cells(iRow, iCol).Value <> Sheets(LiveWS).Cells(iRow, iCol).Value Then
        iLastRow = Sheets(AuditWS).Cells(Rows.Count, 1).End(xlUp).Row
        Sheets(AuditWS).Cells(iLastRow + 1, 1) = "Cell(" & CStr(iRow) & "," & CStr(iCol) & ") " _
          & "changed from '" & Sheets(AuditWS).Cells(iRow, iCol).Value & "' " _
          & "to '" & Sheets(LiveWS).Cells(iRow, iCol).Value & "'"
        Sheets(AuditWS).Cells(iRow, iCol) = Sheets(LiveWS).Cells(iRow, iCol).Value
      End If
    Next iCol
  Next iRow
 
  iLastRow = Sheets(AuditWS).Cells(Rows.Count, 1).End(xlUp).Row
  Sheets(AuditWS).Cells(iLastRow + 1, 1) = "Workbook opened by " & Environ("USERNAME") _
     & " on " & Format(Now(), "dd/mm/yyyy") & " at " & Format(Now(), "hh:nn:ss")
 
  ActiveWorkbook.Save
 
End Sub

Any good? Happy to make changes as required - just ask.

 

[Ruddles]

Next time someone opens the workbook, the changes from the current session will be logged under the name of the previous user.

That should have read: "Next time someone opens the workbook, the changes from the current session will be logged under the name of the current user (i.e. before the subsequent user is logged as having opened the workbook)."

The best way to see what it's doing is to try it. Don't make any manual changes to Audit though.

 

[Fwiz]

thanks guys, both very good ideas.

I've found this thread on another forum that captures the old cell value - how can i incorporate this into either suggestions?

http://www.excelforum.com/excel-programming/674069-capture-cell-value-before-change-event.html

any idea - this level of VBA code is way out of my reach .... but hey I'm learning...

thanks for your help

 

[Ruddles]

That 'solution' doesn't log anything - as far as I can tell it merely displays a warning in a message box.

My suggestion captures the old value and the new value and the identity of the person who changed it and the date and time they opened the workbook. It drops straight into a workbook as it stands, it's tested and it works, and has the advantage that the author is available for consultation, code modifications, etc.

 

[Fwiz]

I agree your proposed solution is a good one, but I'm not sure if this will fit the requirements 100% - for example, this worksheet is a resource planner and helps me to track shift patterns and vacation requirements, however as multiple users have access and dispite numerous attempt to prevent errors - they still occur, the idea of the tracker will record every change event steps by means of changing something and saving the workbook. if I have a copy of the range for comparision then the field could be changed several times over but only records the 1 event change?....for example, live sheet has value of "E" the copied version has "E" if a users changes this to a "L" this will record on the log, if later on the user changes that cell again 2 days later to a different value of "V" will the log capture both changes?

I might be misinterpreting the code - forgive me, i'm a newbie on this.

thanks

 

[Ruddles]

The best way to see what it's doing is to try it.

As I said.

And if it's deficient in any way, I'm happy to look at changing the code to suit your requirements.

 

[Fwiz]

I've tested this and its really good, the only thing is tho - when user opens the workbook the macro runs and it records all the current data even though nothing has changed....

see example

Workbook opened by billyblogs on 12/10/2010 at 10:18:21
Cell(9,4) changed from '' to 'E'
Cell(9,5) changed from '' to 'E'
Cell(9,6) changed from '' to 'E'
Cell(9,7) changed from '' to 'E'
Cell(9,8) changed from '' to 'E'
Cell(9,9) changed from '' to 'E'
Cell(9,10) changed from '' to 'E'
Cell(9,11) changed from '' to 'E'
Cell(9,12) changed from '' to 'E'
Cell(9,13) changed from '' to 'E'
Cell(9,14) changed from '' to 'E'
Cell(9,15) changed from '' to 'E'

where as when I actually change something it it records it like this :

Workbook opened by billyblogs on 12/10/2010 at 10:20:25
Cell(19,4) changed from 'E' to 'L'
Workbook opened by areid3 on 12/10/2010 at 10:20:25
Cell(19,4) changed from 'E' to 'L'
Workbook opened by billyblogs on 12/10/2010 at 10:20:49
Cell(20,4) changed from 'E' to 'V'
Cell(20,5) changed from 'E' to 'V'
Cell(20,6) changed from 'E' to 'V'
Cell(20,7) changed from 'E' to 'V'
Cell(20,8) changed from 'E' to 'V'
Cell(20,9) changed from 'E' to 'V'

this is what I'm after, so how do I stop the macro recording all cell data? ....I only want the changes.

any suggestions?

thanks

 

[Ruddles]

When you say, 'it records all the current data even though nothing has changed" - it only does that the very first time you open the workbook, doesn't it? I mean, when you open the workbook subsequently, it doesn't log all the data then, does it? It shouldn't.

The reason it logs all the data the first time is that the audit sheet is empty the first time you set it up, so all the data looks like a change.

Try this:-

1) Open the workbook several times without changing anything. Are any data changes logged? They shouldn't be.

2) Having opened the workbook at least once, change one cell, close, then re-open. How many changes are logged. Should only be one.