Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Sheet tab hide unhide

Posted on 2011-09-22
5
Medium Priority
?
228 Views
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-
0
Comment
Question by:RWayneH
  • 2
  • 2
5 Comments
 
LVL 12

Expert Comment

by:kgerb
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


Kyle
0
 

Author Comment

by:RWayneH
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-
0
 
LVL 42

Accepted Solution

by:
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....

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
 
LVL 42

Expert Comment

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

Cheers,

Dave
0
 

Author Closing Comment

by:RWayneH
ID: 36590963
Thanks for the help!!  -R-
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

577 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