Search code examples
excelvstooffice-interop

Determine if Popup is in foreground of Excel

I'm writing a VSTO add-in for Excel and I have noticed that if I lock the sheet and password protect it (so only my add-in can write to it but the user can view it), if the user tries to edit the sheet they get the "This sheet is locked" popup. If while that prompt is still pending user input, the add-in tries to write to the sheet, Excel crashes. The act of writing to the sheet involves unprotecting it, writing the data, then locking it again. The Add-in captures data from an external source via the serial port so data could be written at any time.

To recreate: 1. Lock sheet with add-in. 2. User attempts to edit sheet contents 3. User is prompted that they cannot edit the contents of the sheet because it is locked. 4. Data comes in the serial port and the add-in attempts to unlock, write data, and lock the sheet before the user has a chance to ack the prompt that was thrown. 5. Excel bytes the dust.

Any suggestions? I was toying with the idea of maintaining a hidden "master" sheet and the visible sheet and just using an excel formula or named range to reference the hidden sheet. However this would then be open to editing and potential data corruption by a user. The data must be as un-editable as possible.

Update: I would be satisfied with catching the COMException so it doesn't kill excel, however the general "catch (Exception ex)" doesn't seem to help.

Solution

  • The issue was apparently

    catch(Exception)

    does not function as a general catch all when it comes to a COMException. Adding the line

    using System.Runtime.InteropServices;

    and then adding

    catch(COMException)

    to my try...catch blocks allows me to handle the errors better. Mix that with some code to retry the failed action a few times after using

    SendKeys.SendWait("{ESC}");

    seems to make things better. However, blindly sending the escape key to the forefront program can cause some issues however I cannot sit in an endless loop waiting for a user who may or may not be paying attention to clear up the issue on the screen. It seems to cause the serial port to stop buffering data and just dropping it if it's not handled fast enough.