password protect workbook based on user level?

[mishmish3000]

Howdy! Is there a way to have an Excel workbook with several sheets in it open differently for different users? I have some sheets I only want the nurse administrators to see, and some sheets that all the nurses can see, and one sheet only I can see... is there a way to set up a password that would be based on these three different criteria? I've not worked much with passwords and Excel before.
Thanks
MishMish

For example--see attached:
Workbook title: "Perinatal Hep B Data 2010"
Tab1 is "PNHB_Monthly_Data", which can be seen by everyone--nurses, nurse administrators, and me.
Tab2 is "Monthly_TTLS" which can be seen by the nurse administrators and me.
Tab3 is "Data_Crunching" which can only be seen by me.

[HansV]

The attached workbook (.xlsm format) shows a rather primitive way of doing it.
The last two sheets are hidden, and they can't be unhidden by the usual means.
I have added a "Start" sheet with three buttons to activate each of the other sheets.
You'll have to provide a password for the last two sheets.
To prevent users from looking at the VBA code, the VBA project has been password-protected too.
In this example, the passwords are listed on the Start sheet. You'd remove them in a "real" version of course.

Note: users need to enable macros in order to be able to click the buttons, for example by placing the workbook in a trusted location. If macros are disabled, there is no way to unhide the last two sheets.

Perinatal Hep B Data 2010.xlsm

[Don Wells]

Hi Hans
    Can you advise of any drawback to writing the first procedure of the project you provided, to the code shown below?

Code: Select all

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Workbook_Open
End Sub

[HansV]

None. You could also create a separate procedure, either in ThisWorkbook or in a standard module:

Code: Select all

Sub SetStart()
  Worksheets("Start").Select
  Worksheets("Monthly_TTLS").Visible = xlSheetVeryHidden
  Worksheets("Data_Crunching").Visible = xlSheetVeryHidden
End Sub

and call it from both Workbook_BeforeSave and Workbook_Open:

Code: Select all

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Call SetStart
End Sub

Private Sub Workbook_Open()
  Call SetStart
End Sub

[Don Wells]

Thank you Hans

[Reimer]

Don,
If there are not too many users involded, you could unhide sheets based on the login. The problem with this method is that maint could be a killer if there are changes to the list of users regularly. The benefit is that the user would not have to remember a password.