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

Mrock1

Board Regular
Joined
Oct 7, 2014
Messages
77
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
The Change event fires as soon as you commit the change (via Enter, Tab, or arrow key).
 
Upvote 0
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 :(
 
Upvote 0
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.
 
Upvote 0
Put a workbook on box.net or dropbox and post a link.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top