Admin password to Individual protected worksheets by users

[mohammed.mansoor.ali]

I have an excel workbook where each individual sheet is protected by
unique user.

I want to add a admin id & password to this workbook so that the admin
can access all sheets at the same time. (unprotect, unhide all
sheets).

The code I m currently using comes from here:

http://exceltips.vitalnews.com/Pages/T0087_Protecting_Individual_Worksheets_by_User.html

 

[Trevor Shuttleworth]

I'd be inclined to store the individual user names and passwords in another
sheet in the workbook. If you make this "very hidden" then users will not be
able to see it or unhide it without knowing the password for the VBA
Project.

If the Project isn't already protected, anyone could look at the code anyway
and see all the passwords. And having passwords built in is a bit
restrictive.

Assuming you do list all the user names and passwords in a sheet, you could
have a piece of code that cycled through all the sheets/users and unlocked
the sheets ... and another to reset them.

Regards

Trevor

 

[Harlan Grove]

I'd be inclined to store the individual user names and passwords
in another sheet in the workbook. If you make this "very hidden"
then users will not be able to see it or unhide it without knowing
the password for the VBA Project.

Dead wrong. Dangerously wrong.

Assume X.xls has a very hidden worksheet. In any other workbook,
insert a new visible module, enter the code

Sub bar()
Dim ws As Worksheet
For Each ws In Workbooks("x.xls").Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub

and run it.

IF a user can OPEN an .XLS file, that user can access ANYTHING in
that .XLS file without all that much effort. The ONLY strong password
protection Excel provides are file open and file modify passwords. All
other passwords are easily defeated.

And the point you need to try to remember: any macro in any open Excel
workbook can operate on any other open Excel workbook.

If you can't trust users, you shouldn't be putting sensitive data
into .XLS files.

 

[mohammed.mansoor.ali]

I tried making the sheet as very hidden but it doesn't work, can't
figure out why.

And any ideas abt the admin password.

 

[Harlan Grove]

(e-mail address removed) wrote...

I tried making the sheet as very hidden but it doesn't work, can't
figure out why.

Very hidden is easily bypassed. Don't bother with it.

And any ideas abt the admin password.

If you need as high security as Excel provides, break this up into
different workBOOKs for each non-admin user, and give each of these
workBOOKs a different file-open password. If only the specific non-
admin users know only the passwords for their respective workBOOKs,
then only they amonh the non-admin users should be able to open them.

Also create an admin workbook, which should have a file-open password
known only to the admin users, containing a worksheet with the
filenames and file-open passwords of the non-admin users' workBOOKs.
Include an Open event handler in the admin workbook's ThisWorkbook VBA
class module that iterates through the list of non-admin users'
workBOOKs opening them with their file-open passwords. Whether your
admin users then use them as separate workbooks or move them into the
admin workbook is up to you.