OK so...
You can password protect a workbook so only people with the password can access it. You only get one password for this feature
You can instead use VBA to deny users access by asking them to enter their details, and kicking them out again if they give the wrong info. This requires macros to be enabled or it won't ask the questions, and it only runs once the file is open. You can force users to have macros enabled by ensuring the system is locked down whenever it is saved, such that it can only be opened again by VBA. The issue is that someone skilled in VBA WILL be able to get it open quite easily, and see all the info in it. If people use the same passwords here as they use for more private stuff then you need to consider your data protection obligations - e.g. you need to tell them the system is not secure, or assign a password to them which they cannot change (I'd recommend this one, as they won't get to see the password sheet)
For this idea - which is untested, and for you to decide whether it is suitable for your needs - you'll need:
- Sheet1 is a splash screen, containing a big message that says something like "you must enable macros in order to proceed"
- Sheet2 is a sheet that needs to contain a range named "PWs", which is a two column range of cells, column 1 = staff name, column 2 = password. This sheet should be "very hidden" at all times (change the setting via VB Editor Properties window, not via Excel)
This code in a standard code module:
Code:
Option Explicit
Public Const PW As String = "myPassword"
Sub applyLockdown(state As Boolean)
Dim ws As Worksheet
Application.ScreenUpdating = False
Select Case state
Case True ' show splash screen, very hide all others
Sheet1.Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> Sheet1.Name And ws.Name <> Sheet2.Name Then ws.Visible = xlSheetVeryHidden
Next ws
ActiveWorkbook.Protect Password:=PW, Structure:=True, Windows:=False
Case False ' very hide splash screen, password sheets, show all others
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> Sheet1.Name And ws.Name <> Sheet2.Name Then ws.Visible = xlSheetVisible
Next ws
Sheet1.Visible = xlSheetVeryHidden
ActiveWorkbook.Unprotect Password:=PW
End Select
Application.ScreenUpdating = True
End Sub
This code in the ThisWorkbook code module:
Code:
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
applyLockdown True
End Sub
Private Sub Workbook_Open()
' declarations
Dim strThisUser As String, strThisPassword As String
Dim strOpenMessage As String, intOpenButtons As Integer, strPwMessage As String, intPwButtons As Integer
Dim rngPWdata As Range: Set rngPWdata = Range("PWs")
' default message data
strOpenMessage = "this system is locked. Enter password to continue"
intOpenButtons = vbExclamation + vbOKCancel
strPwMessage = "enter password"
intPwButtons = vbExclamation + vbOKCancel
' ask user for ID details, retry if not found
repeatID: ' named location
strThisUser = MsgBox(strOpenMessage, intOpenButtons)
If strThisUser = "" Then ' action cancelled, close file without saving changes
ThisWorkbook.Close savechanges:=False
Exit Sub
End If
If WorksheetFunction.CountIf(rngPWdata.Columns(1), strThisUser) = 0 Then ' user not found, try again
strOpenMessage = "ERROR: User not found"
intOpenButtons = vbCritical + vbOKCancel
GoTo repeatID
End If
' ask user for password, retry if not found
repeatPW:
strThisPassword = MsgBox(strPwMessage, intPwButtons)
If strThisPassword = "" Then ' action cancelled, close file without saving changes
ThisWorkbook.Close savechanges:=False
Exit Sub
End If
If WorksheetFunction.VLookup(strThisUser, rngPWdata, 2, False) = strThisPassword Then ' correct password, proceed
applyLockdown False
Exit Sub
Else ' incorrect password, try again
strOpenMessage = "ERROR: incorrect password"
intPwButtons = vbCritical + vbOKCancel
GoTo repeatPW
End If
End Sub
Notes: Sheet1 and Sheet2 are the VBA names of these worksheets, not the names on the tabs in Excel. You'll see these in the VB Editor window, folder structure on the left. You should also consider password protecting your VB Code (VB Editor, Tools > VBA Project Properties) otherwise your password is visible, as is the code that controls this feature
Additional Warning: You are now playing with features that involve closing the file, and which decide whether or not to save changes. Be careful when testing your code!