Audit Trail

[Spikenaylor]

My end users enter data in the sheet for a batch, then they press a button to run code, check for certain errors, then lock the cells. Audit trail is populated with Date, Time, Excel User ID, Batch Number, worksheet tab.

Sub AuditTrail(batchnumber As String)

        Sheets("Audit_Trail").Cells(65536, 1).End(xlUp).Offset(1, 0).Value = _
            " Date - " & Date & "          " & " Time - " & Time & "          " & " User - " & Application.UserName & "          Batch number Locked - " & batchnumber & "          " & " Sheet - " & ActiveSheet.Name


End Sub

If there are errors in the data, ie someone has entered 35000 instead of 30000, then a Team leader unlocks the Batch via a unlock Batch button, changes the data then locks the batch.

The unlock Batch button runs code to capture audit trail details Date, time, Excel User ID, Batch Number unlocked, Reason for Unlocking, worksheet tab.

Sub AuditTrail(batchnumber As String)
Recheck:
batchunlockmessage = InputBox("Please enter the reason you are unlocking the Batch", "Unlock Batch Reason")
If batchunlockmessage = vbNullString Then
MsgBox ("You must enter a reason for unlocking the batch!")
GoTo Recheck
End If


        Sheets("Audit_Trail").Cells(65536, 1).End(xlUp).Offset(1, 0).Value = _
            " Date - " & Date & "          " & " Time - " & Time & "          " & " User - " & Application.UserName _
            & "          Batch number Unlocked - " & batchnumber & "         Reason for Unlocking - " & batchunlockmessage _
            & "          " & " Sheet - " & ActiveSheet.Name

End Sub

I now need to capture the data actually changed during the unlock and lock actions.

I have this code for Audit trail, but not sure how to implement alongside only during the unlock and lock actions.

Dim PreviousValue
 
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Value <> PreviousValue Then
        Sheets("Audit_Trail").Cells(65000, 1).End(xlUp).Offset(1, 0).Value = _
            Application.UserName & " changed cell " & Target.Address _
            & " from " & PreviousValue & " to " & Target.Value & " In Sheet " & ActiveSheet.Name
    End If
End Sub

Any guidance or help appreciated.
regards
Spikenaylor

 

[ScottR]

One suggestion would be to use global variables to store your custom states (ie locked,unlocked etc). Use the lock and unlock button codes to set these states and then look for them in your Worksheet_Change code before executing anything.