none
Audit Trail in Excel RRS feed

  • Question

  • I have set up an audit trail in Excel where it tracks changes in the cells of my spreadsheet but I would like for it to also track whether cells or whole rows/columns are inserted or deleted. Can anybody help me modify my code to add this? Here's my coding:

    Dim PreviousValue


    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim i As Long
    Dim ws As Worksheet

    Set ws = Sheets("Trail")

    i = ws.Range("A" & Rows.Count).End(xlUp).Row + 1

    If Target.Value <> PreviousValue Then
        With ws
            .Range("A" & i).Value = Application.UserName
            .Range("B" & i).Value = Target.Address
            .Range("C" & i).Value = PreviousValue
            .Range("D" & i).Value = Target.Value
            .Range("E" & i).Value = Format(Now(), "mm/dd/yyyy, hh:mm:ss")
        End With
    End If

    End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        PreviousValue = Target.Value
        
    End Sub


    Thanks in advance!

    Kimberly

    Wednesday, November 20, 2019 6:12 PM

All replies

  • Hi Kimberly,

    I don't know the meaning of "cells are inserted or deleted".
    But we can know whole row(s) or whole column(s) are inserted or deleted.
         
    To realize this, we need to define the target rows and columns for checking.
    My code in [ThisWorkbook]:
       the target rows are 1-10,
       the target columns are A-J
    Private Sub Workbook_Open()
        With Sheets("Sheet1")
            Set .RowRange = .Range("1:10")
            Set .ColRange = .Range("A:J")
            ' ---
            .RowCnt = .RowRange.Rows.Count
            .ColCnt = .ColRange.Columns.Count
        End With
    End Sub

    My code in [Sheet1]:
    ' --- for Row
    Public RowRange As Range
    Public RowCnt As Long
    ' --- for Column
    Public ColRange As Range
    Public ColCnt As Long
    
    ' ---
    Private Sub Worksheet_Activate()
    End Sub
    
    ' --- check if Rows or Columns are deleted (inserted)
    Private Sub Worksheet_Change(ByVal Target As Range)
        ' --- check Row count
        If (RowRange.Rows.Count < RowCnt) Then
            MsgBox RowCnt - RowRange.Rows.Count & " rows deleted"
            RowCnt = RowRange.Rows.Count
        End If
        If (RowRange.Rows.Count > RowCnt) Then
            MsgBox RowRange.Rows.Count - RowCnt & " rows inserted"
            RowCnt = RowRange.Rows.Count
        End If
        ' --- check Columns count
        If (ColRange.Columns.Count < ColCnt) Then
            MsgBox ColCnt - ColRange.Columns.Count & " columns deleted"
            ColCnt = ColRange.Columns.Count
        End If
        If (ColRange.Columns.Count > ColCnt) Then
            MsgBox ColRange.Columns.Count - ColCnt & " columns inserted"
            ColCnt = ColRange.Columns.Count
        End If
    End Sub

    Regards,

    Ashidacchi -- http://hokusosha.com


    • Edited by Ashidacchi Friday, November 22, 2019 7:07 AM
    Friday, November 22, 2019 7:05 AM