• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel VBA Audit Log

ajpclifford

New Member
New Member here so kid gloves please :) So I searched for an answer and couldn't find one.


I have vba code to log changes in an excel cells to a log sheet, however it will only log values in the cell and not take into account formulas. Anyone got an idea on how to track formulas also? I can log that data in a different column, i.e one column contains the value, the next column contains the formula.

Any help would be appreciated.

This is what my code looks like:

[pre]
Code:
Option Explicit
Public PriorVal As String

Private Sub Workbook_Open()
Dim NR As Long
With Sheets("AuditLog")
NR = .Range("C" & .Rows.Count).End(xlUp).Row + 1
Application.EnableEvents = False
.Range("A" & NR).Value = Environ("UserName")
.Range("B" & NR).Value = Environ("ComputerName")
Application.EnableEvents = True
End With
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal sh As Object, ByVal Target As Range)
If Selection(1).Value = "" Then
PriorVal = "Blank"
Else
PriorVal = Selection(1).Value
End If
End Sub

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
Dim NR As Long
If sh.Name = "AuditLog" Then Exit Sub     'allows you to edit the log sheet

Application.EnableEvents = False
With Sheets("AuditLog")
NR = .Range("C" & .Rows.Count).End(xlUp).Row + 1
.Range("A" & NR).Value = Environ("UserName")
.Range("B" & NR).Value = Environ("ComputerName")
.Range("C" & NR).Value = Now
.Range("D" & NR).Value = sh.Name
.Range("E" & NR).Value = Target.Address
.Range("F" & NR).Value = PriorVal
.Range("G" & NR).Value = Target(1).Value
NR = NR + 1
End With
Application.EnableEvents = True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim ws As Worksheet
Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
If ws.Name = "AuditLog" Or ws.Name = "Extra" Then ws.Visible = xlSheetVeryHidden

Next ws
Application.ScreenUpdating = True

End Sub

Sub ShowSheet()
Dim pword As String
pword = InputBox("Please Enter a Password", "VIP access")
If pword = Worksheets("Instructions").Range("A1") Then GoTo UnlockSheet Else: _
MsgBox "Wrong Password entered - access denied", vbOKOnly
Exit Sub
UnlockSheet: _
Application.ScreenUpdating = False
Worksheets("Extra").Visible = xlSheetVisible
Worksheets("AuditLog").Visible = xlSheetVisible
Worksheets("Extra").Activate
Application.ScreenUpdating = True
End Sub
[/pre]
 
I think you just need to modify these 2 to reference the formula object, instead of the value. Note that referencing the formula works just fine for cells that are constants, since XL treats "Bob" as the formula (confusing, but hey, it does).

[pre]
Code:
Private Sub Workbook_SheetSelectionChange(ByVal sh As Object, ByVal Target As Range)
If Selection(1).Formula = "" Then 'Changed
PriorVal = "Blank"
Else
PriorVal = Selection(1).Formula  'Changed
End If
End Sub

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
Dim NR As Long
If sh.Name = "AuditLog" Then Exit Sub     'allows you to edit the log sheet

Application.EnableEvents = False
With Sheets("AuditLog")
NR = .Range("C" & .Rows.Count).End(xlUp).Row + 1
.Range("A" & NR).Value = Environ("UserName")
.Range("B" & NR).Value = Environ("ComputerName")
.Range("C" & NR).Value = Now
.Range("D" & NR).Value = sh.Name
.Range("E" & NR).Value = Target.Address
.Range("F" & NR).Value = PriorVal
.Range("G" & NR).Value = Target(1).Formula 'Changed
NR = NR + 1
End With
Application.EnableEvents = True

End Sub
[/pre]
 
Back
Top