VBA Dynamic Control over Sheet Visibility

[DixiePiper]

I am using a "Workbook_BeforeClose" operation to hide all worksheets except one. This is working fine for now but as I move through the process, I want to be able to change which sheets are visible. Within the application, I have a login in function to control visibility but when the workbook closes, I want to reset the visible sheets. Controlling visibility reduces visual clutter and focuses the user on what needs to happen at the current stage of the project. Here is where I start to get lost . . .

I have an Admin page that holds "behind the curtain" data and I've managed an ActiveX ListBox that holds the sheet names that might need to be visible when the workbook opens. I'd like to be able to select the sheets and have the "Workbook_BeforeClose" macro use that selection to determine which sheets should be visible/very hidden before the workbook shuts down.

Thanks in advance.

 

[John_w]

Try this. Assumes your Admin sheet is named "Admin" and the listbox is "ListBox1".

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim lb As MSForms.ListBox, i As Long
    
    Set lb = ThisWorkbook.Worksheets("Admin").OLEObjects("ListBox1").Object
    
    For i = 0 To lb.ListCount - 1
        If lb.Selected(i) Then
            ThisWorkbook.Worksheets(lb.List(i)).Visible = xlSheetHidden  'or xlSheetVeryHidden
        Else
            ThisWorkbook.Worksheets(lb.List(i)).Visible = xlSheetVisible
        End If
    Next

    If Not ThisWorkbook.Saved Then ThisWorkbook.Save
    
End Sub

 

[DixiePiper]

John,
Thanks for helping get this started. How would I modify the above to hide all sheets EXCEPT for those selected via the listbox?

 

[John_w]

Just swap the statements in the Then and Else clauses.