Select the product you need help with

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

Article ID: 823988 - View products that this article applies to.

SYMPTOMS

You have built a managed code extension for Office Excel 2003. Your solution uses the Click event of an MSForms CommandButton control to remove rows from a List object that is located on a worksheet. When you click the button to remove the List row, the row is not removed, and you may receive a run-time error.

Back to the top | Give Feedback

STATUS

Microsoft has confirmed that this is a bug in Office Excel 2003.

Back to the top | Give Feedback

RESOLUTION

To resolve this problem, make sure that CommandButton does not have the focus when you delete the list row. Use one of the following methods:

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 | Give Feedback

WORKAROUND

To work around this problem, use one of the following methods:

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 | Give Feedback

MORE INFORMATION

Steps to Reproduce the Behavior

Start Microsoft Visual Studio .NET 2003.
On the File menu, point to New, and then click Project.
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.
Click Finish in the Microsoft Office Project Wizard.
Press the F5 key to run the project and to open the workbook in Excel.
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.

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);
	}
}

Press the F5 key to build and to run the project.
Click CommandButton1.

Result An exception is caught when deleting the row in the list. You receive the following error message
Exception from HRESULT: 0x800A03EC