hide / unhide separate sheets based on password

1) You can't hide ALL the sheets, obviously, so let's keep Sheet1 visible. Put this in the ThisWorkbook module to accomplish that.

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
        ThisWorkbook.Save       'saves wb again without asking
    Next ws
    
End Sub

2) This macro can be used to prompt a user for a password to ID which sheets should be made visible

Option Explicit

Sub PwdCheck()
Dim Pwd As String
    
    Select Case Application.InputBox("Please enter your password", "Your password", 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

That can be attached to a button, or even reconfigured into a Workbook_Open prompt.

Good afternoon spudinsane

I have a template available that does exactly what you require. It stores a list of users and passwords, and which sheets are to be accessed by which users. You, as administrator, can see all worksheets and control which sheets users can access. This was originally developed for XL2003, but should work fine on 2007.

The security on this isn't 100%, as we are using Excel, which by its very nature is not completely secure, but it should keep out most nosey parkers.

If you prefer to write something of your own, then all my code is unprotected, so you could use that to get some ideas, or fine tune the workings.

If you would like a copy of this template, then please PM me your e-mail address.

HTH

DominicB

A couple of tweaks. First, I was saving that wb too many times, here is the corrected code for ThisWorkbook module:

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

Then, the PwdCheck macro goes into a code module, not the sheet module. (Insert > Module). A small tweak for that as well:

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

When I type in "john" it unhides sheet2 as expected and when I close the workbook it hides all and saves as expected.

I've posted this several times


PASSWORD FORM4.zip

you could add back in the same "hideall" logic used in the other macro so each time the macro is run, all sheets hide again.

Option Explicit

Sub PwdCheck()
Dim Pwd As String
Dim ws  As Worksheet

Sheets("Sheet1").Activate
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Sheet1" Then ws.Visible = xlSheetVeryHidden
    Next ws

    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

You can password protect your VBA by right-clicking the VBAProject and going into Properties.

Hi sigeg... Welcome to the forum.

Be sure to read through the Forum Rules so you can use and follow them effectively. For instance, you'll need to start your own thread and ask your questions there, include your own sample sheets, and if you find this thread possibly relevant, include a link to this thread.

Forum rules forbid hijacking one thread with new questions of your own, this is meant to benefit you, to insure the most possible new eyes look at your new question(s).

As Jerry points out, your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.