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?

Answer 1

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

Answer 2

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.

Answer 3

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!  

Answer 4

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#? 

Answer 5

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.

Answer 6

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.

Answer 7

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!! :-)

Answer 8

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.

Answer 9

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.

Answer 10

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.

Answer 11

Again, thank you. I have to look into this some more; i'll keep you posted!

Answer 12

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

Answer 13

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)? 

Answer 14

Unsolved miseries.. 4 years later and still nothing.  :[  

If anyone has anything please post. 

Thanks,

-Leo