Solved

Track and record changes with VBA

Posted on 2012-12-27
9
1,461 Views
Last Modified: 2013-01-01
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
0
Comment
Question by:PCNOTE
  • 4
  • 4
9 Comments
 
LVL 10

Expert Comment

by:tdlewis
Comment Utility
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

Open in new window

0
 

Author Comment

by:PCNOTE
Comment Utility
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.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
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.
0
 

Author Comment

by:PCNOTE
Comment Utility
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
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 26

Expert Comment

by:redmondb
Comment Utility
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.
0
 

Author Comment

by:PCNOTE
Comment Utility
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
0
 
LVL 26

Accepted Solution

by:
redmondb earned 300 total points
Comment Utility
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)...
Sub Display_Tracking_History()

With Workbooks("Excel_Tracking_Test.xlsm")
    .HighlightChangesOptions When:=xlAllChanges
    .ListChangesOnNewSheet = True
    .HighlightChangesOnScreen = False
End With

End Sub

Open in new window

With the possible exception of the sharing nuisance, I think that tracking does everything you need.

Regards,
Brian.
0
 

Author Comment

by:PCNOTE
Comment Utility
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
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, Antonio.

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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now