10/18/2019 - 11:23 PM

Automatic Change Logger

Logs every change made in a worksheet

' search tags: changelog, changes, change

' Instructions for use:
' Copy & Paste code into the sheet you wish to MONITOR for changes
' Change "logSheet" variable to be the sheet you wish to send the
' log to (line 26). Point to the column of the item name (line 39)
' Set the monitoredRange (range where a change will trigger logging)
' (line 25)
Option Explicit
' ----------------------------------------------------------------
' Event: Worksheet_Change
' Purpose: Log changes in a seperate sheet when change is made in
'          range given to monitor
' Params: Range: Target
' ----------------------------------------------------------------

Dim oldValue As String
Dim itemName As String

Private Sub Worksheet_Change(ByVal Target As Range)

        Dim logSheet As Worksheet
        Dim monitoredRange As Range
\!h       Set monitoredRange = Range("J11:X250")
\!h        Set logSheet = Sheets("FcstChangeLog")
        recordChanges logSheet, Target, monitoredRange
End Sub
' ----------------------------------------------------------------
' Event: Worksheet_SelectionChange
' Purpose: Sets SKU Name based on selected cell
' Params: Range: Target
' ----------------------------------------------------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    On Error Resume Next
\!h    itemName = Cells(Target.Row, 3)
End Sub

' ----------------------------------------------------------------
' Subroutine: recordChanges
' Purpose: If changed cell is in monitored range, set the values
'          of cells in the logsheet according to changes made
' Params: Worksheet: logSheet: sheet where changes are logged
'         Range: Target: the cell being edited in the monitored
'                        range
'         Range: monitoredRange: the range of cells to watch for
'                                changes to put in the log
' By: HG
' Date: 4/1/2019
' ----------------------------------------------------------------
Private Sub recordChanges(logSheet As Worksheet, ByVal Target As Range, monitoredRange As Range)
    ' find last row in the table
    Dim lastRow As Long
\!h    lastRow = logSheet.Range("tblFcstChangeLog").Row + logSheet.Range("tblFcstChangeLog").Rows.Count
    ' determine if cell is in the range we are monitoring
    If Not Intersect(ActiveCell, monitoredRange) Is Nothing Then
        Dim newValue As String
        If Target.Value = "" Then newValue = "BLANK" Else newValue = Target.Value
        ' Set values of cells in log sheet based on changes in monitored tab
        logSheet.Cells(lastRow, 2).Value = Format(Now, "mm/dd/yyyy hh:mm")
\!h        logSheet.Cells(lastRow, 3).Value = skuName
        logSheet.Cells(lastRow, 4).Value = oldValue
        logSheet.Cells(lastRow, 5).Value = Target.Value
        logSheet.Cells(lastRow, 6).Value = Application.UserName
    End If
End Sub