Record changes to Excel File

[Lensmeister]

Morning all,

I have worksheet with about 50+ sheets that is on our server. I need to be able to see who changed what cell and on what sheet and when.

I found this code on here:

Option Explicit

Const intUsernameColumn = 1
Const intCellRefColumn = 2
Const intNewValueColumn = 3
Const intTimestampColumn = 4

Private Sub Worksheet_Change(ByVal Target As Range)

Dim shtLog As Worksheet
Dim cll As Variant
Dim lngNextRow As Long

Set shtLog = ThisWorkbook.Sheets("Log")

For Each cll In Target.Cells

lngNextRow = shtLog.Cells.Find(What:="*", After:=[A1], Searchorder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

shtLog.Cells(lngNextRow, intUsernameColumn).Value = Environ("username")
shtLog.Cells(lngNextRow, intCellRefColumn).Value = cll.Address
shtLog.Cells(lngNextRow, intNewValueColumn).Value = cll.Value
shtLog.Cells(lngNextRow, intTimestampColumn).Value = Format(Now, "dd-mmm-yy hh:mm:ss")

Next cll

End Sub

Initially it worked but just shows the cell reference number. Also it has to be pasted into each sheet in the workbook.

Is there another way to do this so that the code is in a module and logs to the "log" sheet with the sheet name as well please?

Many thanks in advance

 

[wideboydixon]

Try this in the "ThisWorkbook" module:

Option Explicit

' The columns for the log sheet
Private Const UserColumn = 1
Private Const SheetColumn = 2
Private Const AddressColumn = 3
Private Const ValueColumn = 4
Private Const TimestampColumn = 5
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim logSheet As Worksheet
Dim changedCell As Range
Dim nextRow As Long

' Ignore changes to the log sheet
If Sh.Name = "Log" Then Exit Sub

' Suppress events since we're changing a sheet here
Application.EnableEvents = False

' Get a handle to the log sheet or create it if it's not there
On Error Resume Next
Set logSheet = Sheets("Log")
If logSheet Is Nothing Then
    Set logSheet = Sheets.Add(After:=Sheets(Sheets.Count))
    logSheet.Name = "Log"
    logSheet.Cells(1, UserColumn).Value = "Username"
    logSheet.Cells(1, SheetColumn).Value = "Sheet"
    logSheet.Cells(1, AddressColumn).Value = "Cell"
    logSheet.Cells(1, ValueColumn).Value = "New Value"
    logSheet.Cells(1, TimestampColumn).Value = "Timestamp"
    Sh.Activate
End If

' Find the next row
nextRow = logSheet.Cells(logSheet.Rows.Count, UserColumn).End(xlUp).Row + 1

' Log all changed cells
For Each changedCell In Target
    logSheet.Cells(nextRow, UserColumn).Value = Environ("username")
    logSheet.Cells(nextRow, SheetColumn).Value = Sh.Name
    logSheet.Cells(nextRow, AddressColumn).Value = changedCell.Address
    logSheet.Cells(nextRow, ValueColumn).Value = changedCell.Value
    logSheet.Cells(nextRow, TimestampColumn).Value = Format(Now, "dd-mmm-yy hh:mm:ss")
    nextRow = nextRow + 1
Next changedCell

' Re-enable events
Application.EnableEvents = True

End Sub

WBD

 

[Lensmeister]

Thanks for the response.

I have pasted it into the ThisWorkbook and saved it and closed and opened it. It has not recorded any of the changes I made as test.

 

[wideboydixon]

Go to this line in the macro:

Set logSheet = Sheets("Log")

Press [F9] to set a breakpoint and then go and edit a cell on one of the sheets. Does the breakpoint get triggered? If not, perhaps events are disabled. Go the VBA editor, press Ctrl+G to bring up the immediate windows and type the following:

Application.EnableEvents = True

Then hit return. Try again.

WBD

 

[Lensmeister]

Thanks.

I set break point and nothing logged

Pressed Ctrl+G and typed in the application bit.

Pressed return.

Tried a test nothing logged.

Thanks for helping

 

[Lensmeister]

OK ....

I had a bit of VBA already in the ThisWorkbook. Cut it out and pasted below the code you provided and BINGO ... it now works .... How strange !!!

Thanks

 

[wideboydixon]

Ah. Glad it's working now

WBD