Sheet tab hide unhide

Posted on 2011-09-22
Last Modified: 2012-05-12
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-
Question by:RWayneH
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 12

Expert Comment

ID: 36583714
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


Author Comment

ID: 36585305
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-
LVL 42

Accepted Solution

dlmille earned 500 total points
ID: 36585815
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....


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.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.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,

LVL 42

Expert Comment

ID: 36585833
If you like that idea, you might like the logic/code in this solution:  http:/Q_26875534.html



Author Closing Comment

ID: 36590963
Thanks for the help!!  -R-

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question