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

LouDog

New Member
Joined
Aug 13, 2021
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
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
ABCD
1#Year BuiltAddressValue
212001123 Main Street, City, OH, 12345$1,000,000
321985124 Main Street, City, OH, 12345$2,000,000
431961125 Main Street, City, OH, 12345$3,000,000
541979126 Main Street, City, OH, 12345$7,000,000
652002127 Main Street, City, OH, 12345$6,000,000
Buildings


Track Changes Test.xlsm
ABCD
1#MakeModelYear
21FordTaurus2000
32HondaPilot2018
43ToyotaHighlander2015
54JeepGrand Cherokee2016
65DodgeRam1997
Vehicles


Track Changes Test.xlsm
ABCDEF
1Sheet & Cell ReferenceSheet & Cell ReferenceChanged toUserDate & TimeBack Link
2Buildings-B419601961pezzijo8/13/21 4:40:14 PMLink
3Vehicles-D420142015pezzijo8/13/21 4:41:00 PMLink
4Buildings-D590000007000000pezzijo8/13/21 4:43:50 PMLink
5Vehicles-C3AccordPilotpezzijo8/13/21 4:45:15 PMLink
6Buildings-B519691979pezzijo8/13/21 4:49:35 PMLink
7Vehicles-D619971997pezzijo8/13/21 5:19:04 PMLink
Logged Changes
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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

New Member
Joined
Aug 13, 2021
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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. :unsure:
It's an untested idea yet
 

Forum statistics

Threads
1,148,282
Messages
5,745,836
Members
423,981
Latest member
ph1l

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top