Sheet tab hide unhide

I have a userform that call out a hide sheet.  To get the user back to the control page we are using Ctrl+d  When the user uses this I would like it to rehide the sheets.  So if Sheet7 8 and 9 are open or not, hide them anyway. -R-
RWayneHAsked:
Who is Participating?
 
dlmilleConnect With a Mentor Commented:
Excel doesn't care if its already hidden.  Create a loop through all the worksheets that should be hidden and hide them - you could store the worksheet names in a range on a hidden sheet and then loop through that to get the names....

E.g.,

Here's some straight-forward code. First, to generate that list of sheets, then you can manually set TRUE/FALSE for what is hidden (you really only need the FALSE list, so you can delete the rest after its generated).

Second  - the hideSheets() macro, will search the HiddenSheets tab and use that to hide any sheets.  It only hides sheets, but you can change the logic to unhid what should be.  
Sub generateSheets()
Dim wkb As Workbook
Dim sht As Worksheet, ws As Worksheet
Dim i As Long

    Set wkb = ActiveWorkbook

    On Error Resume Next
    Set sht = ThisWorkbook.Sheets("SheetsHidden")
    If Err.Number <> 0 Then 'create it
        Set sht = ThisWorkbook.Sheets.Add(after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        sht.Name = "SheetsHidden"
    End If
    On Error GoTo 0
    sht.Cells.ClearContents
    
    sht.Range("A1").Value = "HIDDEN?"
    sht.Range("B1").Value = "SHEET NAME"
    
    i = 2
    For Each ws In wkb.Sheets
        sht.Range("A" & i).Value = False
        sht.Range("B" & i).Value = ws.Name
        sht.Range("A" & i).Select
        With sht.Range("A" & i) 'for some user-friendliness :)
            .Validation.Delete
            .Validation.Add Type:=xlvalidationlist, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="True,False"
            .Validation.InCellDropdown = True
            .Validation.IgnoreBlank = True
            .Validation.ShowInput = True
            .Validation.ShowError = True
            .Interior.ColorIndex = 6
        End With
        i = i + 1
    Next ws
    
End Sub
Sub hideSheets()
Dim wkb As Workbook
Dim sht As Worksheet, ws As Worksheet
Dim i As Long

    Set wkb = ActiveWorkbook
    On Error Resume Next
    Set sht = ThisWorkbook.Sheets("SheetsHidden")
    If Err.Number <> 0 Then
        MsgBox "there is no SheetsHidden tab to reference", vbCritical, "Aborting..."
        Exit Sub
    End If
    On Error GoTo 0
    
    For Each mycell In sht.Range("A2", sht.Range("A" & sht.Rows.Count).End(xlUp))
        If mycell.Value = True Then
            wkb.Sheets(mycell.Offset(0, 1).Value).Visible = xlSheetHidden
        End If
    Next mycell
End Sub

Open in new window

In fact, you could have columns of sheets, with each column representing a PHASE in your application.  Based on the Phase, you could hide/unhide using a technique like this...

See attached,

Dave
hideSheets-r1.xlsm
0
 
kgerbChief EngineerCommented:
Well, without seeing any sort of example code or file or anything the best I can do it to show you how to hide and unhide sheets with VBA.  If you would like a more tailored solution we're going to need some more info.

To hide sheets with VBA:

Worksheets("Sheet1").Visible = False

Open in new window


To Unhide sheets with VBA:

Worksheets("Sheet1").Visible = True

Open in new window


Kyle
0
 
RWayneHAuthor Commented:
I understand these to codes.. however I need it to say something like.  With Sheet1 check if it is hidden or not, then if it is already hidden fine continue, but if it is not hide it.  Some sheets will be already hidden and other will not be, and those are the ones that I would like to hide.  I plan on checking multiple sheets to make sure they are hidden.

Or does Excel not care if it is already hidden and I just do a .Visible = False for them all?  I thought that it had to be an if statement type in the code.   -R-
0
 
dlmilleCommented:
If you like that idea, you might like the logic/code in this solution:  http:/Q_26875534.html

Cheers,

Dave
0
 
RWayneHAuthor Commented:
Thanks for the help!!  -R-
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.