Solved: Dynamic array in VBA of newly added worksheets

[greglittle]

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:

[vba]Sub ShowHideTabs ()
For Each ws In Worksheets
Select Case ws.CodeName
Case "Sheet19"
ws.Visible = True
GoTo Skip
Case Else
ws.Visible = False
End Select
Skip:
Next ws
End Sub[/vba]

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?

[mikerickson]

Is this
one workbook going to be used by only one user. After making choices, some sheets will never be seen again.

or

one workbook is used by several users. Each user makes their choices and the wb detects which user opens the wookbook, showing them the applicable sheets.

If case one, why not delete the unneeded sheets (on initial questioning) rather than hiding them. Then the Open event can show all the sheets, included any added ones.

If case two, make a list for each user of the needed sheets. Any sheet a user adds during their session should be added to their list.

[greglittle]

@mikeerickson, only one user, but if they change the answers to the initial questions then I need the flexibility to show/hide again.

Either way, I think I can implement your second suggestion using a dynamic named range (DNR) with the user-added worksheets. If one is deleted I could remove the deleted worksheet from the range and then res-sort the range to ensure that empty cells are pushed to the bottom and the DNR is readjusted to only contain valid values. However, I'm not sure how I will be able to tell which one they deleted. Any thoughts on that? Thanks!

[greglittle]

Actually, using a prefix makes it super simple! I just added the following easy code to ThisWorkbook:

[VBA]Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sh.Name = "USR-" & Sh.Name
End Sub[/VBA]

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 for the help!

[mikerickson]

A different application would be at design time, write the routien to loop through all the worksheets that you created, changing visiblity as needed. Any sheets the user adds won't be on the list and the .Visible = xlSheetVeryHidden won't be applied to those sheets.

For example, if the workbook contains 5 sheets when the user gets it,
[VBA] Dim ws As Variant

For Each ws In Array(Sheet1, Sheet2, Sheet3, Sheet4, Sheet19)
Select Case ws.CodeName
Case "Sheet19"
ws.Visible = xlSheetVisible
Case Else
ws.Visible = xlSheetHidden ' (or xlSheetVeryHidden)
End Select
Skip:
Next ws [/VBA]Note that the Array uses sheet code names, allowing the user to change the tab names of what ever they want to