B
bstobart
I'm in the process of creating a change log. The intention is to store any
change to any cell in the workbook, ideally including formatting changes.
Each time a cell is changed, both the old and new values should be stored.
I'm using the SheetChange workbook event. The challenge is to capture the
old value and format. I've tried two approaches:
1) Use of Undo to revert the Target to its previous value(s), capture those
values, then use Undo again to make the users change again. Here's a snippet
which should give you the idea:
' Revert to previous value(s), store that value, then switch back to
current value
Application.Undo ' Under the user's change
For Each cell In Target ' For each cell in the changed range
count = count + 1
' Precede formulas with "'", otherwise just take the .Formula value
If cell.HasFormula = False Then
logws.Cells(bottom + count, OldValCol) = cell.Formula
Else
logws.Cells(bottom + count, OldValCol) = "'" & cell.Formula
End If
Next
Application.Undo ' Redo the change that the user made.
This works, but when I tried to expand it to capture also the formatting by
using a Copy command between the two .Undo commands, I get an error. I
believe .Copy is interpreted as a user command, so the second Undo fails. So
my first question is whether there's a way to use Copy between the two
..Undos. A second question is whether there is another way to identify the
change after the SheetChange event, without usnig the two Undo commands at
all.
2) The second approach I tried was more elaborate. Whenever the
SelectionChange event fired I copied off the selection to a dummy sheet,
where it would be available for the SheetChange event to find it, if the user
made a change. This worked fairly well, but it became quite complicated.
Of course, if you happen to have a complete Change Log routine, I not too
proud to throw mine away and take someone else's. I do want both the old and
new value though. Just logging the new value would not be sufficient. On
this discussion board, the only change log I've been able to find stored only
the new value after a change.
Any ideas are welcome.
change to any cell in the workbook, ideally including formatting changes.
Each time a cell is changed, both the old and new values should be stored.
I'm using the SheetChange workbook event. The challenge is to capture the
old value and format. I've tried two approaches:
1) Use of Undo to revert the Target to its previous value(s), capture those
values, then use Undo again to make the users change again. Here's a snippet
which should give you the idea:
' Revert to previous value(s), store that value, then switch back to
current value
Application.Undo ' Under the user's change
For Each cell In Target ' For each cell in the changed range
count = count + 1
' Precede formulas with "'", otherwise just take the .Formula value
If cell.HasFormula = False Then
logws.Cells(bottom + count, OldValCol) = cell.Formula
Else
logws.Cells(bottom + count, OldValCol) = "'" & cell.Formula
End If
Next
Application.Undo ' Redo the change that the user made.
This works, but when I tried to expand it to capture also the formatting by
using a Copy command between the two .Undo commands, I get an error. I
believe .Copy is interpreted as a user command, so the second Undo fails. So
my first question is whether there's a way to use Copy between the two
..Undos. A second question is whether there is another way to identify the
change after the SheetChange event, without usnig the two Undo commands at
all.
2) The second approach I tried was more elaborate. Whenever the
SelectionChange event fired I copied off the selection to a dummy sheet,
where it would be available for the SheetChange event to find it, if the user
made a change. This worked fairly well, but it became quite complicated.
Of course, if you happen to have a complete Change Log routine, I not too
proud to throw mine away and take someone else's. I do want both the old and
new value though. Just logging the new value would not be sufficient. On
this discussion board, the only change log I've been able to find stored only
the new value after a change.
Any ideas are welcome.