spacecaptainsuperguy

Board Regular
Joined
Dec 30, 2004
Messages
202
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
Is there any functionality missing ?
 
Last edited:
Upvote 0
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.
 
Upvote 0
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 ?
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top