Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
Solved
Track and record changes with VBA
Posted on 2012-12-27
Hi,
I need to track the changes to the cell values in a given portion of a sheet (approx 74000 cells) and store these changes in another sheet - row, column, old value, new value-.
I have tried some of the solutions I have found based on the events Change and SelectionChange but none of them work fine to me
I have problems dealing e.g with recording the result of Undo or copying a range of cells when only one cell is selected when pasting.
Do you know if there exists a robust way of tracking and recording changes?
Antonio
I need to track the changes to the cell values in a given portion of a sheet (approx 74000 cells) and store these changes in another sheet - row, column, old value, new value-.
I have tried some of the solutions I have found based on the events Change and SelectionChange but none of them work fine to me
I have problems dealing e.g with recording the result of Undo or copying a range of cells when only one cell is selected when pasting.
Do you know if there exists a robust way of tracking and recording changes?
Antonio
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
4-
4
9 Comments
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.
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
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.
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...
Regards,
Brian.
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 workbookA 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
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.
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
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
PCNOTE,
Ok, I think I understand what's happening. There are two ways that Excel can display your history. I think that you're using the "Highlight changes on screen" which just shows you the last change,.
I'm using "List changes on a new sheet". Each time I save the file the data for this list is updated. So...
I change the cell from blank to "A" and then to "B" and save the file.
I change the cell from "B" to "C" and then to "D" and save the file.
...now when I display the History it shows me two transactions...
From blank to "B"
From "B" to "D"
Edit: (You need to change "When" from its default of "Since I last saved" to "All" - or simply untick it.)
Does that help?
By the way, can I access this tracking information using VBA?
When you select "List changes..." the information is shown in a new sheet (called "History"). The following macro displays this sheet, showing all tracked changes (not just the ones in the last save)...
Regards,
Brian.
Ok, I think I understand what's happening. There are two ways that Excel can display your history. I think that you're using the "Highlight changes on screen" which just shows you the last change,.
I'm using "List changes on a new sheet". Each time I save the file the data for this list is updated. So...
I change the cell from blank to "A" and then to "B" and save the file.
I change the cell from "B" to "C" and then to "D" and save the file.
...now when I display the History it shows me two transactions...
From blank to "B"
From "B" to "D"
Edit: (You need to change "When" from its default of "Since I last saved" to "All" - or simply untick it.)
Does that help?
By the way, can I access this tracking information using VBA?
When you select "List changes..." the information is shown in a new sheet (called "History"). The following macro displays this sheet, showing all tracked changes (not just the ones in the last save)...
Sub Display_Tracking_History()
With Workbooks("Excel_Tracking_Test.xlsm")
.HighlightChangesOptions When:=xlAllChanges
.ListChangesOnNewSheet = True
.HighlightChangesOnScreen = False
End With
End Sub
Regards,
Brian.
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
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
Featured Post
Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Suggested Courses
Course of the Month4 days, 17 hours left to enroll
670 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.