Hide/Unide Pages - Macro Pls Help

[scoscione]

Hello all,

I have a file with multiple pages and which will have two different type of users:
1. the one updating the drivers behind the calculations (mappings sheets)
2. the ones using the final information ( pivot and chart sheets)

I am trying to segment the file so that if there is a reading user (number 2) he will see only the pivot and chart sheets. But if the user is a super user (number 1) he/she will qlik on a button and input a passoword to show the drivers sheets. Once he/she is done, he/she will qlik the other button to hide the drivers sheets.

The objective is to reduce the number of information diplayed to users (I had users deleting mapping sheets and thus generating issues with the file) and thus making it easier to read.

So far I was only able to do this (with the help of the forum previous questions). The issue is this one just leaves the index (welcome sheet) displayed, and I need to have index, sheet 1 and sheet 2 displayed. After that I want to add a button that enables the user to see the drivers if needed.

Private Sub Workbook_Open()Dim wsSheet As Worksheet
Application.ScreenUpdating = False
    For Each wsSheet In Worksheets
    If wsSheet.Name = "Index" Then
    Else
    wsSheet.Visible = False
    End If
    Next wsSheet
Application.ScreenUpdating = True
End Sub

I am not good at macros and I am getting very confused so I would really appreciate any help

Thanks.

 

[CalcSux78]

With hiding sheets, you need to have at least one sheet visible. A "Select Case" statement would be an easy way to make changes based off of sheet name. Here's more info on Select Case: Select Case Statement in Excel VBA Macro Code. Alternative to Multiple If, Or, And Else Statements

Private Sub Workbook_Open()
Dim ws As Worksheet


With Application
    .ScreenUpdating = False
    Sheets("Sheet1").Visible = xlSheetVisible
    
    For Each ws In Worksheets
        Select Case ws.Name
            Case "Index", "Sheet1", "Sheet2"
                ws.Visible = xlSheetVisible
            Case Else
                ws.Visible = xlSheetVeryHidden
        End Select
    Next ws
    .ScreenUpdating = True
End With
End Sub

 

[scoscione]

Thanks a lot
I was able to successfully implement all the macros for the groups of sheets.

In this way the user currently opens a page and it opens just the index page, and I added control buttons where the users selects which groups of sheets he would be seeing.

Regards

With hiding sheets, you need to have at least one sheet visible. A "Select Case" statement would be an easy way to make changes based off of sheet name. Here's more info on Select Case: Select Case Statement in Excel VBA Macro Code. Alternative to Multiple If, Or, And Else Statements

Private Sub Workbook_Open()
Dim ws As Worksheet


With Application
    .ScreenUpdating = False
    Sheets("Sheet1").Visible = xlSheetVisible
    
    For Each ws In Worksheets
        Select Case ws.Name
            Case "Index", "Sheet1", "Sheet2"
                ws.Visible = xlSheetVisible
            Case Else
                ws.Visible = xlSheetVeryHidden
        End Select
    Next ws
    .ScreenUpdating = True
End With
End Sub