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
Solved

Track and record changes with VBA

Posted on 2012-12-27
9
1,928 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
ID: 38726083
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
ID: 38726321
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
ID: 38726410
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:PCNOTE
ID: 38726515
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
 
LVL 26

Expert Comment

by:redmondb
ID: 38726551
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
ID: 38726602
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
ID: 38726644
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
ID: 38734760
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
ID: 38734921
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 Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

839 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