very hidden sheets add-in

[spacecaptainsuperguy]

Hi everyone,
I used to have an excel add-in that had a form that allowed me to select multiple sheets from within that spreadsheet and select whether to hide, unhide or make very hidden the selected sheets. I recently upgraded my machine and office and that old add-in no longer works. I've searched the forum but haven't found anything quite like that in the VBA discussion. Anyone know of an add-in that provides that functionality or willing to share some code that performs that function? The code itself of hiding/unhiding (from what I've been looking at) doesn't seem like it is that involved, it's the user form and the functionality it provided, allowing me to select whatever sheets I needed that was the real time saver.
Thanks in advance.

 

[jorismoerings]

Hi,

take a look at this and see if this helps https://www.excelcise.org/how-to-listpopulate-sheet-names-to-a-listbox-on-a-userform-vba/

 

[Yongle]

Would this do what you want ?

An icon in the ribbon area available regardless of which workbook is currently active
Clicking on the icon would list current status of all sheets in active workbook and enable user to quickly change status of (one or more) sheet to visible, hidden or very hidden

 

[spacecaptainsuperguy]

Would this do what you want ?

An icon in the ribbon area available regardless of which workbook is currently active
Clicking on the icon would list current status of all sheets in active workbook and enable user to quickly change status of (one or more) sheet to visible, hidden or very hidden

That sounds like exactly what I'm looking for.

 

[spacecaptainsuperguy]

Hi,

take a look at this and see if this helps https://www.excelcise.org/how-to-listpopulate-sheet-names-to-a-listbox-on-a-userform-vba/

Nice. Thank you for sharing this. I've been wanting to learn how to do this.

 

[Yongle]

I have cobbled this together very quickly just to see if it is giving you the basic functionality
If it is broadly what you want then I will tidy it up and convert into an addin later
It is designed to run only in the workbook containing the code for this initial test

Test as follows..
1. Create a NEW workbook
2. Copy the 4 subs below and paste into the SHEET module of sheet1
3. Add a few more sheets to the workbook
4. Run macro Master
5. You should now get a list of sheets with status
6. Click on a cell in column D to change a sheet's status to very hidden (C for hiidden, B for visible)

Let me know how you get on

Sub Master()
    Application.EnableEvents = False:   Application.ScreenUpdating = False
    Me.Activate
    Call PrepSheet
    Call LoopSheets
    Application.EnableEvents = True
End Sub

Private Sub PrepSheet()
    Cells.Clear
    Cells.Font.Color = vbBlack
    Cells(1, 1).Resize(, 4).Value = Array("Sheet", "Visible", "Hidden", "Very" & Chr(10) & "Hidden")
    Columns(3).Font.Color = vbRed
    Columns(4).Font.Color = vbBlue
    With Cells(2, 2).Resize(Worksheets.Count, 3)
        .Font.Name = "Marlett"
        .Font.Size = 14:
        .HorizontalAlignment = xlCenter
    End With
End Sub

Private Sub LoopSheets()
    Dim w As Long, r As Long, ws As Worksheet
    For w = 1 To Worksheets.Count
        r = w + 1
        Set ws = Sheets(w)
        Cells(r, 1) = ws.Name
        Select Case ws.Visible
            Case 0:    Cells(r, 3) = "b":   Cells(r, 1).Font.Color = vbRed
            Case 2:    Cells(r, 4) = "b":   Cells(r, 1).Font.Color = vbBlue
            Case -1:   Cells(r, 2) = "b"
        End Select
    Next w
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim r As Long, ws As Worksheet
    Application.ScreenUpdating = False
    If Intersect(Range("B2:D" & Rows.Count), Target) Is Nothing Then Exit Sub
    
    r = Target.Row
    If Cells(r, 1) = "" Then Exit Sub
    Set ws = Sheets(Cells(r, 1).Value)
    With ws
        If .Name = Me.Name Then Exit Sub
        Cells(r, 2).Resize(, 3) = ""
        Target = "b"
        Select Case Target.Column
            Case 2: .Visible = xlSheetVisible:      Cells(r, 1).Font.Color = vbBlack
            Case 3: .Visible = xlSheetHidden:       Cells(r, 1).Font.Color = vbRed
            Case 4: .Visible = xlSheetVeryHidden:   Cells(r, 1).Font.Color = vbBlue
        End Select
    End With
End Sub

 

[spacecaptainsuperguy]

That functions very well.

 

[Yongle]

Is there any functionality missing ?

 

[spacecaptainsuperguy]

If I were to give you a wish list, I suppose the only additional functionality I would add would be to enable/disable sheet protection.

 

[Yongle]

So you would like a column with toggle on\off for sheet protection ?

Password will be the issue
- are passwords identical for all sheets in the same workbook ?