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

Mrock1

New Member
Joined
Oct 7, 2014
Messages
23
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

CharlesChuckieCharles

Well-known Member
Joined
May 10, 2011
Messages
2,153
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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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

New Member
Joined
Oct 7, 2014
Messages
23
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Mrock1

New Member
Joined
Oct 7, 2014
Messages
23
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

New Member
Joined
Oct 7, 2014
Messages
23

ADVERTISEMENT

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

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
Put a workbook on box.net or dropbox and post a link.
 

Mrock1

New Member
Joined
Oct 7, 2014
Messages
23
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,550
Messages
5,529,472
Members
409,884
Latest member
Msinmath
Top