Give permission for specific ranges to variable users

There are probably a number of ways however the basic principle should be:

In the Workbook_Open event

Check the username, this all depends on how you have your users set up. Do they have to log into the system? or can you use the User info from windows to identify them? (for instance, our systems in my workplace hold the persons Staff Number as the login user so we are able to refer to that automatically to identify the user. You can use

Please Login or Register  to view this content.

to get the logged in user however depending on how your network is set up that may not contain the unique info you need.

Once you have the username then check it against a list in the workbook to see if its one of the allowed users. (depending on the importance of this data you may wish to make this a little more secure such as querying a database etc so the table of users cant be manipulated).

If so, run a Sub that unprotects the relevant ranges.

Something to note though, if the workbook will then be saved by the user for the next person to access you'll need to reset all of the Protections before closing the sheet otherwise it will be open by default for the next user.