Excel VBA AUDIT LOG

[rosscoexcelmonster]

Hi Guys,

I have been trying to create a VBA change log for days now, without any luck whatsoever.

I have a workbook that contains several sheets, and I want to log every change that is made to a cell within the workbook

The information I want to log:

Username (excel username), Date, Time, Cell Change, Previous Value, New Value.



Ideally, I would like to log this on a hidden sheet within the workbook - one that cannot be edited easily.


I know am asking a lot, and I really don't like straight-up asking for things, but if any of you smart guys could help, i would be very greatful

 

[PJS1500]

I see you want to save the log in the workbook, I do not have that, but the code that I use logs them in a .txt file in whatever folder I want. So it can be hidden
Create a new macro and change the name to "Logging"
In the macro copy and paste the below code (both functions)
Now in the macro that you want to "log", you would add this line of code:
Logging.LogEvent "Name me whatever you'd like", Application.username
Hope this helps

Function LogEvent(EventName As String, Info As String)
Set FS = CreateObject("Scripting.FileSystemObject")
Set TS = FS.OpenTextFile("C:\Logs\NameMe.txt", 8, True)
TS.WriteLine expandString(Now, 30, "L") & " " & expandString(EventName, 30, "L") & " " & expandString(Info, 250, "L")
TS.Close
Set TS = Nothing
Set FS = Nothing
End Function


Function expandString(ipString As String, Length As Integer, Optional Justify As String = "L") As String
Dim strLength As Integer
Dim Spaces As String
strLength = Len(ipString)
If strLength > Length Then
expandString = Left(ipString, Length)
ElseIf strLength = Length Then
expandString = ipString
Else
Spaces = String(Length - strLength, " ")
If UCase(Left(Justify, 1)) = "R" Then
expandString = Spaces & ipString
Else
expandString = ipString & Spaces
End If
End If
End Function