Allow access to specific users for protected cells (Excel 2003)

**zpphillips** · 03-18-2010, 11:46 AM

HI All,
I am creating a form where I want the whole sheet to be protected. This way users will be unable to change information on the sheet. BUT, I want a select few users to be allowed access to a few specific fields on the protected sheet without having to unprotect the whole sheet, make the changes, and then protect it again.

Is there a way this can be done?

Notes: I feel using the "Allow Users to Edit Ranges" option in tools - protection - allow users to edit rangers, should accomplish this. When i click that option, then click new, select the range I want user X to access w/o using a password, click "permissions" and add user X to the security list, and then protect the sheet, user X is unable to click on that one field. What am I doing wrong?

**dominicb** · 03-18-2010, 12:05 PM

Good afternoon zpphillips

The Allow Users to Edit Ranges thing should be perfect for your requirements. How are you populating the security list? Are you just typing user X's name in?

Try going to Permissions, Add, Advanced, and find the user in the Common queries box (you may well find it is tied in with their log in). I've just tried it this way and it worked perfectly for me.

HTH

DominicB

**zpphillips** · 03-18-2010, 01:05 PM

DominicB,
The users allowed are connected to a network. When i type their name in, it self populates using their email address connected to the network.

When I go to permissions, and add myself to be allowed to use the cells w/o typing a password then save and then protect the sheet, it still won't let me click the cell.

Is there anything to do with file - permissions - restrict permission as .... or anything to do with permissions?

Also, when you go to the cell format, is the cell locked? I don't want anyone but the approved user to be able to click the cell.

Are there any specific cell properties that need to be changed before this can work?

Thanks for the response by the way

**zpphillips** · 03-22-2010, 04:17 PM

Does anyone know how this will work w/ out macros? I can't seem to get it to work.

I lock the cell. Go into the protection - allow users to edit ranges - assign the users to certain cells - and save.

I apply the protection for the whole sheet and then when i try to click the cell (as i am one of the names i put to be allowed to edit the locked cell) it doesn't select.

Does anyone know why?

**dominicb** · 03-25-2010, 05:01 AM

Hi zpphillips

Take a look at the attached file. You won’t be able to enter in any cells on the workbook – if you try to edit the cells that are shaded in yellow you will be prompted for a password (which is pass). I can only edit the yellow cells – but don’t need a password. I’ve also checked it on a colleague’s machine, and she can’t alter the shaded cells without a password.

This is a step by step guide to what I've done :

Tools > Protection > Allow users to edit ranges

Select New, leave the title as Range1 and set the range as =$A$1:$E$2,$A$7,$C$7,$E$7,$H$1:$H$7

The set the password as “pass”
Click on the “Permissions” button
Click on the “Add” button
Click on the “Advanced” button
In the Common Queries tab, left as “Starts with” and entered DJ in the box and clicked Find Now. Mine was the only name there. Selected it.

Make sure that object types has at least these ticked :
Built in Security Principals
Groups
Users

Just make sure that the location is where you want to search.

Then click OK in all the boxes to get back to the first dialog. Then click the Protect Sheet button (I haven’t assigned a password).

HTH

DominicB