[RESOLVED] Excel VBA - Delete Sheet Disabled

[Hack]

Can someone explain why Edit/Delete Sheet is disabled in a workbook that is not protected in any way?

I need to delete a sheet, and delete is disabled both in the right click popup menu and the menu item under Edit.

[westconn1]

what error do you get if you try to delete with code?

[anhn]

A sheet cannot be deleted if it is the last visible sheet or the workbook is protected in some way.
Are you sure "workbook that is not protected"? Is the workbook shared?

[westconn1]

A sheet cannot be deleted if it is the last visible sheet

i checked this, in excel 2000, it does not disable the menu item, just brings up a dialog if you try to delete

[Hack]

The workbook is not shared.

The sheet is not the last in the workbook (in fact, there are a whole lot of sheets and need to go. This is just one of several - I got this app dumped in my lap cause the guy who did it retired, and it needed fixing.)

I have not attempted to delete it through code, and do not want to. There is no reason to do this through code as that code would only need to be run a single time. Once this sheet is deleted, it will never been recreated. The sheet was used to populate a dropdown. I'm populating the dropdown with code, so there is no reason at all for this sheet to exist (all of the controls on the workbook are being populated with different sheets and I'm replacing them all with code, so there are about 6 or 7 sheets that I want to completely, totally, and forever delete from the workbook.

However, the delete options from the menus remain disabled.

[anhn]

One thing may happens is the author of the workbook may use code to disable the delete menu and command. You may find the code some where in VBE.

Try this: Open the workbook with Macro Disabled then try to delete the sheets to see if it works.

[zaza]

I definitely get the Delete menus disabled when the workbook (but not the worksheet) is protected. You will also be unable to move sheets around, insert new ones (etc) if the workbook structure is protected.

To be certain, if on the Tools -> Protection menu it says "Unprotect workbook", then it has been protected. Notice that you may have the sheets Unprotected but the workbook protected.

If you're certain that the workbook is not protected, then it's definitely unusual. Can you post it, or is it confidential?

[Hack]

Try this: Open the workbook with Macro Disabled then try to delete the sheets to see if it works.

To be certain, if on the Tools -> Protection menu it says "Unprotect workbook", then it has been protected. Notice that you may have the sheets Unprotected but the workbook protected.

Thanks guys. I will check this out on Monday.....I hadn't thought about the disable macros thing, and I did not know there was a difference between workbook and sheet protection.

zaza: You say the a sheet could be unprotected but the workbook protected. Is it possible to have the reverse...i.e., the workbook unprotected, but the sheet protected?

[westconn1]

you can run a single line of code to delete a sheet from the intermediate window

i was just interested what if any error message you got, as it may have pointed to the reason for the menu being disabled, but it turns out the message is just generic
sheets("sheetname").delete

[zaza]

Thanks guys. I will check this out on Monday.....I hadn't thought about the disable macros thing, and I did not know there was a difference between workbook and sheet protection.

zaza: You say the a sheet could be unprotected but the workbook protected. Is it possible to have the reverse...i.e., the workbook unprotected, but the sheet protected?

Yes, definitely. The workbook protection is to protect the structure of the workbook without necessarily interfering with what the user can do on the sheets. It will prevent you from inserting or deleting sheets or moving their order, for example, but won't stop you typing in cells or changing formatting.
Sheet protection will stop you from overtyping formulas, changing cells etc on individual sheets, but won't stop you from adding extra sheets in or rearranging them. Either and both can be password protected, but don't have to be in order to be protected.

[Hack]

Disabling the macros didn't work.

I also can't upload it. Even zipped, it exceeds the attachment file limit.

[zaza]

What about double-checking the protection?

[Hack]

What about double-checking the protection?

As it turns out, it is protected. As least, I think it is protected.

I thought this not to be the case, because the "Tools/Protection/Protect Sheet" menu item is enabled. I was told, or read somewhere, that if protection is on, then that will be disabled, and "Unprotect Workbook" will be enabled. Oddly enough, they are both enabled. When I click "Unprotect Workbook" however, it asks me for a password (which none of use have any clue what it might be - the author of this "application" retired).

Just to verify - if I click Unprotect Workbook and it asks for a password, then that means it is protected, right?

If so, then why is Protect Sheet still enabled? Isn't it already protected? How can you protect the same thing twice?

[anhn]

That's odd enough.

If a menu item shown "Protect Sheet/Workbook" that means Sheet/Workbook is not protected.
If a menu item shown "Unprotect Sheet/Workbook" that means Sheet/Workbook was protected.

If the sheet is protected but the workbook is not protected then you can delete the sheet.
However, if the sheet is not protected but the workbook is protected then you CANNOT delete the sheet.

Try this way:
Step 1: In VBE, drag any existing forms and modules from old workbook to a new workbook.
Step 2: Select all sheets then use option "Copy or Move" : Copy all sheets to a new workbook.
This method may have a problem with linked cells from this sheet to another sheet but you can change links later.

Sheet and workbook paswords are relative easy to break with some tools (you can find out ...), only openning password is hard to break.

[Hack]

If a menu item shown "Protect Sheet/Workbook" that means Sheet/Workbook is not protected.
If a menu item shown "Unprotect Sheet/Workbook" that means Sheet/Workbook was protected.

That is exactly what I thought, yet, it was protected, but Protect Sheet was still enabled.

Sheet and workbook paswords are relative easy to break with some tools

One of the Excel guys back in my shop managed to break it, so I got it unprotected, and could delete the sheet. However, it remains a mystery with respect to how it got protected and still the associated menu items remained enabled.