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

Copper Contributor

Hi, 

I need help. I need to record the changes in a range of cells in a single column to a new sheet with the time of the change and next change on a new cell.

record changes on Sheet1, Column A, Cell A5:A1005

save changes on Sheet2 from cell A5 to row 5 in Sheet2, starting with B5 and next change on C5 with time as the header and same with other cells like from cell A6 to row 6.

Please help me, I am stuck on this problem for a long time. 

If additional information is needed kindly let me know.

 

 

6 Replies

@Newman_Mark 

But the value in B5 on the new sheet could refer to another time than the value in B6, B7 etc.

Can you tell us what the new sheet should actually look like?

@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:

AAAA4.JPG

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.

 

 

@Hans Vogelaar 

I have shared an attachment for example. Hope it helps..

In the new sheet, the details for the cells on sheets1 should be saved horizontally. as on the old sheet, all data are stored in a column.

If time cannot be placed commonly for all changes, then maybe an option if changes after every min could be recorded. 

 

Thank you @NikolinoDE,

I am sorry I did not share any reference data earlier. I believe you would be able to help me out. I have shared a file for reference.

 

I would like to record the changes that happen after every min for every row(user). The final goal is to plot the changes in price/values in a chart and refresh the data once every week.

 

High hopes with you :)

@Newman_Mark 

The attached example will record the values of column A on Sheet1 every minute, whether a change has occurred or not.

It is a macro-enabled workbook, so you have to allow macros for it to work.

There is code in a standard module and in the ThisWorkbook module.

Thanks,@Hans Vogelaar 

 How do I do it?

what if it's on the standard excel? I can change it.