Can not delete Command Button from worksheet without Excel crashing

Question

I have a worksheet that I want to delete. Originally it contained 3830 rows. When trying to delete the worksheet, Excel would crash, and completely close. I thought this might be due to the amount of data on the worksheet, and the presence of a formula in one of the columns. So I removed the formula from the column in sections, and then deleted rows in blocks, saving each time. I removed the data until there were only 30 rows left, and still could not delete the worksheet without a crash. After this I went through the VBA and removed any references to the sheet, in case there was some kind of dependency problem. This still did not allow me to delete the worksheet.

As well as the data there is a Command Button embedded in the worksheet, which I have determined is the cause of the problem. The button has no related click event - clicking on it appears to do nothing.

To modify the button I have to enable Design Mode, which I presume is because it is an Active X control. If I try to cut the button via the Right click option, Excel crashes. If I try to delete the button by selecting it and hitting the delete key, Excel crashes. The button is called 'Sort' instead of 'Command Button 1' or similar, but I have not been able to find a reference to this name in the Name Manager.

I have been through the object properties, and can Edit the Command Button Object, moving the object, resizing it, etc. I have also been through Format Control, but with no success. Originally the object was locked, but the worksheet was not. I have unlocked the object, but again this has made no difference.

I have also created a new Active X Command Button and a Form Controls Command Button on the worksheet, and have been able to delete both without any problem. The workbook in question contains a lot of worksheets so ideally I want to be able to resolve this issue without needing to copy each of the worksheets I want to keep to a new workbook, or a similar fix.

Any suggestions would be gratefully received!

Copy your data to a clean worksheet if possible - that should be the easies solution ;)# — Christian Sauer
I have to agree with Christian. Also, to greatly speed up the copying process, you can use Shift+Click to select multiple worksheets (i.e. hold shift, click worksheet1, then click worksheet3, this gives you all 3 worksheets), then right click to copy into a new workbook. The hard part would be making sure the VBA and formulas carried over intact to the new worksheet and don't reference the old one. — PowerUser
Yeah, Excel is prone to wierd things such as this. I have encountered many times where creating a new workbook, then copying in the worksheets and VBA was the only way to fix things. — Alan Waage
Have you tried removing the button by using VBA? Something along the lines of Sheets("Sheet1").Shapes("Button Name").Delete — Jose M.
I tried removing the button using VBA, but was getting run-time errors. I've got quite a lot of VBA and formulas in the workbook, across the many worksheets, so don't really want to copy them to a new workbook - I think the tradeoff in terms of copying all the worksheets versus hiding the problem sheet is not really worth it. So instead, as the worksheet only has a single button on it, I have decided to hide the worksheet. — Gary Stacey

Hunter Hunter · Accepted Answer · 2014-12-23T14:01:21

I had the same issue. I added the command button and for some reason, when I tried removing it, the excel crashed out.

Solution:

Create a blank excel file and save it with a new name.
Open the file with the command object.
Right click on the sheet and use MoveOrCopy.
Check copy option and copy the sheet across to the newly created file.

All your formulas, NamedCells get copied across successfully. If you got any VB code, you need to copy it manually.

Can not delete Command Button from worksheet without Excel crashing

2 Answers