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]
[/pre]
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