Application.undo only works if I commit cell changes by selecting another cell with the mouse

[Mrock1]

I have been trying to get application.undo function to work in a simple worksheet_change event macro as follows:
private sub worksheet_change(byval Target as range)
Application.enableevents = false
Application.undo
Application.enableevents = true
end sub

By rights this should undo every change I make to any cell on the worksheet but it only works if I commit the cell value by click the mouse cursor on another cell or if I select a value from a data validation drop down list.

it produces a 1004 error in every other circumstance of committing an edited cell value. For example pressing ENTER, TAB or the cursor keys

I am using excel 2007

i only have the one subroutine associated with the sheet in question.

This is is making me pull my hair out because all the documentation suggests it should work. Very weird indeed.

hope someone can help

 

[CharlesChuckieCharles]

worksheet_change(byval Target as range)


The change isn't committed until you move the focus from the cell(s) (ie Excel lets you finish)

Undo say "Cancels the last user-interface action" ( not the current action)

doesn't work for VBA changes

 

[Jerry Sullivan]

That code works for me and executes the Undo when I commit a change to a cell with Tab or arrow keys.

When you get the Error-1004, what line of code is highlighted?

What other event code do you have running at the same time (including code in ThisWorkbook or Add-Ins)?

Try the code in a blank new workbook with no other workbooks opened to see if it still throws the error.

 

[Mrock1]

The error is on the application.undo command. Sounds like CCC might be on to something about the change not being committed until the focus moves to another cell. That theory fits the observed behaviour. So if the worksheet_change event triggers as the enter or tab is pressed but before the focus changes that would explain what I'm experiencing. So my next question is - is there a way to force the change to commit in the event code or an alternate event to use?

 

[shg]

The Change event fires as soon as you commit the change (via Enter, Tab, or arrow key).

 

[Mrock1]

so then...?
why does the application.undo produce the error unless I commit the change by change the cell focus with the mouse? That is. The change event fires but the application.undo errors in all other circumstances (enter or tab).
Very strange
losing more hair

 

[Mrock1]

Just to be clear. The worksheet in question only has the worksheet_change routine. There are other sheets with other worksheet_change routines and worksheet_selectionchange. There's nothing in ThisWorkbook and I have some subroutines in Module1 that are associated with checkboxes on one of the sheets. All of the worksheet event routines are declared as private.

 

[shg]

Put a workbook on box.net or dropbox and post a link.

 

[Mrock1]

OK. I feel quite stupid now. I found the problem. On one of the other sheets, I had a worksheet_calculate event routine. It seems that this was running before the change was committed with an enter or tab. I worked around this by putting an if Activesheet.name = "sheetname" around the worksheet_calculate routine so it does execute if the active sheet isn't the particular sheet the routine is associated with.

All good. Thanks for all the excellent advice.