0

Currently I am creating an Excel workbook that is Macro Enabled and after reaching completion, realized my undo button is disabled. I have searched this site and many other forums for a solution but have been unable to find code that fits my situation. The workbook consists of a home page sheet and four "template" sheets that are very hidden. A user can enter a name into a text box on the home page and a click a command button that will in turn create copies of the templates and save them as the name entered.

I managed to make everything work without any code in the "This Workbook" sheet or creating any macros, but since I use the Worksheet_SelectionChange(...) event and other VBA code, the undo stack is erased constantly.

Also I would like to note that trying to fix the problem by creating copies of the worksheets and saving any changes to them does not work either. Since my project relies on copies to work already, It makes this method obsolete.

Ultimately, all I need is code that will undo user actions (ex: text edits). I do not need to undo any VBA commands. Any help on how to make this possible would be greatly appreciated!

Community
  • 1
  • 1
B. Hart
  • 1
  • This link from MSDN also references to JKP's Undo class: https://social.msdn.microsoft.com/Forums/en-US/9ef5ac45-9c62-4f17-a074-e9e8326941b0/capture-undo-history-before-event-in-excel?forum=exceldev In short, there is not an "easy" way to implement this sort of thing, though JKP solution might work for you I am not otherwise familiar with it, and have always accepted the built-in limitation of the undo stack. Otherwise, you're trying to program and persist an indeterminate amount of "state" *before* your application runs. – David Zemens Jul 31 '17 at 18:12
  • 1
    I believe https://stackoverflow.com/q/7004754/11683 is not the correct duplicate. It talks about undoing built-in Excel commands from VBA. The OP wants to make sure [the undo stack is preserved](https://stackoverflow.com/q/339228/11683) after running a macro. – GSerg Jul 31 '17 at 18:13
  • The user makes a manual edit, which triggers the `_Change` event procedure, which clears the undo stack. You want to be able to revert the manual edit that preceded the event? How far back in the stack do you need to go? – David Zemens Jul 31 '17 at 18:34
  • @B.Hart it seems as though this is actually a somewhat commonly asked question. From what I can tell, there is no way of capturing and restoring the Undo stack. Your best bet is to capture the state of the worksheet before a macro call, but this seems relatively inefficient. Depending on what you are trying to allow the user to preserve, you can try creating your own `stack` of sorts that would allow them to go back `X` steps (create a variable that stores the states for each step) and then a button that effectively uses an index to go back through these stored states. – Brandon Barney Jul 31 '17 at 18:45
  • @DavidZemens The first sheet of the template has two command buttons captioned "Add Sheet" and "Delete Sheet". Each time a user decides to add or delete, the three hidden sheets will one at a time either show or hide themselves. I was hoping for the stack to remember all manual edits as far back as the last time one of those command buttons was clicked. – B. Hart Jul 31 '17 at 18:47
  • OK so this isn't probably going to be easy, but assuming you can limit the scope of this to a handful of cells, and otherwise limit the type of actions you need to keep track of, you might be able to whip up your own version of the stack using a [`CreateObject("System.Collections.Stack")`](https://msdn.microsoft.com/en-us/library/system.collections.stack(v=vs.110).aspx) object - you'll need to capture each cell's initial value, and then `Push` to the stack on the `Worksheet_Change` event (push an array containing Address *and* Value). May be tricky to fully integrate this, though. – David Zemens Jul 31 '17 at 19:34

0 Answers0