I have an Excel 2007 add-in with a ribbon. One of the buttons in the ribbon triggers heavy manipulations of the current worksheet and I set various Excel.Application properties like Interactive and EnableEvents to false during the manipulations for speed and UX reasons.
My problem is that the ribbon doesn't steal focus so if the user was editing a cell when he clicks my ribbon button, an exception is thrown when setting Application.Interactive = false because Excel considers the user is still editing the cell.
Is there a way to stop the edition either by saving or discarding the changes made to the cell?
It appears that Range.DiscardChanges() would probably solve my problem but it isn't available in the 2007 API.
EDIT: It seems that Range.DiscardChanges() is for ranges based on OLAP data sources so it wouldn't solve my problem anyway.
EDIT: I have tried calling Range.Activate() on another cell. While I do see the cell focus changing in the GUI, the exception is thrown anyway. Range.Select() doesn't do anything.
So after looking around for a few days and asking on MSDN, the final answer is that there is simply nothing in the API for this and that it isn't possible without using hacky solutions1.
The way Excel itself handles this kind of thing is simply to gray out its menus while the user is editing so I've done that instead for the sake of consistency with the host application.
1: Hacky, because techniques like using SendKeys require going back to the GUI to make sure the action is processed and then calling the method that required the cells to not be in edition. This is fine in one place, but doing that for every method call to the Excel Interop is madness.