I think that you should be able to achieve it with macros.
First, you need to add a fourth (can be blank) sheet. This is because at least one sheet needs to be visible at the point that the user enters the password.
Then, this is the macro code that you need:
Code:
Sub VeryHideMacro()
Sheets("A").Visible = xlVeryHidden
Sheets("B").Visible = xlVeryHidden
Sheets("C").Visible = xlVeryHidden
End Sub
Private Sub Workbook_Open()
EnterPasswordMacro
End Sub
Sub EnterPasswordMacro()
VeryHideMacro
ValidPassword = False
PasswordEntered = InputBox("Enter Password")
If PasswordEntered = "PasswordA" Then Sheets("A").Visible = True: ValidPassword = True
If PasswordEntered = "PasswordB" Then Sheets("B").Visible = True: ValidPassword = True
If PasswordEntered = "PasswordC" Then Sheets("C").Visible = True: ValidPassword = True
If PasswordEntered = "Admin" Then Sheets("A").Visible = True: Sheets("B").Visible = True: Sheets("C").Visible = True: ValidPassword = True
If ValidPassword = False Then MsgBox ("Invalid password entered."): ActiveWorkbook.Close (False)
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
VeryHideMacro
End Sub
You will then need to password protect the project with a password that only you will know - this stops the users being able to find the other passwords in the text of the macro. To do this, in the Project area of the VB screen (usually top left), collapse the VBA Project... line, then right click on it and select VBA Project Properties. On the Protection tab, click Lock project for viewing, and enter the password that you've created for yourself in the boxes below. If at any future stage you need to look at or edit the macros, you will be prompted for this password.
VeryHide is a way of hiding worksheets so that they can only be unhidden by macros. The macro
VeryHideMacro() is used before the workbook is saved (so that it isn't saved with sheets A, B and C visible) and when it is first opened.
When the workbook is opened, the macro
EnterPasswordMacro() is triggered. This is a message box asking for a password. In my example I have made the passwords PasswordA for sheet A, PasswordB for sheet, PasswordC for sheet C and Admin for all sheets ... you can obviously set these to whatever you like. If an invalid password is entered, a message is displayed, then the workbook is closed.
Note that the text is visible as it is being entered. If this is a problem, it may be possible to get round it by using a VB form for the password entry...
It is necessary to VeryHide all sheets when the workbook is saved, to ensure that if the file is opened by a user whose Excel settings disable macros, only the blank sheet is visible. However this does mean that if a user saves their work, but then wants to carry on working, they will need to re-enter their password. This can be done by selecting Macros from the toolbar (or Alt-F8), then running the EnterPasswordMacro - alternatively you may want to add a button for that macro on the spare blank worksheet.
I've tried all this on a test workbook and haven't been able to easily break the security - but I'd suggest that you do the same before applying it to your live workbook, to ensure that you're satisfied. Also bear in mind that this won't restrict the ability of users to edit the sheet(s) that they can see - if you want to do this, you'll need to use Excel's standard worksheet protection as well as these macros.
