Dynamic array in VBA of newly added worksheets

I have some Workbook_Open code that hides all tabs irrelevant to the user based on previously entered data. (i.e. The first time the user opens the template they answer questions and they are shown only the worksheets relevant to their needs. In subsequent openings of the file, the Workbook_Open code ensures that only those relevant worksheets are visible.) Sample code below:

Please Login or Register  to view this content.

What I am trying to do is ensure that if the user creates a new blank worksheet that it does not "disappear" on them upon future openings of the file (you can imagine how frustrating that could be to a user thinking that their work disappeared). The only way I can think of doing this is to use a Workbook_NewSheet event that places the CodeName of any new sheets into a dynamic array (if the sheet is deleted it would be deleted from the array). I would then add a check to the ShowHideTabs sub above so that the array of new sheets are not hidden.

The problem is, I cannot figure out how to create a dynamic array of worksheets where I can write code that will add new worksheets and remove worksheets in the array that are deleted by the user. Any thoughts?

Hi

Maybe have an extra sheet that has the name of the sheets in the workbook, and the user id that can access them. You could use that to determine what sheets are visible for which user.

If a new sheet is created, then it would be added to that list. A beforeclose and beforesave event could check the list of visible sheets against this stored list, and if there is a sheet on the store, that isn't visible, then it could be removed from the store.

HTH

rylo

@rylo, that's a great idea. Thanks.

Actually, using a prefix makes it even simpler. I added the following easy code to ThisWorkbook:

Please Login or Register  to view this content.

And then in my ShowHideTabs sub, I inserted an extra case so that if ws.Name is left("USR-", 4) then they will remain visible. If the user decides to remove the USR- then they're just out of luck.

Thanks!

Bit brutal, but if it works then great.

rylo