Preserve Undo Capability or Delayed Writing Activity Thread so Undo stack is preserved.

[LouDog]

Hi Everyone- I am trying to create an activity log that records which cells were edited, by whom and when and the old and new values across multiple worksheets. I am able to do that without much trouble, but losing the undo functionality is a problem for us. I would like to preserve the undo functionality somehow (even if just 1-2 changes back)... OR... somehow delay the logging of the changes to occur when the workbook is closed (to allow the macro to collect the changes, but not write them which I understand is what actually disrupts the undo functionality). I'm not sure if either of these is even possible, but would appreciate any insight. Thanks in advance- my code and mini-sheets are below.

Dim oldValue As String
Dim oldAddress As String
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If ActiveSheet.Name <> "Logged Changes" Then
Application.EnableEvents = False

Sheets("Logged Changes").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & "-" & Target.Address(0, 0)
Sheets("Logged Changes").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = oldValue
Sheets("Logged Changes").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Value
Sheets("Logged Changes").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ("username")
Sheets("Logged Changes").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Now
Sheets("Logged Changes").Hyperlinks.Add Anchor:=Sheets("Logged Changes").Range("A" & Rows.Count).End(xlUp).Offset(0, 5), Address:="", SubAddress:="" & ActiveSheet.Name & "!" & oldAddress, TextToDisplay:="Link"

Sheets("Logged Changes").Columns("A:F").AutoFit
Application.EnableEvents = True
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
oldValue = Target.Value
oldAddress = Target.Address
End Sub

Track Changes Test.xlsm
	A	B	C	D
1	#	Year Built	Address	Value
2	1	2001	123 Main Street, City, OH, 12345	$1,000,000
3	2	1985	124 Main Street, City, OH, 12345	$2,000,000
4	3	1961	125 Main Street, City, OH, 12345	$3,000,000
5	4	1979	126 Main Street, City, OH, 12345	$7,000,000
6	5	2002	127 Main Street, City, OH, 12345	$6,000,000
Buildings

Track Changes Test.xlsm
	A	B	C	D
1	#	Make	Model	Year
2	1	Ford	Taurus	2000
3	2	Honda	Pilot	2018
4	3	Toyota	Highlander	2015
5	4	Jeep	Grand Cherokee	2016
6	5	Dodge	Ram	1997
Vehicles

Track Changes Test.xlsm
	A	B	C	D	E	F
1	Sheet & Cell Reference	Sheet & Cell Reference	Changed to	User	Date & Time	Back Link
2	Buildings-B4	1960	1961	pezzijo	8/13/21 4:40:14 PM	Link
3	Vehicles-D4	2014	2015	pezzijo	8/13/21 4:41:00 PM	Link
4	Buildings-D5	9000000	7000000	pezzijo	8/13/21 4:43:50 PM	Link
5	Vehicles-C3	Accord	Pilot	pezzijo	8/13/21 4:45:15 PM	Link
6	Buildings-B5	1969	1979	pezzijo	8/13/21 4:49:35 PM	Link
7	Vehicles-D6	1997	1997	pezzijo	8/13/21 5:19:04 PM	Link
Logged Changes

 

[bobsan42]

You code or data are irrelevant unfortunately.
Changing cells through VBA erases the undo stack.
There are solutions but IMO they are too complicated and not a 100% reliable.
If it's only data changes you want to undo you can log values/formulas on Selection change, then log the new values/formulas on Change and use this information for building a home-made undo option. But this will become too tedious and buildup a lot of data. Potentially will slow down productivity.
However, while I was reading your post I came up with an idea. I have not tested it but it's worth a shot. Make you logs in another workbook. You can open a second WB automatically upon opening the production one and keep it hidden. You can do it with a class as well. Log the information in it and close it upon closing the first one. Maybe rhis will allow you to preserve the Undo stack. If not successful - last resort is a similar approach, but also starting another instance of the excel application and open the Log WB in it.

 

[LouDog]

You code or data are irrelevant unfortunately.
Changing cells through VBA erases the undo stack.
There are solutions but IMO they are too complicated and not a 100% reliable.
If it's only data changes you want to undo you can log values/formulas on Selection change, then log the new values/formulas on Change and use this information for building a home-made undo option. But this will become too tedious and buildup a lot of data. Potentially will slow down productivity.
However, while I was reading your post I came up with an idea. I have not tested it but it's worth a shot. Make you logs in another workbook. You can open a second WB automatically upon opening the production one and keep it hidden. You can do it with a class as well. Log the information in it and close it upon closing the first one. Maybe rhis will allow you to preserve the Undo stack. If not successful - last resort is a similar approach, but also starting another instance of the excel application and open the Log WB in it.

I hadn't thought of that... I'll give it a shot. Thanks for the feedback.

 

[bobsan42]

First test with a simple line of code if the undo is preserved after writing to another wb and saving it. Befor rewriting your code.
It's an untested idea yet