very hidden sheets add-in

spacecaptainsuperguy

Board Regular
Joined
Dec 30, 2004
Messages
173
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.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,974
Office Version
365
Platform
Windows
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

Board Regular
Joined
Dec 30, 2004
Messages
173
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.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,974
Office Version
365
Platform
Windows
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

Code:
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
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,974
Office Version
365
Platform
Windows
Is there any functionality missing ?
 
Last edited:

spacecaptainsuperguy

Board Regular
Joined
Dec 30, 2004
Messages
173
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

Well-known Member
Joined
Mar 11, 2015
Messages
4,974
Office Version
365
Platform
Windows
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 ?
 

Forum statistics

Threads
1,085,968
Messages
5,387,029
Members
402,031
Latest member
udn3939

Some videos you may like

This Week's Hot Topics

Top