BUG: You cannot delete an Excel list row from the CommandButton Click event

• Set the TakeFocusOnClick property of CommandButton to false.
  
  -or-
• Re-select the current worksheet selection to take the focus away from CommandButton.

Back to the top

• Set the TakeFocusOnClick property for the CommandButton control to false:

  protected void ThisWorkbook_Open()
  {
  	//Get a reference to the first worksheet.
  	ws = (Excel.Worksheet)(ThisWorkbook.Worksheets[1]);
  
  	//Set up the Click event handler for CommandButton1.
  	cb = (MSForms.CommandButton)(this.FindControl("CommandButton1"));
  	cb.Click+= new MSForms.CommandButtonEvents_ClickEventHandler(cbClick);
  
  	cb.TakeFocusOnClick = false;
  }
  

  
  
  -or-
• Call the Select method for the current worksheet Selection to remove focus from the CommandButton:

  private void cbClick()
  { 
  	ThisApplication.Selection.GetType().InvokeMember("Select",
  		System.Reflection.BindingFlags.Public | 
  		System.Reflection.BindingFlags.InvokeMethod | 
  		System.Reflection.BindingFlags.Instance,
  		null,
  		ThisApplication.Selection,
  		null);
  	
  	ws.ListObjects[1].ListRows[1].Delete();
  }
  

Back to the top

Steps to Reproduce the Behavior

1. Start Microsoft Visual Studio .NET 2003.
2. On the File menu, point to New, and then click Project.
3. In the list of project types, expand Microsoft Office System Projects, and then click Visual C# Projects. Select Excel Workbook in the template list, and then click OK.
4. Click Finish in the Microsoft Office Project Wizard.
5. Press the F5 key to run the project and to open the workbook in Excel.
6. Modify the workbook as follows:
   1. Select cell A1.
   2. On the Data menu, point to List, and then click Create List. The Create List dialog appears. Click OK.
   3. Add any text to cells A2, A3 and A4 (A2:A4).
   4. On the View menu, point to Toolbars, and then click Control Toolbox.
   5. Draw a CommandButton control on the worksheet. The default name is CommandButton1.
   6. Save the workbook, and then quit Excel.
7. Add the following code to the Thisworkbook.cs code module:

   private MSForms.CommandButton cb;
   private Excel.Worksheet ws;
   
   protected void ThisWorkbook_Open()
   {
   	//Get a reference to the first worksheet.
   	ws = (Excel.Worksheet)(ThisWorkbook.Worksheets[1]);
   
   	//Set up the Click event handler for CommandButton1.
   	cb = (MSForms.CommandButton)(this.FindControl("CommandButton1"));
   	cb.Click+= new MSForms.CommandButtonEvents_ClickEventHandler(cbClick);
   }
   
   private void cbClick()
   {
   	try
   	{
   		//Delete the first row in the list object.
   		ws.ListObjects[1].ListRows[1].Delete();
   	}
   	catch (Exception ex)
   	{
   		System.Diagnostics.Debug.WriteLine(ex.Message);
   		MessageBox.Show(ex.Message);
   	}
   }
   

8. Press the F5 key to build and to run the project.
9. Click CommandButton1.
   
   Result An exception is caught when deleting the row in the list. You receive the following error message
   Exception from HRESULT: 0x800A03EC

Back to the top