Make a collection of a few worksheets and check if another worksheet is in the collection

Hi all,

I have a workbook database that I've built. I want to create a list of the worksheets that I have created myself. I want to make the list of the worksheet codenames, specifically, because the average user can accidentally change the name of the worksheet but cannot accidentally change the codename. Once I've made this list, I want to run a macro in the Before Close event that checks every worksheet in the workbook against this list. If the worksheet is not included in the list, I want that worksheet to be deleted.

The idea is to remove any worksheets that a user has created themselves BEFORE the workbook closes. For a hundred different reasons that I won't get into on this forum, I can't run a Workbook.Protect to prevent worksheet creation. Just trust me, I can't. So I need to build a sort of protected group of worksheets that I do not want to be deleted, and then I want to get rid of all the other ones. And it gets more complex after that: I have a userform set up where a user can legitimately add a worksheet to the database. I want to add code lines that will automatically include any worksheets created this way in this special group of worksheets.

After doing extensive reading online, I'm thinking that a collection is the best way to go. But I have never made a collection or used a class module before. I have absolutely no idea how to do so, and not for lack of trying to understand. I apparently have a mental block for the concept.

Please be very specific when you explain, and please don't just give me links to online tutorials, because I just spent my entire afternoon Googling and have read at least the first ten tutorials that pop up in searches about building collections, and I'm still completely confused and, to be honest, rather cranky.

Thanks for any help you can provide!

Thank you for explaining it so plainly! I appreciate that. I have already decided to build a table with the worksheet names that should be included, and I will set the code to add the worksheet name and codename to the table when a user legitimately adds a worksheet. One thing, though: how do I go about adding the table information to the array? Again, please be explicit. Is this a For Each situation?

I ended up putting the list of worksheet code names into a table, because it's easier to just add a new row to the table and put the sheet name in it than it is to find the last cell in Column A. Then I just specified the array range as the first column data body range of the table. But otherwise I did this exactly the way you did it, and it works perfectly. I also have set up some code to prevent users from accidentally deleting the worksheets I want to stay put. Another ounce of idiot-proofing is in place! Thank you!