Password protection/access

[kefee85]

I have a workbook consisting of 14 separate worksheets. I would like for
each department to be able to access only their individual worksheet and two
summary worksheets, but not be able to view the other worksheets.

 

[JBeaucaire]

If you mean allow the "access to their sheet and let them change
things"...you'll need some pretty strong VBA code to do that.

If you mean "access their sheet and summary to read only"...then the answer
is simple. PDF files.

I use a free tool called PrimoPDF that installs a printer driver, let's me
create PDF files from inside any program. Create a PDF of their department
sheet and the summaries, send them that.

Our company stopped passing ACTUAL Excel files around years ago and we've
never regretted it.

My two cents... PrimoPDF.com

 

[JBeaucaire]

If you mean allow the "access to their sheet and let them change
things"...you'll need some pretty strong VBA code to do that.

If you mean "access their sheet and summary to read only"...then the answer
is simple. PDF files.

I use a free tool called PrimoPDF that installs a printer driver, let's me
create PDF files from inside any program. Create a PDF of their department
sheet and the summaries, send them that.

Our company stopped passing ACTUAL Excel files around years ago and we've
never regretted it.

My two cents... PrimoPDF.com

 

[Paul C]

If you hide all but the summary sheets and protect the workbook with a
password, you can then put in a auto_open macro to prompt for a password and
unhide sheets based on the response.

You would also need a auto_close macro to hide all of the sheets again for
the next user. You can get much fancier and do a custom form that uses
password characters, but that gets more involved.

In this simple version if the user hit cancel or enters an unrecognized
password no sheets will be unhidden. For added security protect the VBA
project so no one can get to the code and find the workbook password.

Something like this:
Sub Auto_Open()
x = InputBox("Enter Dept Password")

ActiveWorkbook.Unprotect Password:="password"
If x = "accounting1" Then Sheets("Sheet1").Visible = True
'ADD OTHER CONDITIONS

ActiveWorkbook.Protect Password:="password", Structure:=True, Windows:=False

End Sub

Sub Auto_Close()
ActiveWorkbook.Unprotect Password:="password"
Sheets("Sheet1").Visible = False
'ADD OTHER SHEETS

ActiveWorkbook.Protect Password:="password", Structure:=True, Windows:=False
End Sub

 

[Paul C]

If you hide all but the summary sheets and protect the workbook with a
password, you can then put in a auto_open macro to prompt for a password and
unhide sheets based on the response.

You would also need a auto_close macro to hide all of the sheets again for
the next user. You can get much fancier and do a custom form that uses
password characters, but that gets more involved.

In this simple version if the user hit cancel or enters an unrecognized
password no sheets will be unhidden. For added security protect the VBA
project so no one can get to the code and find the workbook password.

Something like this:
Sub Auto_Open()
x = InputBox("Enter Dept Password")

ActiveWorkbook.Unprotect Password:="password"
If x = "accounting1" Then Sheets("Sheet1").Visible = True
'ADD OTHER CONDITIONS

ActiveWorkbook.Protect Password:="password", Structure:=True, Windows:=False

End Sub

Sub Auto_Close()
ActiveWorkbook.Unprotect Password:="password"
Sheets("Sheet1").Visible = False
'ADD OTHER SHEETS

ActiveWorkbook.Protect Password:="password", Structure:=True, Windows:=False
End Sub