Record every change in a range of cells in a column to sheet2 with the time and date of change.

Newman_Mark 

Since you do not provide any further data, such as Excel version, operating system, workbook (without sensitive data), I will send you this task and solution that you can adapt to your project.

Task:

I have a folder with 53 tables (one for each week)

- The tables are each from A1: Q550, the data are in A9: Q550

- One line = one data record.

- I would now like that if someone changes something in a line, the date, time and name of the person who made the change is in this line in column R.

- The logging should take place automatically without switching on a macro.

Answer:

It is not possible to record with macro, you have to write in the VBE (Alt + F11).

I don't like your desired solution, because only the last change in the entire data set is logged here.

Better to log every change.

1. Insert a new sheet and name it "Protocol"
2. Insert the headings: table, cell, new value, date, time, user
3. Change to the editor with Alt + F11
4. Search and mark the table "Protocol" in the ProjectExplorer (top left) and mark it.
5. Press F4, look for the property Visible in the properties window (bottom left) and change it to "xlSheetVeryHidden".
6. Mark "This workbook" in the top left.
7. Paste the following code in the right window:

Code:

Private Sub Workbook_SheetChange (ByVal Sh As Object, ByVal Target As Range)
Dim ErsteFreieZeile As Long
If Target.Count> 1 Then Exit Sub
If Sh.Name = "Protocol" Then Exit Sub
If Intersect (Target, Sh.Range ("A9: Q550")) Is Nothing Then Exit Sub
With Sheets ("Protocol")
    FirstFreeLine = .Cells (Rows.Count, 1) .End (xlUp) .Row + 1
    .Cells (ErsteFreieZeile, 1) = Sh.Name
    .Cells (ErsteFreieZeile, 2) = Target.Address (0, 0)
    .Cells (ErsteFreieZeile, 3) = Target.Value
    .Cells (ErsteFreieZeile, 4) = Date
    .Cells (ErsteFreieZeile, 5) = Time
    .Cells (ErsteFreieZeile, 6) = Environ ("username")
End With
End Sub

1. Now you should protect the code with a password:

- Right click on your VBA project, properties, protection

- Lock project for display

- Assign a password and keep it safe ...

Now a change log is created in the background, which nobody can see at first.

If you want to see it, there are two options:

1. You switch to the VBE, click on your project, enter the password and change the property of the worksheet to xlSheetVisible. It's awkward, above all you mustn't forget to undo everything afterwards ...
2. and better: You automate with VBA. For this you need two additional event routines. In the module This work folder:

Code:

Private Sub Workbook_Open ()
If Environ ("username") = "Nikolino" Then 'adjust!
    Worksheets ("Protocol"). Visible = xlSheetVisible
End If
End Sub

Here you still have to change your Windows login name (probably "Oscar").

The log is only visible to you.

With:

Code:

Private Sub Workbook_BeforeClose (Cancel As Boolean)
Worksheets ("Protocol"). Visible = xlSheetVeryHidden
End Sub

Ensures that it is made invisible again when the file is closed.

One possible result in the "Protocol" sheet:

Here you can now easily search for the change that annoys you, e.g. using an auto filter ...

Everything freshly fished from the internet 🙂

I would be happy to know if I could help.

I wish you continued success with Excel

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.