Excel - changes and sheet protection

Excel - changes and sheet protection

• Haplo Peart

  Hi Don,

  To understand more about the problem, could you please provide details such as code snippet of binding and repro steps on drag/edit listobject with/out sheet protection

  Thanks

  -Qiong Ou

   

  
  

• Gerhard Schneider - MSFT

  Thanks, Qiong... I was beginning to lose hope and had been deciding that perhaps this is feature we just have to live with...

  It is not easy for me to post the source code but I can try to explain what we are doing and hopefully this will allow you to help us.

  Basically the solution we are developing is a workflow modelling application. We are using Excel as the UI and VSTO 2005 components to a complex model that create/maintain  the model. OO allows us to model flow of "items" through work areas. Bascially

  Anyway, we impose a "view" (VSTO 2005) over the model for a given work area and the view is bound to the ListObject which renders the the data. We basically have items coming in 10 minute timeslots over a period (say a shift). The more resources you assign, the greater the number of items you can process but you want resource so that you don't have too much processing capacity nor too little. The processed items, depending type make flow into another work area(s) or output as processed... There are lots of rules which makes the model complex and we rely heavily on events to update the model. The user changes the resource figures at a given timeslot, the property change event is caught and we then use it to raise events to recalculates the capacity, the output, items carried forward to the next slot and cascade the calculation down the timeslots and the model.

  We protect the sheet so that user can only select and change certain range (columns) of data in the ListObject. Nothing unusual, you think... The trouble is that we then see exceptions when we use events to update the model. The trouble with VSTO/Excel is that exception handling from what we have seen is poor. We think it is the interop layer but we cannot pinpoint. Anyway, the effect is that the eventing stops and the model is left in a inconsistent state. Sometimes, the effect is that VSTO component is dumped from Excel and nothing happens. This is happens consistently when events are fired asynchronously or parallel... Please note that an event will cause a recalculation of the a value which will in turn may cause an update event etc etc etc.

  Funny thing is that, the minute you take the sheet protection off, ListObject data is updated, and we do not see exceptions on the events... BTW, the exception relates to cross-thread invocation of method. We tried implementing ISynchronizeInvocation with synchronizer but it seems to make no difference. Please note that the exception does not happen when one updates a cell at a time giving plenty of time in bewteen for the model to update and ListObject to refresh (a second or so). We get the problem consistently when we use copy and paste of a range of data or drag edit which causes a number of property change event via the databinding and onto the eventing hooked up to the model...

  It is not easy to describe the problem but one thing we know for sure is that we do not get the problem when we take the sheet protection off. We don't want to take protection off and on for every update event (we believe it will be fatal to the performance of the app) nor do we want to code something to the change/focus/ events to prevent users from selecting cells that should not be changed...

  What we want to know is why does the protection casue invalid cross-thread method invocation exceptions or is this a catch-all exception like the old ActiveX error...

  Any help would be much appreciated... I will talk to my colleagure to find out whether we can post some code...

  Thanks.

   

  
  
  

• Mike Bohlander

  Hi Don.

  I'm afraid you're running into a limitation of Excel itself.  When a sheet is protected (even if you allow editing ranges, and inserting rows, etc...), a List on the Sheet will not automatically insert rows.  You can verify this behavior in normal Excel.  A list that allows for automatic addition of rows will have the special * row at the bottom of the list.  On a protected sheet, the * row will never be present. 

  To work around this, you may want to try intercepting the user-actions through the event model, verifying the area they are trying to modify, and if it is within the allowed range, then turn the protection off on the sheet.  After completed, then re-enable the worksheet protection.... This could be very complicated (if at all possible...)

  Also, please refer to the following sample on Excel Document Protection with a bound ListObject.

  http://msdn2.microsoft.com/ms268876.aspx

  -Brent

  
  
  
• Excel - changes and sheet protection