How to hide certain sheets with password

[marcielo]

Hello

I´m trying to achieve the possibility to hide some sheets with a password, but not all.

I found this thread:
http://www.excelforum.com/excel-prog...-password.html

Where all the sheets are hidden with a password, but since I'm not a coder, I´m not able to get some of the sheets to be hidden and some visible by default.

The workbook has this:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Sheet1" Then ws.Visible = xlSheetVeryHidden
    Next ws
    
    ThisWorkbook.Save       'saves wb again without asking
    
End Sub

and the module:

Option Explicit

Sub PwdCheck()
Dim Pwd As String
    
    Select Case LCase(Application.InputBox("Please enter your password", "test", Type:=2))
        Case "john"
            Sheets("Sheet2").Visible = xlSheetVisible
        Case "sam"
            Sheets("Sheet3").Visible = xlSheetVisible
        Case "admin"
            Sheets("Sheet2").Visible = xlSheetVisible
            Sheets("Sheet3").Visible = xlSheetVisible
    End Select

End Sub

Hope someone can help me.

[111StepsAhead]

I would go about it by having a worksheet called "usernames" that is always xlSheetVeryHidden.

Worksheets("usernames") = xlSheetVeryHidden

And then in column A store the usernames and in column B store the passwords. (Username can be the name of the worksheet you are hiding.)

So have a userform popup with 2 different entries. 1 for the username and 1 for the password.

Search the hidden worksheet by looping through each value in column A. (http://www.mrexcel.com/td0058.html)

'pseudocode
For Each value in column A
     If userform username = seclected value in column A
          Check password.
          If password is ok
               Worksheets("username") = xlSheetVisible
          Else
                MsgBox "Wrong password."
          End If
      Else
          MsgBox("User does not exist."
      End If
Next value in column A

See what you can do with the above. It is pseudocode so it won't work but should help you get started.

Tutorial:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Sheet1" Then ws.Visible = xlSheetVeryHidden
    Next ws
    
    ThisWorkbook.Save       'saves wb again without asking
    
End Sub

Option explicit means you have to define all your variables as a data type.
So "Dim someVariable As String"
means you made a variable called someVariable and it will hold data of the type "String".

The next line means for each worksheet in the workbook you will check the sheets. If the first worksheet is not the first sheet then you hide it. Repeat and check all the worksheets.

The final line saves it.

If you want to select which sheets are hidden or not hidden you need to change that code.

'Wrong way
Worksheets(ws) = xlSheetVeryHidden

'correct way
Worksheets("PUT THE NAME OF ANY SHEET HERE") = xlSheetVeryHidden
Worksheets("PUT SOME OTHER NAME HERE") = xlSheetVeryHidden

Hope this gives you a good start.

[royUK]

Have a look at the password form

[marcielo]

Thank you for the replies

111StepsAhead: Without your help I would not have achieved - thanks a lot!!

royUK: Thank you for the file, but unfortunately I was not able to access the code, cause it wants me to enter a password no matter what I do.

Thank you again for taking the time!!!

[john55]

hi marcielo,
as an option
open the Roy's file with "Manager" and password "secret", or click on the tab name, view code and set to visible the Sheet1!

[joenathaniel26]

Hi Roy, thank you for the file, but when I tried it like in 3 instances the user have entered the wrong password, it says the workbook will now close, but it doenst really close, and to add, every time I close the worksheet, the sheet "hide this sheet" always becomes visible even I tried to put it, sheetveryhidden. thank you.