How to Capture log of Activities on each worksheet of a Workbook

[tera]

Hi,
My excel workbook is in share mode. I need to capture all the activities
on each worksheet by all users

 

[Otto Moehrbach]

Tera
This macro should get you started.
I assumed that you had a sheet named "Log" in which you want all the
activities data placed. I assumed the Log sheet has headers in Columns A.
The data is placed in the first empty row below the headers. Change the
"Log" sheet name in the macro to suit.
In the Log sheet, Column A has the name of the sheet in which the activity
took place.
Column B has the cell address.
Column C has the user name.
Column D has the date and time.
Note that ALL activities in ALL sheets (except the Log sheet) are logged by
this code. Come back if you need help with excluding other sheets.
This macro is a workbook event macro and must be placed in the workbook
module. To access that module, right-click on the Excel icon that is
immediately to the left of the word "File" in the menu that runs across the
top of the screen, select "View Code" in the menu that drops down. Paste
this macro into the module that is on the screen. "X" out of the module to
return to your worksheet. HTH Otto
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Dest As Range
If Sh.Name = "Log" Then Exit Sub
Set Dest = Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset(1)
Application.EnableEvents = False
Dest.Value = Sh.Name
Dest.Offset(, 1).Value = Target.Address(0, 0)
Dest.Offset(, 2).Value = Environ("username")
Dest.Offset(, 3).Value = Now
Application.EnableEvents = True
End Sub

 

[tera]

Hi Otto
This is an excellant piece of solution. Is it possible to capture log in a
different workbook which should also include before change and after change
values along with the current log columns

 

[Otto Moehrbach]

Tera
Yes, that can be done. However, that other workbook has to be open on
the same computer or you have to be able to give me (or you put it in the
code) the full path and file name of that other workbook.
Let me add something to what I said before when I was talking about
doing the log in the same workbook. That "Log" sheet can a hidden sheet.
In that way, the user will not be able to see the log. We can even make the
"Log" sheet "Very Hidden". That provides additional security and increases
significantly the Excel knowledge that the user will have to have in order
to view the log sheet. Let me know. Otto

 

[tera]

Hi Otto,
Thanks for your suggestion. we explored that. Our main idea is to capture
log in a different Excel file.
The log excel file should be located under D:\log\user_log.xls

 

[Otto Moehrbach]

Tera
I'll work up the code for you, but be aware that the code will have to
open the log file, enter the log data, save the log file, and close the log
file, for every change in the contents of any cell in any sheet in the file.
The significance of this, to you, is that there will be a small delay when
any change IS made before any other change CAN be made. This delay will be
discernable (and maybe annoying) to the user.
If the log file is already open and will remain open whenever the file to be
logged is open, let me know as the code will be much simpler if that is the
case. Otto

 

[Otto Moehrbach]

Tera
Here is the code. As written, it doesn't matter if the Log file is open
or not. The code checks if the file is open and opens it if not. But the
code does save and close the file after the log is made.
The code I first wrote for you was all in one macro. This code is in
multiple macros for ease of development. The first macro named:
Private Sub Workbook_SheetChange...............
goes in the workbook module as before.
The remaining macros all go into a regular module.
If you wish, send me an email and I'll send you a small file with all the
code properly placed. My email address is ottokmnop.comcast.net. Remove
the "nop" from this address. Otto

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Set ws = Sh
Set TheCell = Target
Call LogChange
End Sub

Option Explicit
Public ws As Worksheet
Public TheCell As Range
Dim Dest As Range
Dim wbLog As Workbook
Dim wbThis As Workbook
Dim OldValue As Variant
Dim NewValue As Variant

Sub LogChange()
Application.ScreenUpdating = False
Call GetOldNewValues
Set wbThis = ThisWorkbook
Call OpenLogFile
Call LogData
Application.ScreenUpdating = True
End Sub

Sub GetOldNewValues()
Application.EnableEvents = False
NewValue = TheCell.Value
Application.Undo
OldValue = TheCell.Value
TheCell.Value = NewValue
Application.EnableEvents = True
End Sub

Sub OpenLogFile()
Dim ThePath As String
Dim Length As Long
ThePath = "D:\log\"
On Error Resume Next
Length = Len(Workbooks("user_log.xls").Name)
On Error GoTo 0
If Length <> 0 Then
Set wbLog = Workbooks("user_log.xls")
Else
Set wbLog = Workbooks.Open(ThePath & "user_log.xls")
wbThis.Activate
End If
With wbLog.Sheets("Log")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
End Sub

Sub LogData()
Application.EnableEvents = False
Dest.Value = ws.Name
Dest.Offset(, 1) = TheCell.Address(0, 0)
Dest.Offset(, 2).Value = Environ("username")
Dest.Offset(, 3) = Now
Dest.Offset(, 4) = OldValue
Dest.Offset(, 5) = NewValue
wbLog.Close SaveChanges:=True
Application.EnableEvents = True
End Sub