re: events/undo

[cart0250]

I have several sheets where the user enters data that must be validated upon entry. The validation is based on data held in a separate sheet. I chose to use an event b/c there were issues with people using cut/paste, drag/drop etc which over-rode the data validation and therefore made it possible to enter invalid data into those cells. It works fine, but the issue is that the Undo Excel function now doesn't work, obviously b/c the event is being triggered.

Please Login or Register  to view this content.

I am wondering if it would be possible, within the above event procedure, to check the cell to see if it contains validation and if it does disable events
using Application.EnableEvents = False. This would mean that in the vast majority of cases, the undo function would work.

[dominicb]

Good evening cart0250

Unfortunately, a side effect of running any macro is that it clears the undo stack - gthe pshot of which is you lose the undo function. The only way to check if a cell contains validation, and the only way to cancel the events is using a macro. And macros clear your stack. Sadly, you're stuck with the situation. However, you could write your own undo code - this would constantly give you a single level of undo. If this is of interest look here :

http://www.j-walk.com/ss/excel/tips/tip23.htm

Alternatively, Jan Karel Petersen has written an article incorporating a generic undo routine here :

http://www.jkp-ads.com/Articles/UndoWithVBA00.asp

Either way, this is not for the faint hearted and you may have to consider how important the undo routine is to you / your users.

HTH

DominicB