Hi,
So far I did't find a solution that can keep undo/redo after running macro's. Except those for undoing the last macro, but you can't keep track of multiple undo's!!!

So I already made a macro to make a new copy of the complete workbook which should be run after every change using Sub Workbook_SheetChange.
Then add a Application.OnUndo to go back 1 workbook, where you add again a OnUndo to go back over and over up to the maximum workbooks you can and want to keep in memory.

Using Sub Workbook_BeforeSave I need to make a copy from the VBA-code in the copied workbook before saving it and close all the copies.
I wonder if one could copy the vba-code from one workbook-module to an other workbook?

Anyone?

Formula: copy to clipboard 


Sub sUndo_Keep()

   ''' To keep undo working in Excel 2010, this needs to run after any changes to the workbook adding the "on undo"

   ''' Excel's undo/redo will keep working and swaps between temperary copied workbooks, see number in titlebar...

''' !!! The original workbook needs to be saved otherwise VBA-code will be lost !!!

   Dim oActWD As Window

   Application.EnableEvents = False

   Application.ScreenUpdating = False

      Set oActWD = ActiveWindow

      winName = ActiveWindow.Caption

      pos = InStr(winName, " §") '''find out if it is the original workbook or already a copy

      If pos = 0 Then '''original worbook open

         winNr = 1

         'Application.ShowWindowsInTaskbar = False  '''only 1 visuable on the taskbar

      Else '''workbook already copied, get the number of the active copied workbook

         winNr = Val(Mid(winName, pos + 2))

         winName = Left(winName, pos - 1)

      End If

      nr = 1

      Do Until nr > Windows.Count '''delete workbooks from later saved undo's, loosing redo !

         If winNr < Val(Mid(Windows(nr).Caption, InStr(Windows(nr).Caption, " §") + 2)) Then

            Windows(nr).Close SaveChanges:=False

         Else

            nr = nr + 1

         End If

      Loop

      sheetName = ActiveSheet.Name '''remember original sheet

      ActiveWorkbook.Worksheets.Copy '''create new undo-workbook

      Sheets(sheetName).Activate  '''activate the same sheet as in the original workbook

      ActiveWindow.Caption = winName & " §" & winNr + 1 '''number the new workbook in the title §..

      'oActWD.Visible = False  '''hide previous workbook doesn't work because undo/redo can't find them !!!

   Application.EnableEvents = True

   Application.ScreenUpdating = True

End Sub