Restrict access to worksheet by password

[logstarter]

Hi,

I have two worksheets, named as A and B. I would like to assign two different password to A and B. And if the user input A's password, the worksheet A will be shown.. and same for worksheet B. Some cell values in B is referring to A, thus, I would like to make sure B also displays the value properly even it only input the password for worksheet B.

I would like to ask if it's feasible to do that by Excel function or VBA.

Thank you.

 

[Yongle]

VBA option - test EXACTLY like this and then amend to suit your own requirements after you have completed step 10

1 close Excel completely & reopen Excel

2 create a NEW workbook with 3 sheets

3 name the sheets A & B & X respectively (a single letter name NOT SheetA etc)

4 paste code below into a STANDARD module (see NOTE 1 below)

Public VisibleSheet As Worksheet

Sub EnterPassword()
    Const pwA = "aaa"                   'password for sheet "A"
    Const pwB = "bbb"                   'password for sheet "B"
    
    Select Case InputBox("Enter Password", "Show sheet")
        Case pwA:   ShowSheet Sheets("A")
        Case pwB:   ShowSheet Sheets("B")
        Case Else:  ShowSheet Sheets("X")
    End Select
End Sub

Sub ShowSheet(aSheet As Worksheet)
    Dim sh As Variant
    On Error Resume Next
    Set VisibleSheet = aSheet
    If VisibleSheet Is Nothing Then Set VisibleSheet = Sheets("X")
    On Error GoTo 0
    VisibleSheet.Visible = xlSheetVisible
    For Each sh In Array(Sheets("A"), Sheets("B"), Sheets("X"))
        If Not sh.Name = VisibleSheet.Name Then sh.Visible = xlSheetVeryHidden
    Next
End Sub

5 paste code below into ThisWokbook module (see NOTE 2 below)

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    If VisibleSheet Is Nothing Then Set VisibleSheet = Sheets("X")
    ShowSheet VisibleSheet
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Me.Sheets("X").Visible = True
    Me.Sheets("A").Visible = xlSheetVeryHidden
    Me.Sheets("B").Visible = xlSheetVeryHidden
End Sub

Private Sub Workbook_Open()
    Set VisibleSheet = Sheets("X")
    EnterPassword
End Sub

6 Save the workbook as MacroEnabled workbook
- now only sheet X should be visible

7 {ALT}{F8} to show list of macros
- select and run EnterPassword and enter bbb to make B visible

8 Save the workbook again - whichever sheet was visible before should still be visible

9 Save, close and reopen the workbook - now only sheet X should be visible
- enter password aaa or bbb to make either A or B visible

10 If something did not work try again FROM scratch!


NOTE 1
To paste code into a STANDATD module
{ALT}{F11} takes you to VBA window
{ALT} I M toInsert a standard Module
Paste code into the window which appears
{ALT}{F11} takes you to back to Excel

NOTE 2
To paste code into a ThisWorkbook module
{ALT}{F11} takes you to VBA window
Double click on ThisWorkbook in left windowpane (Projects window)
Paste code into the window which appears
{ALT}{F11} takes you to back to Excel