How can I protect a number of cells from being modified by users?

You need to password protect the sheet.

Review tab >> Protect sheet

does excel offer the ability to selectively protect a single cell or range.

I know they have the "allow users to edit ranges" function but i can never seem to get it to work properly.

and if i select all cells, unprotect, then lock only the cells in the range i want locked, then enable the user to do everything except select locked cells. Then the user still doesn't have access to a lot of functions. It seems very limited in it's functionality or maybe i'm just not using it right

Yes you can limit the protection to a single cell if you so desire.

Ctrl + 1 >> Format cells >> Protection tab >> Locked (Check or uncheck) >> Protect workbook

JTwrk,

Please follow the forum rules and create your own thread if you have a question. I answered the first one, but this thread belongs to TallOne and his/her query.

Originally Posted by TallOne

Hi Jeff

Thanks for your reply, I don't want to protect the whole worksheet, I only need to protect a number of cells in a worksheet, and in the same time the user can edit the unprotected cells. I followed your steps in (#4) but does not work

Thanks

How much functionality do the users need in editing the cells

You can select all cells>format cells>protection>unprotect/unclick the locked box

then select the range you want protected>format cells>protection>Locked

then go to the protect worksheet button, choose what you want users to be allowed to do and then protect the worksheet. Users will be allowed to edit the contents of unlocked cells and won't be allowed to edit the contents of your locked range.

However, when you do this even if you checked every box users are barred from certain functionality. For example lets say you only protected a range of helper cells in a column to the far right of your info range, cells that had constants or calculated values that the user didn't need to see or edit and that fed into the info range.

Now you want to filter on your info range or turn off the autofilter that's already on your info range, oh wait you can't because excel has barred you from that functionality even though you clicked every check box. You can only use the autofilter if it was already turned on before the sheet was protected and once it is protected you can't turn it off without unprotecting. And if it wasn't already on, tough luck you can't turn it on even though it has no impact on your locked range.

I find it frustrating and it's why i think excels protect function is very limited in it's functionality.

Hi TallOne,

You could use event code like this in that sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.row = 1 Then
If Target.Column < 6 Then
Cells(Target.row + 1, Target.Column).Select
End If: End If: End Sub

Maybe reading through some tutorials on-line may help...

This is just one of many...

http://spreadsheets.about.com/od/exc...8_lockcell.htm

Hi TallOne,

Here's a book:

TallOne.xls