Check if command button is on worksheet, delete worksheet if not

[Jerbinator]

Hi all,

I'm trying to figure out how to get rid of any extra worksheets my users might add to a workbook by accident. They are not supposed to add any worksheets themselves at all. I can't protect the workbook structure because there are macros that programmatically and legitimately add new worksheets to the database. However, one thing that happens is a button called "SuperAdminButton" is created on every new legitimate sheet, which is something a user wouldn't do themselves. The button is used for navigation.

SO. I need a macro that will be included in my BeforeClose event that will check each worksheet in the workbook, look to see if there's a command button on there with the name "SuperAdminButton," and if there is no button like that, delete the sheet without warning.

The button is a form control, if that matters.

[Norie]

Couldn't you use the workbook level event NewSheet to delete any unauthorized sheets that are added?

That event is triggered whenever a new sheet is added to the workbook and you could have code in it to check if the new sheet is legitimate or not.

[Gatti]

Hello

Try this (not very smart code... but works)

Please Login or Register  to view this content.

[Jerbinator]

Norie, how would I code that, exactly? I'm happy to use that event, and considered it at the beginning of this conundrum, but I need to know how to make VBA realize whether a worksheet is legitimate or not.

[Gatti]

Jerbinator, my code didn't work?

[Leith Ross]

@Gatti

Here is another way using the Forms Buttons collection.

Please Login or Register  to view this content.

[Gatti]

@Leith

Well... much better and optmized than mine
Thanks for the feedback!

[Jerbinator]

I ended up using a mixture of these ideas. @Leith, when I ran your code, VBA did not recognize the "Excel.Button" dimension and started deleting sheets left and right. (Amen for backup copies, right?) So I defined it as a shape, the way @Gatti did, and it recognized it after that. I just ran a test where I added a sheet and put a form button on it, but didn't name it the same way, and the code deleted that sheet and left the rest. Perfect! Thank you all!

I have a ton of things going on already in the BeforeClose event, but here's the snippet of code I ended up using for this:

Please Login or Register  to view this content.