11

I am considering implementing an Excel add-in using COM (not VBA) that will manipulate data in the worksheet.

I am going to need this add-in to seamlessly integrate with the Undo stack. More specifically:

  1. Any changes this add-in makes to the data need to be undoable by the user (through the standard Undo action)
  2. The items on the Undo stack before the add-in action takes place need to be preserved

In my (albeit perfunctory) research so far, it is unclear whether or not Excel can allow for this. If it does not, this is a showstopper, and the add-in will have no value.

My question: is it possible? This is more of a "yes or no" question than a "how" question, as I need to know if I am embarking on a wild goose chase or not. However, any pointers on how it can be done would be a bonus.

kes
  • 5,983
  • 8
  • 41
  • 69
  • Are you using a component for this, I've used `add-in express` and it worked well for me. – Johan Jun 22 '11 at 17:15
  • Yes, considering using add-in express. However, their demo application does not support undo, and it clears the undo stack. I am trying to find out if there is a way around this. – kes Jun 22 '11 at 17:43
  • You can ask them, their helpdesk is quite responsive. In my experience they usually answer within 24 hours. – Johan Jun 22 '11 at 17:55

3 Answers3

4

Seems like it is not possible after all.

The Application.OnUndo method clears the current undo stack and places itself on top.
There doesn't seem to be anything else related to customizing undo.

It is possible.

Application.OnUndo registers an undo sub for the currently executing sub:

sub ImMakingChanges()
  cells(1,1).interior.color = vbyellow
  application.onundo "Undo the stupid color", "RemoveMyStupidChanges"
end sub

sub RemoveMyStupidChanges()
  cells(1,1).interior.colorindex = xlnone
end sub

Obviously, saving a previous state in the wild is generally a nightmare. But here you go.

Also, your undo sub will need to be visible to the general public so that Excel can find and call it.

GSerg
  • 76,472
  • 17
  • 159
  • 346
1

Apparently, this is not possible and there are no plans to support it even in Office 2013, as per the response by an MSFT employee on this thread.

Ishan De Silva
  • 935
  • 2
  • 8
  • 22
0

Seems like it is possible after all

This can be done, but you'll have to write a pretty involved Undo handler. Here's a link that well tell you exactly
how: http://www.jkp-ads.com/Articles/UndoWithVBA01.asp

Fortunatly the link has full sourcecode and it looks ironclad so you should have no problems.
I'll be happy to hear how/if this worked for you.

PS: here's the same link in Dutch: http://www.jkp-ads.com/Articles/UndoWithVBA01NL.asp

Johan
  • 74,508
  • 24
  • 191
  • 319
  • Looked through the code, it's sort of a completely custom undo queue. That cannot seamlessly replace the default undo stack or integrate with it. That's the whole point of the question. – GSerg Jun 23 '11 at 22:25
  • More precisely, using a stack of these classes, you can easily write a sub which you can register with `Application.OnUndo`, but the user's actions will be lost anyway. – GSerg Jun 23 '11 at 22:40