restricting access using a macro

[Popeye the powerman]

hi there,
Does anybody know of a method for restricting access to a workbook based on
a list of people.
My problem is I have a workbook that two groups of people need to access 1
group as read only the other with red/write access.
I know I can use the password method but some of the read/write group are
moaning about having to enter a password every time.
So I was just wondering if there was a way of comparing the username of the
person who opens the workbook to a list within that workbook & allowing write
access if they match.
Cheers
Popeye

 

[Joel]

You could get the user name by using the envirn command

User = Environ("UserName")

You would have to make the VBA Project protected, but you could have a
workbook open macro that gets the user name and then unprotects the sheet in
VBA code. Then you would need a workbook close event to automatically
protected the sheets. I would also in the workbook open macro that if the
username did not have write protection to protect the sheets just in case the
workbook got closed without being protected.

You could also could use the XP or Vista protectection on the file.

 

[Shane Devenshire]

Hi,

You don't need to use a macro. Choose Tools, Protection, Allow Users to
Edit Ranges. Click New, delete the contents of the Refers to cells box and
click the top left corner of the spreadsheet (the intersection of the row
numbers and column letters). Add a password and click OK, confirm the
password and click OK. Click Protect Sheet enter the same password (or
another), confirm it and hit OK. With this approach anyone can open the
workbook but when they try to do any editing they will be prompted for a
password. You need to do this for each sheet you want protected.

Alternatively you can choose the command Tools, Options, Security, and enter
and confirm a Password to modify, in the File sharing settings for this
workbook area. Again, any user can open the file but they will be prompted
when they do for a password to modify or they can choose read only. In this
case you don't need to do it for each sheet. Depending on exactly what you
need this may be the better choice.

 

[Joel]

Shane: Read the original posting. The users DON'T want to type a password.