[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


Sheet tab hide unhide

Posted on 2011-09-22
Medium Priority
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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

649 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