Hide sheets so you cannot see them without the password

[Jyggalag]

Hi all,

I have an open workbook with three sheets called "1", "2", and "3".

I want to hide these sheets and make it so that you cannot open or view them without a specific password.

Right now, I can protect these sheets. However, protecting them means that you can still click on the sheets and view them. This solution is not feasible.

Can somebody please help me? I would greatly appreciate it!

P.S. I have a screenshot here from something I found online, in case it might help. However, the code essentially does nothing for me - maybe I am misusing it? I am not extremely familiar with VBA.

Kind regards,
Jyggalag

 

[RoryA]

You could hide them and then protect the workbook structure. You would then have to unprotect the workbook before being able to unhide them.

 

[Jyggalag]

You could hide them and then protect the workbook structure. You would then have to unprotect the workbook before being able to unhide them.

Hi RoryA,

That makes sense!

I forgot one important detail in my introduction however, and I apologize for that.

I would like to password protect each sheet with one password for each sheet. So essentially, you enter a password, for example "1234" and then you will be able to view sheet 1. However, if you type another password, such as "2345", you can then be able to view sheet 2.

Does that make sense?

So rather than one password to unlock everything, I would like one for each sheet

 

[RoryA]

Well, nothing you do is going to be particularly secure, so you could simply add a menu system (eg buttons on a sheet) that prompts for a password before unhiding the relevant sheet.

 

[Jyggalag]

Well, nothing you do is going to be particularly secure, so you could simply add a menu system (eg buttons on a sheet) that prompts for a password before unhiding the relevant sheet.

okay that makes sense,

how do I do that though? apologies for my confusion, I am not an extreme excel wizard

 

[RoryA]

You'd use something like:

if ucase$(Inputbox("Enter password")) = "YOUR PASSWORD HERE IN CAPS" then
sheets("Some sheet").visible = xlsheetvisible
else
msgbox "Incorrect password"
end if

 

[Jyggalag]

You'd use something like:

if ucase$(Inputbox("Enter password")) = "YOUR PASSWORD HERE IN CAPS" then
sheets("Some sheet").visible = xlsheetvisible
else
msgbox "Incorrect password"
end if

Hi Rory,

I found this online:

Private Const PASSWORD_1 As String = "1234"
Private Const PASSWORD_2 As String = "234"
Private Const PASSWORD_3 As String = "34"

Sub ProtectSheets()
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Sheets
        Select Case ws.Name
            Case "1"
                ws.Protect Password:=PASSWORD_1
            Case "2"
                ws.Protect Password:=PASSWORD_2
            Case "3"
                ws.Protect Password:=PASSWORD_3
        End Select
    Next ws
End Sub

Sub ShowSheet1()
    Dim Password As String
    Password = InputBox("Enter password for sheet 1:")

    If Password = PASSWORD_1 Then
        ThisWorkbook.Sheets("1").Visible = xlSheetVisible
    Else
        MsgBox "Incorrect password."
    End If
End Sub

Sub ShowSheet2()
    Dim Password As String
    Password = InputBox("Enter password for sheet 2:")

    If Password = PASSWORD_2 Then
        ThisWorkbook.Sheets("2").Visible = xlSheetVisible
    Else
        MsgBox "Incorrect password."
    End If
End Sub

Sub ShowSheet3()
    Dim Password As String
    Password = InputBox("Enter password for sheet 3:")

    If Password = PASSWORD_3 Then
        ThisWorkbook.Sheets("3").Visible = xlSheetVisible
    Else
        MsgBox "Incorrect password."
    End If
End Sub

Essentially, it does what I want.

I have created a "Frontpage" sheet where I have attached "ShowSheet1", ""ShowSheet2", ""ShowSheet3" to a macro/rectangular shape each

However, I would like to create three additional macros that re-hide the sheets and re-passprotect them with their original password once I press a macro where I attach this code for each respective sheet 1, 2 and 3

is this possible?

 

[Jyggalag]

Fixed it!
Full code available here

Private Const PASSWORD_1 As String = "1234"
Private Const PASSWORD_2 As String = "234"
Private Const PASSWORD_3 As String = "34"

Sub ProtectSheets()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
    Select Case ws.Name
        Case "1"
            ws.Protect Password:=PASSWORD_1
        Case "2"
            ws.Protect Password:=PASSWORD_2
        Case "3"
            ws.Protect Password:=PASSWORD_3
    End Select
Next ws

End Sub

Sub ShowSheet1()
Dim Password As String
Password = InputBox("Enter password for sheet 1:")

If Password = PASSWORD_1 Then
    ThisWorkbook.Sheets("1").Visible = xlSheetVisible
Else
    MsgBox "Incorrect password."
End If

End Sub

Sub ShowSheet2()
Dim Password As String
Password = InputBox("Enter password for sheet 2:")

If Password = PASSWORD_2 Then
    ThisWorkbook.Sheets("2").Visible = xlSheetVisible
Else
    MsgBox "Incorrect password."
End If

End Sub

Sub ShowSheet3()
Dim Password As String
Password = InputBox("Enter password for sheet 3:")

If Password = PASSWORD_3 Then
    ThisWorkbook.Sheets("3").Visible = xlSheetVisible
Else
    MsgBox "Incorrect password."
End If

End Sub

Sub HideSheet1()
ThisWorkbook.Sheets("1").Visible = xlVeryHidden
End Sub

Sub HideSheet2()
ThisWorkbook.Sheets("2").Visible = xlVeryHidden
End Sub

Sub HideSheet3()
ThisWorkbook.Sheets("3").Visible = xlVeryHidden
End Sub