edit -> delete on excel disabled, after excel vsto installed

edit -> delete on excel disabled, after excel vsto installed

• Matt Connolly

  Hi johnnym

  Have you checked by putting a breakpoint, that the shutdown event for the VSTO application for Sheet1 is indeed being invoked and the above code is being executed.

  Try this - Use the VSTO solution with the code mentioned in my previous post. In the VSTO solution the Edit->delete is expected to be disabled. Shut down the application and ensure that the breakpoint at the shutdown event handler is executed on shutdown and all the commandBars are reset.

  Open a non-VSTO excel spreadsheet and type something on a cell and see if edit->delete.. is enabled on selecting that cell.

  Let me know if it still does not work for you.

  Thanks,
  Nikhil

  
  
  

• RobinSed

  The above solutions are all very well but I use a lot of customisations in Excel and resetting all command bars is not something I really want to do. Also I'm about to deploy a product that uses list objects extensively to a customer site - if anyone has any news on available fixes for this problem I'd appreciate a reply.

  
  

• astubble

  See my post about a temporary solution that may help you: http://forums.microsoft.com/msdn/ShowPost.aspx postid=1150070&isthread=false&siteid=1

  Gennady.

  
  

• hearn

  I am not sure how this will help. The project I completed will soon be installed on our customer computer. I will not be on their computer or their site. I would like to know a permanent fix. And what cause this error, is there something in VSTO that disables function in Excel. Their computers did not have this issue before only after installed my application.

  Thank you

  
  
  

• sdmeijer

  Nikhil,

  I'm having the same issue, although I don't protect any of my sheets. I have applied the code as you suggested and still no luck.

  One other problem I have, is the Delete Column option is disabled in all my excel worksheets, not just the one create through VSTO.

  I am using a listobject amd I have had a few sudden crashes as you stated.

  If I right click on a cell or row, "delete is enabled", on a column it's disabled. but under the EDIT option it's disable in all 3 cases.

  But if I right click on a cell and select delete, then I have the option of deleting a column, which works.

  Any thoughts

  
  

• nikitaj

  Alisa, another question: did your Excel project in VSTO use list objects If so, how many in a sheet

  Thanks

  
  

• tizza2k

  Alisa,

  OK - so if you select the sheet that is protected Edit-Delete should be disabled. If you select a sheet that is not protected, Edit-Delete should be enabled. It doesn't matter if you protected it via the user interface or via code - if a worksheet is protected, Edit-Delete will be disabled when the protected worksheet is the active sheet (i.e. the menu item is worksheet specific). It's standard Excel behavior.

  To validate, if you unprotect the worksheet in your project and then test it, I'd bet that Edit-Delete is enabled as usual. Assuming I'm correct (not always a safe assumption ), your next series of questions might revolve around how to allow the end-user some interaction with a worksheet while at the same time protecting it because of other concerns. For that, you might want to check out an Excel specific newsgroup:

  http://msdn.microsoft.com/newsgroups/default.aspx dg=microsoft.public.excel.programming&lang=en&cr=US

  Regards,

  Steve

  
  
  

• Hibernating Bear

  I am having this exact same issue, but have not found a workaround. Did you have any eventual luck with this I'd be very interested in any suggestions you may have. Thanks in advance.

  Cheers,

  Hans

  
  

• Tom S

  Hi

  You can try and run this snippet in a VBA command window and that should reset the commnadbars in Excel.

  Sub ResetCommnadBars()
  For i = 1 To Application.CommandBars.Count
  Application.CommandBars(i).Reset
  Next
  End Sub

  This should restore your command bars to their original state. I hope this helps!

  Thanks,
  Nikhil
  

  
  
  

• DNA5122

  Hi Alisa,

  VSTO doesn't do anything to disable this command.

  Perhaps answering a few questions would shed some light on the problem.

  1. On your development PC, do you experience this issue
  2. If so - there must be something specific to your project that is causing this. Double-check any event handlers and startup/shutdown methods to look for code that is causing this. To confirm that it's not VSTO in and of itself do a simple "Hello World" VSTO app (or take my word for it that it's nothing to do with VSTO :) ) Are you doing anything in your solution with dynamically protecting/unprotecting worksheets
  3. If you don't experience the problem on your PC, have you experienced it in person on your clients computer If not, ask them to record exactly what they are doing right before the command becomes disabled (including the name of the active workbook and worksheet). It may be that they're simply opening up another workbook that has protected worksheets or something (by default Edit->Delete is disabled when you protect a worksheet and accept the defaults).

  Regards,

  Steve

  
  
  

• Thre

  I ran into the same problem and used the vb version in Excel vba ide. It reset the menus, but also undid some customization that I did. Will using the routine as part of the shutdown event in VSTO wipe out all users intentionally customized menus
  

• Brad Pitcher

  Yes it did.

  I run the code that was give to me in the previous to yours. it worked though links and object are still disabled but I don’t know excel well enough to know if that is suppose to be that way until I do something in it.

  I don’t know what went wrong with excel, is it just excel being weird

  To answer question 2, I do have one sheet protected in my project, though I protected it on the sheet level not though the code.

  
  
  

• JohnI

  Hi Alisa

  From what it looks like, you are running into a recently found issue when using ListObjects. The VSTO runtime for ListObject saves the current state of certain menu items like edit.delete, disables the edit.delete on selection of the ListObject, and restores the saved state on deselection or closing of the solution. This is done in order to enforce constraints of databound listObjects for example columns cannot be added/removed.

  In normal cases, the ListObject should restore the disabled menu items to the state they started with on shutdown. However, there are two cases involving use of more than one ListObjects and a sudden Excel crash which can cause the menu items not to be re-enabled on closing of the VSTO solutions. I believe you have run into one of them. We are working to resolve this issue in future versions. For now, as a workaround, you can use the following code snippet such that this is called in the Shutdown event handler of the VSTO customisation, to surely resolve the issue.

  private void Sheet1_Shutdown(object sender, System.EventArgs e)

  {

  for (int i = 1; i <= this.Application.CommandBars.Count; i++)

  this.Application.CommandBars[ i ].Reset();

  }

  Let me know if this works for you. Hope this helps!

  Thanks,
  Nikhil

  
  
  

• alexlim8190

  I also had the same issue. I mapped the workbook shutdown to the following C# code...

  private void ThisWorkbook_Shutdown(object sender, System.EventArgs e){

  for (int i = 1; i <= this.Application.CommandBars.Count; i++)

  this.Application.CommandBars[ i ].Reset();

  }

  However, this ONLY works if you don't have a normal workbook open at the same time as your VSTO workbook. Ideally you would do this on the workbook deactivate event. However, you then need to use workbook activate to disable those commands again (assuming you don't want them available). If you are using a ListObject you really don't want the user insert/deleting columns (or at least I don't).

   

  
  
• edit -> delete on excel disabled, after excel vsto installed