none
Capture undo history before event in excel

    Question

  • It is a known problem/bug that using event handlers in excel kill the undo buffer, I searched the internet for a solution but could not find it. So I was wondering if i could just capture the undo buffer as the first thing in my event; do what ever I want in my handler and then place the buffer back to where it belongs. Or is maybe this bug solved in excel 2010?
    Friday, April 13, 2012 12:19 PM

Answers

  • Unfortunately, I must agree that it is not documented very well.

    To get the listcount of the undo list, use this (Giving you VB.Net Code - Please convert it to C#)

    xlApp.CommandBars("Standard").Controls("&Undo").Control.ListCount

    You can store the list in an array by using this code

                For i As Integer = 1 To xlApp.CommandBars("Standard").Controls("&Undo").Control.ListCount
                    UnDoList(i) = xlApp.CommandBars("Standard").Controls("&Undo").Control.List(i)
                Next

    But I am not sure if there is any way you can assign this list back to Excel as the values in the list are plain "string"

    Jan Karel Pieterse has created an undo class in VBA. See if you can convert that into C#?

    Topic: Creating An Undo Handler To Undo Changes Done By Excel VBA

    Link: http://www.jkp-ads.com/articles/undowithvba00.asp


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

    Tuesday, April 17, 2012 12:45 PM
    Moderator

All replies

  • It is a known problem/bug that using event handlers in excel kill the undo buffer

    That's a misunderstanding. Almost all code, whether from an event handler or any other procedure, that changes the interface in some way will clear the Undo stack. If the code doesn't end up changing anything the stack will remain unaffected.

    A typical use of a workbook event handler is to write new data and/or formatting to cells. Very often that's unnecessary because it only re-writes what was already there. To minimise loss of Undo make this your approach

    If myCell <> some-condition then myCell = some-condition

    As for Undo/Redo you can create one level which will call a specified Undo or ReDo procedure. Look at Application.OnUndo .OnRepeat, also see Application.Undo & .Repeat

    What you could do is store the existing scenario (if not too much) for use with OnUndo before making any changes.

    Peter Thornton

    Friday, April 13, 2012 2:21 PM
    Moderator
  • I agree with Peter on this. Whatever action that you perform in Excel Spreadsheet is stored in a list which is called the Undo List. So when you do an Undo for the first time, Excel refers to this list and then Undoes the last action that you performed. Also if you want to get the Undo list then you can use this as well

    Application.CommandBars("Standard").Controls("&Undo").List(1)

    .List(1) refers to the 1st item in the list.

    Edit

    Here is one post where I have used the above.

    Topic: VBA Excel–Allow Paste Special Only
    Link: http://siddharthrout.wordpress.com/2011/08/15/vba-excelallow-paste-special-only/



    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.



    Friday, April 13, 2012 3:28 PM
    Moderator
  • Thanks for both of your replies. I think I'm understanding it better; I do not change values in cells in my event handler, I mark cells and rows as modified by adding hatching and border colors. But this too is of course a change in the interface.

    I'll dig into the suggestions you supplied me with and will come back with my findings!  

    Monday, April 16, 2012 8:00 AM
  • I looked into Undo() and Repeat() but those functions (as far as I could see) only executed an undo or redo operation, that is not what I'm looking for... I only want to copy the undo buffer; this should be a list somewhere - and after my coloring of borders and hatching of excel rows, i just want to place this list back into the undo buffer.

    So the thing Siddharth Rout is talking about looks more to what I need but it is VBA... not C#. I've search the internet for a C# version of 

    Application.CommandBars("Standard").Controls("&Undo").List(1)

    but i couldn't find it...

    Is there anyone who has some experience with this in C#? 

    Monday, April 16, 2012 3:42 PM
  • Unfortunately I am not good with C# and I tried converting the vb.net code to c# but was surprised to see that there was no .LIST property in C#... Probably I was doing something wrong?

    VB.Net Code

    Imports Excel = Microsoft.Office.Interop.Excel
    
    Public Class Form1
    
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            Dim xlApp As New Excel.Application
            Dim xlWb As Excel.Workbook
            Dim xlsheet As Excel.Worksheet
    
            With xlApp
                .Visible = True
    
                '~~> Add a new workbook
                xlWb = .Workbooks.Add
    
                xlsheet = xlWb.Sheets("Sheet1")
    
                MessageBox.Show("Now Type something in cell A1 and then click ok in this messgaebox")
    
                MessageBox.Show(xlApp.CommandBars("Standard").Controls("&Undo").List(1).ToString())
            End With
        End Sub
    End Class

    Snapshot


    I tried this C# Code but as you can see in the snapshot the .LIST doesn't exists!


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

    Monday, April 16, 2012 5:24 PM
    Moderator
  • Courtesy Peter Ritchie:

    You have to use this code and it will then work in C#

    dynamic x = xlexcel.CommandBars["Standard"].Controls["&Undo"];
    
    MessageBox.Show(x.List[1]);


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

    Monday, April 16, 2012 7:14 PM
    Moderator
  • Thanks Sid!

    That 'dynamic' piece was the key to the puzzle ;-)

    this is what i've got so far:

    dynamic cbcStandardControl = MyExcelApp.CommandBars["Standard"].Controls[14];
    HashSet<string> hUndoBufferItem = new HashSet<string>();
    int iUndoBufferIterator = 1;
    
    try
    {
    while (cbcStandardControl.List[iUndoBufferIterator] != null)
    {
    hUndoBufferItem.Add(cbcStandardControl.List[iUndoBufferIterator]);
    iUndoBufferIterator++;
    }
    } 
    catch 
    {
    }
    
    foreach (string sUndoItem in hUndoBufferItem)
    {
    sDebug += "[" + sUndoItem + "]\n";
    }

    It works, meaning that I've got my list of undo's now i hope i can place them back into the undo list after I'm done... I'm also slightly agitated because I couldn't find out how to check for the size of the list, I had to use a try catch... a bit ugly i think... And while we're at it, I couldn't use "&Undo"; I now accessed the undo control by it's index. But it could go wrong perhaps if a user reorders his controls, am I right?

    Thanks a lot for the help!! :-)


    • Edited by XzJ012wQ Tuesday, April 17, 2012 12:11 PM
    Tuesday, April 17, 2012 9:50 AM
  • Yes you have to use Try/Catch else the code will stall if the list is empty. So it is not ugly. It is the right way :)

    I am surprised that you couldn't use "&Undo". The code that I gave above is tested and it lets you use

    dynamic x = xlexcel.CommandBars["Standard"].Controls["&Undo"];

    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

    Tuesday, April 17, 2012 10:22 AM
    Moderator
  • I'm been busy with experimenting... It seems the only way to do anything with this list is by reading from it like the way it is been bone in the previous threads. I tried List.Add, List.Count, I tried capturing the complete List like this:

    object lUndoBuffer = MyExcelApp.CommandBars["Standard"].Controls[14].List;

    and placing it back like this:

    MyExcelApp.CommandBars["Standard"].Controls[14].List = lUndoBuffer;

    But this also doesn't seem to be working. This List doesn't seem to be a C# List object at all. Does anybody know how this works? been trying to find some documentation on the internet but I couldn't find anything.



    • Edited by XzJ012wQ Tuesday, April 17, 2012 12:12 PM
    Tuesday, April 17, 2012 12:10 PM
  • Unfortunately, I must agree that it is not documented very well.

    To get the listcount of the undo list, use this (Giving you VB.Net Code - Please convert it to C#)

    xlApp.CommandBars("Standard").Controls("&Undo").Control.ListCount

    You can store the list in an array by using this code

                For i As Integer = 1 To xlApp.CommandBars("Standard").Controls("&Undo").Control.ListCount
                    UnDoList(i) = xlApp.CommandBars("Standard").Controls("&Undo").Control.List(i)
                Next

    But I am not sure if there is any way you can assign this list back to Excel as the values in the list are plain "string"

    Jan Karel Pieterse has created an undo class in VBA. See if you can convert that into C#?

    Topic: Creating An Undo Handler To Undo Changes Done By Excel VBA

    Link: http://www.jkp-ads.com/articles/undowithvba00.asp


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

    Tuesday, April 17, 2012 12:45 PM
    Moderator
  • Again, thank you. I have to look into this some more; i'll keep you posted!
    Wednesday, April 18, 2012 1:33 PM
  • Below the code in C#. It works fine :

                Microsoft.Office.Core.CommandBar commandBar = Globals.ThisAddIn.Application.CommandBars["Standard"];
                Microsoft.Office.Core.CommandBarControl undoControl = commandBar.Controls["Ann&uler"];
    
                dynamic x = undoControl;
    
                string listString = "";
                for (int i = 1; i <= x.ListCount; i++)
                {
                    listString = listString + x.List[i] + "\n";
                }
                System.Windows.Forms.MessageBox.Show(listString);
    

    The only things to know is that the List store only dynamic strings.

    I hope this help.

    Arnith


    Monday, August 27, 2012 1:33 PM
  • I realize this is a bit of an old thread but has anyone yet had success inserting the list of undo actions back into the stack, either in c# or vb (preferably VBA since I don't know .NET that well)? 
    Sunday, April 06, 2014 2:42 PM
  • Unsolved miseries.. 4 years later and still nothing.  :[  

    If anyone has anything please post. 

    Thanks,

    -Leo


    Monday, July 18, 2016 11:16 PM