Track and record changes with VBA

You can use the Worksheet SelectionChange event to capture the current value when the selection changes and then use the Change event to record the old and new values in another sheet.

Keep in mind that the Target can be multiple cells so when the selection changes so you will need to capture every cell in the selection. Also, the Target  to the Change event can be multiple cells for an action like Fill Down.

Here is a proof of concept that ignores the  issue of multiple cells being selected or changing.

Dim saveValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox "You changed cell " & Target.Row & "," & Target.Column & " from " & saveValue & " to " & Target.Value
End Sub

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

Select all Open in new window

Thank you, but this solution doesn't seem to solve my two main issues:

1. If you change on cell value from 5 to 6, it is detected, but if you undo it, the old value is not correct.

2. If you copy a range e.g. from (1,5) to (1,10) and then select (2.5) to paste the selected range, the behaviour is not correct either.

Hi, PCNOTE.

The following is from Excel 2010's Help on tracking changes...

You can use change tracking to log details about workbook changes every time that you save a workbook. This change history (...includes the name of the person who made each change, when the change was made, and what data was changed) can help you identify any changes that were made to the data in the workbook

A side-effect of tracking changes is that the file is shared. Sharing has a number of issues (not least that such files are more prone to corruption) but I'm not sure if that's the case when the file is not being simultaneously opened by more than one user.

Regards,
Brian.

Hi Brian,

Thank you.

To my surprise, the change tracking provided by Microsoft does not work fine with Undo either.

In any case, with this option  I can only have the last change.  If a cell value has been changed from "a" to "b" and then to "c" and "d". The comment will read changed from "c" to "d"; I will miss the intemediate values and I would need to store all the initial values.
It also would force me to go through the whole sheet at the end of the session collecting the changes and processing them.

The sharing option could be an issue.

I hope there is another solution.

Antonio

PCNOTE,

the change tracking provided by Microsoft does not work fine with Undo either.
Because there's no "net effect", it doesn't show the change and then the undo. However, the log does correctly reflect the result of the undo.

The comment will read changed from "c" to "d"; I will miss the intemediate values
Yes, you will miss the intermediate, but it will read changed from "a" to "d".

It also would force me to go through the whole sheet at the end of the session collecting the changes and processing them.
Please explain what it is you want to do with the information you're tracking. Also, how much changing does a user typically do in a session? With 74,000 cells being tracked are you not concerned about response times?

The sharing option could be an issue.
Agreed.

I hope there is another solution.
A form would appear to be the only option - but that's going to be awkward if the user is allowed to cut/copy+paste and to insert/delete rows/columns.

Regards,
Brian.

OK then, there must be something wrong with my configuration:

If I change A6 value from "a" to "b", the comment reads:
Changed cell A6 from 'a' to 'b'
If I change again A6 value from "b" to "c", the comment reads:
Changed cell A6 from 'b' to 'c' and not Changed cell A6 from 'a' to 'c'

Now the Undo:
Select cell E6 and type 'a' and then enter
Select cell E6 again and type '1' and then enter
Select Undo and the mesage reads:
Changed cell A6 from <blank> to 'a' an not Changed cell A6 from '1' to 'a'

Does it work OK to you?

Recording the changes is a requirement from the user. As a minimum I would need (if they accept it) to have the initial value. I have to process the changes and depending on the values, the change can trigger an action.

The amount of changes is not meant to be high, it is fully manual and they have to think and decide every change.

The form is not an option for them.

By the way, can I access this tracking information using VBA?

Thank you and regards,
Antonio

Hello Brian,

I need to get used to it but it seems I can get what I need from the Track Changes tool.

Thank you for the hint.

Regards,
Antonio

Thanks, Antonio.

Tracking Changes can feel weird at times, but it's actually very powerful and I think you'll be sorted.