Solved

Sheet tab hide unhide

Posted on 2011-09-22
5
218 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
Comment Utility
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
Comment Utility
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 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
Thanks for the help!!  -R-
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now