VBA Code to Unhide multiple WorkSheets

**craig159753** · 05-06-2015, 06:22 AM

So I have 5 sheets, call them "Title Page", "Page 1", "Page 2", "Page 3", "Page 4", etc.

On the first sheet ("Title Page") I have a list of pages used in column A. This column is populated using a drop down menu.

What i have so far is this, when someone opens the spreadsheet they will only see the "Title Page" sheet, the other sheets are hidden, which is what i want.

On the "Title Page" in column A, the user will then select the pages they want like so

__A__
Page 1
Page 3
Page 4

i.e. I do not want sheet "Page 2".

I then want a VBA macro to look at the values here and unhide the pages listed. Is this possible?
I am a total novice at VBA scripts, I know how to open the VBA section (Alt + F11 and insert a module) that is about it. Any help would be amazing!

**Rioran** · 05-06-2015, 06:56 AM

Good time of day, craig!

You may use something like that. Test button attached.

Sub Operate_Sheets()
    Call Hide_Them
    Call Reveal_Them
End Sub

Private Sub Hide_Them()
    Dim i&
    i = 2
    Do While Cells(i, 1).Value <> ""
        ThisWorkbook.Worksheets(Cells(i, 1).Value).Visible = False
        i = i + 1
    Loop
End Sub

Private Sub Reveal_Them()
    Dim i&
    i = 2
    Do While Cells(i, 3).Value <> ""
        ThisWorkbook.Worksheets(Cells(i, 3).Value).Visible = True
        i = i + 1
    Loop
End Sub

**craig159753** · 05-06-2015, 08:11 AM

This is perfect, i havent tested it yet, but looking at your example, looks really good. One question how do i add a button or assign this macro to Cntrl + Shift + D ?

**davesexcel** · 05-07-2015, 05:23 AM

Originally Posted by craig159753

This is perfect, i havent tested it yet, but looking at your example, looks really good. One question how do i add a button or assign this macro to Cntrl + Shift + D ?

It's a Worksheet_SelectionChange event, you don't assign it to anything, it runs when you select the cell.

**davesexcel** · 05-06-2015, 07:16 AM

In a regular module use this code to hide your sheets.

Sub HideSH()
    Dim sh As Worksheet
    For Each sh In Sheets
        If sh.Name <> "Title Page" Then
            sh.Visible = xlSheetHidden
        End If
    Next sh
End Sub

In the worksheet Module use this. Select the sheets names in column a to unhide them in the attached example.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    Dim sh As Worksheet
    Set sh = Sheets(Target.Value)
    Application.ScreenUpdating = 0
    sh.Visible = xlSheetVisible

End Sub

Worksheet Module
RightClick.jpg