Hello Everyone,

I have been researching writing an Audit Trail macro for Excel 2007 and between several posts in this web site and others, I came up with a macro that works for my purposes (at bottom), except for one item that might not be p0ssible to perform. I want to add a reason for the change, which preferrably pop-up at the save workbook stage and automatically go into the reason column of the "Audit Trail" worksheet for each change that was performed by that user before saving. My current macro is as follows, but I am not opposed to changing it completely if I need to to accoplish my needs:
Dim PreviousValue As Variant

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

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NR As Long
If Intersect(Target, Range("A1:DW400")) Is Nothing Then Exit Sub
With Sheets("Audit Trail")
.Unprotect Password:="xyz"
NR = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("A" & NR).Value = Target.Address(False, False)
.Range("B" & NR).Value = ActiveSheet.Name
.Range("C" & NR).Value = Now
.Range("D" & NR).Value = Environ("username")
.Range("E" & NR).Value = PreviousValue
.Range("F" & NR).Value = Target.Value
.Protect Password:="xyz"
End With
End Sub
The reason would go in column G, but as I stated before, I am not married to this macro and will use anyone that works. Thank you in advance for your help.

Gene