Vlookup Environ ("username") to get proper name and allocate protection

[nickmax1]

I have a spreadsheet that about 5 users have access to .
I wish to have a macro that identifies the users full names based on the environ username in the first colomn and allows the user to control the spreadsheet dependant on their level of authority.

So for example in sheet "Users" I have identified the following usernames with their corresponding full names.

nichampness Nick Champness Full access (ie edit, delete rows etc)
laclarke Linda Clarke Full access but no deletion capability
ABloggs Andy Bloggs Autofilter access, edit access, no delete ability

Anyone else who is not in the list above has read only access

I want a msgbox to pop up, and for it to say "Welcome FULL NAME, you have INSERT ACCESS TYPE access"

[Fettertiger]

Hi NickMax1,

as I was not sure which table should be protected the macro will protect sheet1 only. In case you would like to protect the "users" worksheet, you could simply replace sheet1 by users.

Please regard that the accesslevels (as defined in column C of the users spreadsheet, would need to be spelled exactly as you have mentioned it below ( "Full access (ie edit, delete rows etc)", "Full access but no deletion capability" , "Autofilter access, edit access, no delete ability" ) otherwise the macro would set the level to "read only access" to makes sure!

The macro should not be pasted into a module, but should be put directly into "ThisWorkbook" to ensure that it will run when the file is opened. For your reference I have attached a testfile

Please Login or Register  to view this content.

In case you would require additional access levels. you could simply record a "manual protection" and adjust the code accordingly.
Hope it works as expected

Regards

Theo

[nickmax1]

Wow that is great Fettertiger.

I had to change your Application.UserName commando Environ ("username") commands but other than that it works. How do I change this code so that it protects and unprotects all sheets in the workbook with the exception of "Start" and "Template" ?

[nickmax1]

anyone help with this?

[nickmax1]

basically what i mean is that currently the protection applies to Worksheets("Sheet1").Unprotect Password:=PW, but i want it to apply to ALL sheets in my workbook with the exception of Start and Template.

[Fettertiger]

Hi nickmax,

sorry for not replying any earlier, but I was not online. Try this:

Please Login or Register  to view this content.

Just change your environ routine as you did before and it should work


Regards

Theo

[nickmax1]

works brilliantly!

solved