Here are the basics of what you'd need. Essentially we need to work with the
Workbook_Open() event and with one sheet's Worksheet_Change() event. The
first routine is pretty generic - make all worksheets except one invisible to
the user. The second routine handles details - setting up access to the
sheets/areas of the other worksheets based on whatever was typed into A1 on
the one visible sheet.
To see how this works, open a new workbook with at least 3 sheets in it.
Rename one of them SignIn that's our 'special'/always visible sheet where
your users would enter their password into cell A1. Leave the other two
sheets named Sheet2 and Sheet3 so that this sample code will work once it's
in place.
Here's the first piece you need - the Workbook_Open event code. To put it
into the proper place, right-click on the Excel icon immediately to the left
of the word "File" in the Excel menu toolbar and choose [View Code] from the
list and copy and paste this code into that module:
Private Sub Workbook_Open()
'this simply makes all worksheets, EXCEPT
'the one named SignIn "very hidden" which
'means they don't even show up in the list
'of worksheets available to be made visible
'in the Format | Sheet | Unhide list
Dim anyWS As Worksheet
'make certain that the sign in sheet
'is visible and no left over password in it
ThisWorkbook.Worksheets("SignIn").Visible = xlSheetVisible
ThisWorkbook.Worksheets("SignIn").Range("A1") = ""
For Each anyWS In ThisWorkbook.Worksheets
If anyWS.Name <> "SignIn" Then
anyWS.Visible = xlSheetVeryHidden
End If
Next
Set anyWS = Nothing
End Sub
Next we need some code to react to an entry made in cell A1 of the SignIn
sheet. Right-click on the name tab for the SignIn sheet and again choose
[View Code] from the popup list. Copy the code below and paste it into the
code module presented to you. This is just minimal code to give you an idea
of how things could work for you. We can get into specifics later if need
be, but it may be that these are the only examples you need to make it work
in the real workbook. Here's the Worksheet_Change() event code:
Private Sub Worksheet_Change(ByVal Target As Range)
'we are only interested in changes made to
'cell A1 on this sheet
'entries are treated like passwords -
'i.e., they are case sensitive (Bill not same as bill)
'any keyboard character is valid,
'no leading or trailing white space,
'and first character cannot be single quote mark
'
'"ralPH\*49 golf5h0t!" is valid
'but " ralPH\*49 golf5h0t!" is not
'and "'ralPH\*49 golf5h0t!" is not
Dim passwordEntered As String
Dim anyWS As Worksheet
'start by making all other sheets very hidden again
For Each anyWS In ThisWorkbook.Worksheets
If anyWS.Name <> "SignIn" Then
anyWS.Visible = xlSheetVeryHidden
End If
Next
Set anyWS = Nothing
If Target.Address <> "$A$1" Then
Exit Sub
End If
passwordEntered = Trim(Target.Text)
Select Case passwordEntered
Case Is = "ralph\*49 golf5h0t!"
'make specific sheets for this
'user visible, and/or set up
'cell protection/access on the
'sheets you do make available
'to this user.
ThisWorkbook.Worksheets("Sheet2").Visible = _
xlSheetVisible
Case Is = "password#2"
'make specific sheets for this
'user visible, and/or set up
'cell protection/access on the
'sheets you do make available
'to this user.
ThisWorkbook.Worksheets("Sheet3").Visible = _
xlSheetVisible
Case Is = "password#3"
'make specific sheets for this
'user visible, and/or set up
'cell protection/access on the
'sheets you do make available
'to this user.
ThisWorkbook.Worksheets("Sheet2").Visible = _
xlSheetVisible
ThisWorkbook.Worksheets("Sheet3").Visible = _
xlSheetVisible
Case Is = "password#4"
'make specific sheets for this
'user visible, and/or set up
'cell protection/access on the
'sheets you do make available
'to this user.
'doesn't do anything right now
Case Else
'not a recognized/valid password
MsgBox "Your entry is not a valid password. Check spelling and
punctuation.", _
vbOKOnly + vbExclamation, "Password Not Recognized"
End Select
End Sub
If you need more detailed assistance with things like using passwords on the
worksheets or making specific ranges on the sheets available/unavailable
based on the user who logged in, might be better if you got in touch with me
via email (remove spaces to make valid email address) at
Help From @ JLathamSite .com
