Unprotect sheets/workbooks by username

[jeffwest2]

By using the Environ("Username") I can get the name of the user accessing my workbooks, can someone give me an idea of how I could use this to decide if all sheets are protected (i.e. give people read only rights) or some unprotecetd (read/write rights)

I guess that it would be quicker to create a list of those with read/write rights and just give everyone else read only, but not sure how to go about it.

Any help would be appreciated.

[Andy Pope]

The code can be run when openning by placing it in the Workbook_Open event.

The issue is where are you going to store the information about who can do what?
You could use a table on a hidden sheet. In which case you can then use VLOOKUP formula, from code, to get access rights.

Of course storing access rights in the actual workbook may not be the safest thing, depends on your users knowledge and or curiosity.

[jeffwest2]

I agree about having the list within the workbook, more than likely i was thinking of a seperate password protected workbook, listing those that have permission to read/write, with anyone not on the list given read only access.

I've never been good with VLOOKUP and macros, can you give me a pointer to some code that might help.

[Andy Pope]

Assuming you have a table with names in column A and rights in B

AA Readonly
BB Readonly
CC Write
DD Readonly
EE Readonly

Then you can use

Please Login or Register  to view this content.

[jeffwest2]

Ok, so, adding this looks up the username on my hidden sheet, then looks at the user level and assigns this level accordingly, however.

Adding my username to the list and only giving myself readonly rights i always get the 'unknown user' error message, but this still give me read/write.

What am i doing wrong?

[Andy Pope]

Post your example workbook so we can see exactly what you are doing

[jeffwest2]

The rights are in a hidden sheet (Sheet1)

[Andy Pope]

You need to change the sheet reference. I used codename Sheet1, which is not the same as sheet tab Sheet1.

You sheet tab "Sheet1" is actually code named Sheet7

Please Login or Register  to view this content.

You also need to add a test for the returned value to determine whether to unhide/unprotect the sheets.

[jeffwest2]

I think i have it now, changing the sheet name now picks up the correct value, i already have some code elsewhere that removes/adds the security onto the individual sheets, so think i might have it sorted, thanks for the help.