[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3006
  • Last Modified:

Track and record changes with VBA

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
PCNOTE
Asked:
PCNOTE
  • 4
  • 4
1 Solution
 
tdlewisCommented:
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
 
PCNOTEAuthor Commented:
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
 
redmondbCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
PCNOTEAuthor Commented:
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
 
redmondbCommented:
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
 
PCNOTEAuthor Commented:
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
 
redmondbCommented:
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
 
PCNOTEAuthor Commented:
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
 
redmondbCommented:
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now