+ Reply to Thread
Results 1 to 7 of 7

VBA Undo & Excel's built-in Undo

  1. #1
    Forum Contributor
    Join Date
    08-09-2005
    MS-Off Ver
    2003 & 2007
    Posts
    111

    VBA Undo & Excel's built-in Undo

    I'm trying to make UNDO work with a conditional-formatting VBA routine. I've written a Worksheet_Change routine and I've figured out how to undo the formatting that my own code creates. But after that I don't know how to undo the change that triggered the Worksheet_Change Event. Excel's pre-existing Undo stack has been cleared, so it's impossible to back-up any further. Can anyone help? Or is what I'm trying to do impossible without completely reimplementing Excel's Undo functionality myself?

    In case it helps, here's my code. In a module, some global variables and the undo formatting routine:
    Please Login or Register  to view this content.
    In the Worksheet code, the conditional formatting routine:
    Please Login or Register  to view this content.
    Also, I'm already using Excel's built-in conditional formatting for something else in the same range, so that's not a solution.

  2. #2
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile

    Good afternon dlh

    As you've found out, running virtually any macro has the unfortunate side effect of clearing the netire undo stack, which is something of a limitation of the Worksheet_Change event procedure. the simple answer is, you'll have to code your own undo routine, which can be called from the Edit > Undo menu command, but this wil leave you with only one undo level, not the 14 (or more) you currently enjoy. Have a look at the links to tel you a bit more :

    http://www.j-walk.com/ss/excel/tips/tip23.htm
    http://www.jkp-ads.com/Articles/UndoWithVBA00.asp

    HTH

    DominicB
    Please familiarise yourself with the rules before posting. You can find them here.

  3. #3
    Forum Contributor
    Join Date
    08-09-2005
    MS-Off Ver
    2003 & 2007
    Posts
    111
    Indeed, that's what I feared. There really is no way of preventing the Worksheet_Change Event from clearing the Undo stack? When writing my own UNDO procedure, I only get *half* of an undo: I can undo my own VBA code's work, but I cannot undo the user's change which triggered it. Is my understanding here correct?

    An idea: is there any way of accessing the Undo stack, saving all its contents with my own global variables and then refilling the stack at the end of my Worksheet_Change Subroutine?

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    The Change event itself does not flush the Undo buffer; in fact, it can Undo the change that triggered the event and still preserve the Undo buffer, and it can do anything else EXCEPT change the workbook. Changing anything in the workbook (except for the specific example given) will flush the buffer.

  5. #5
    Forum Contributor
    Join Date
    08-09-2005
    MS-Off Ver
    2003 & 2007
    Posts
    111
    So within a Worksheet_Change subroutine you can use the Undo Buffer with an Application.Undo, but is there any way to read (and store) the contents of the buffer? The structure must exist, whether it's documented or not. True? (Or is that too naive an understanding of how Microsoft's VBA support works?)

    By the way, I'm extremely grateful to each of you for your posts and attention. Thank you!

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    ... but is there any way to read (and store) the contents of the buffer?
    Not to my knowledge, and I can imagine why: it would be a nuisance to document, and MS would have to maintain some level of compatibility across generations for a feature desired by 0.001% of users.

    If they wanted to expose something, I'd much prefer that it be the equation parser -- that would bring joy to 0.002% of users.

    Quote Originally Posted by shg
    Changing anything in the workbook (except for the specific example given) will flush the buffer.
    I should have clarifed that to add to my knowledge. I used a change event to make what I hoped was a gentle change (adding a crosshatch pattern to a named style, to indicate that certain cells using that style was no longer valid). Alas, that dumped the buffer, so I ditched it.
    Last edited by shg; 12-29-2007 at 12:16 PM.

  7. #7
    Forum Contributor
    Join Date
    10-30-2007
    Location
    Norway
    MS-Off Ver
    MS Office 2007
    Posts
    345
    When writing my own UNDO procedure, I only get *half* of an undo: I can undo my own VBA code's work, but I cannot undo the user's change which triggered it. Is my understanding here correct?
    I have been working on a problem like this myself the last couple of days, and have made a one level undo routine that can undo the user's change that triggered the Worksheet_Change event. Maybe this can help you.

    I have a range called "rngInput" in Worksheet(1).
    I use Worksheet(3) to store a undo backup.
    The way it works is that I store a snapshot of "rngInput" values in an array. Then do Application.Undo just to get a "picture" of what I have to restore if the user wants to do a undo later. This is copied to my backupsheet. I then put the values i stored in my array back into "rngInput", to get back to the state I had before i did to undo.
    This code is run from the Worksheet_Change event before I run my routine that handles the formatting.

    Here is the code:
    Please Login or Register  to view this content.
    Here is my routine that actually performs the undo action when the users requires it:
    Please Login or Register  to view this content.
    I have made a Command button on the worksheet the users can use when they wants to do the undo action. The used can have made changes to the worksheet outside the "rngInput" range, and this would not have triggered any VBA that flushed the Undo buffer. The command button then uses the ordinary undo routine. If the undo buffer is empty, my custom routine is run.
    Here is the code for the button:
    Please Login or Register  to view this content.
    Running the CommandButton1_Click routine would normally flush the undo stack before you can do an Application.Undo , but if you set the TakeFocusOnClick property to False, the stack is not flushed.

    My undo code only takes care of the values. This is enough for me because the formatting and datavalidation is already taken care of in the routines that required me to make this undo routine in the first place. I think this might be the same in your case.

    Hope this can be to any help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1