PDA

View Full Version : Locking a certain number of Cells in an Excel Sheet



abhivics
2012-02-29, 07:43 AM
Hello Guys,

Is there a way to lock a certain number of fields in Excel sheet. ie. no one is able to access amend them except the administrator who has the password for it.
Although I have just done a work around by putting a data validation on the fields .

Ken Puls
2012-03-28, 06:37 AM
Do you have an Active Directory domain?

Roger Govier
2012-04-01, 12:35 PM
Hi
By default all cells in a sheet are Locked, so as soon as you Protect a sheet, you are unable to change them.
So, select all of the cells other than those you want to be locked, and then choose Format Cells>Protection and remove the check mark.
Now, protect your sheet with a Password and those cells you have chosen will be locked from user amendment.

Ken Puls
2012-04-01, 11:26 PM
Hiya Roger,

The question was a little ambiguous, but I was thinking something bigger that dealt with specific users:

Starting with Excel 2002, if you have a domain at work, you can lock cells and give permissions to certain individuals to be able to edit them. To do this:

Excel 2002/2003 Tools-->Protection-->Allow Users To Edit Ranges
Excel 2007+ Review-->ChangesàAllow Users To Edit Ranges
The dialog box that results is shown below. Its concept is that you need to specify the ranges that users CAN edit. To do so you’d click New.

529

Once done, you’ll see the dialog box shown below:

530

Give the range a title and a range then also provide a password. This is a very important step, as if you do not provide a password then this setup will have no effect.

Finally, click Permissions and add the users you wish to include. And don’t forget to include yourself or you may also be locked out (which can, of course, be undone by unprotecting the workbook).

While we have tested this interesting feature in our offices, I admit it is not one we use, as it seems like it would be difficult to maintain. Regardless, it could offer value in some situations.