Answered by:
Capture undo history before event in excel

-
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?
Question
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.
- Edited by Siddharth RoutModerator Tuesday, April 17, 2012 12:46 PM Formatting went for a toss...
- Proposed as answer by Bruce Song Monday, April 23, 2012 7:58 AM
- Marked as answer by Asadulla JavedEditor Thursday, August 23, 2012 2:52 PM
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
-
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.
- Edited by Siddharth RoutModerator Friday, April 13, 2012 3:31 PM
-
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!
-
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#?
-
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.
-
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.
-
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
-
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.
-
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
-
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.
- Edited by Siddharth RoutModerator Tuesday, April 17, 2012 12:46 PM Formatting went for a toss...
- Proposed as answer by Bruce Song Monday, April 23, 2012 7:58 AM
- Marked as answer by Asadulla JavedEditor Thursday, August 23, 2012 2:52 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
-
-
Unsolved miseries.. 4 years later and still nothing. :[
If anyone has anything please post.
Thanks,
-Leo
- Edited by Leo Gurdian Monday, July 18, 2016 11:19 PM