Audit Trail with VBA!

Hi all,

If it is ok to ask can someone please help me with the below.

I would like to create a kind of audit trail on my excel which basically will record the cell address of newly inputted data. How I had imagined is as follows:

There will be 2 sheets:
- a main sheet 'Home'
- a second sheet 'Audit Trail'.

The idea is that:
Step 1: every time a user types something in Sheet 'Home', a worksheet VBA will record the cell address of the newly inputted data.
Step 2: the cell address will be pasted in 'Audit Trail' in Column A.

E.g.That is, if someone types 'hello' in cell A8 of worksheet 'Home'. The macro will record A8 and paste it in 'Audit Trail'.

P.S Each time data is inserted in 'Home' the cell reference will be pasted in the last blank cell of Column A in 'Audit Trail'

Not sure if this is possible but would really appreciate a lot your help!!!

Thanks a lot guys!!
Keibri

Cheers Thanks a lot that really worked magic!!! Appreciate a lot man!!

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Myr&
    With Sheets("Audit Trail")
        Myr = .Cells(.Rows.Count, 1).End(3).Row
        If Myr = 1 Then
            .Cells(Myr + 1, 1) = Target.Address(0, 0)
        Else
            .Cells(Myr, 1) = Target.Address(0, 0)
        End If
    End With
End Sub

Cheers guys!! Also not sure if possible or if it will be difficult.

The code also records the cell address when the 'delete' button is hit.

Would it be possible to amend a little bit to distinguish the cell address of those which are entered from those which are deleted?

Not sure if it is possible or not?

Thanks a bunch really guys !

One thing to be aware of - storing the audit information in the workbook can make its size increase dramatically; especially if the workbook is heavily used. You might consider storing the change information in an external CSV file.