Making certain cells unchangeable

[philiplynch]

Hi,

I am preparing an excel spreadsheet to be used as an order form. There are certain columns that I do not want customers to be able to change, for example unit price, but I would still like them to see it. Is it possible to have only certain fields that can be changed and others that are protected?

Thanks.

 

[Lewiy]

Welcome to the board!

All cells by default are "locked". If you go to Format>Cells>Protection you will see the Locked box is ticked. Select the cells that you want to be changeable and un-tick their Locked box. Then when you go Tools>Protection>Protect Sheet. It will only protect those cells whose Locked box is still ticked.

Hope that helps!

 

[philiplynch]

Ok, thanks for the information Lewiy. Problem is I selected to have them locked and it says that, but when I open the file, I can easily edit the contents of the columns...

 

[Lewiy]

Have you actually protected the worksheet or just locked the cells? The locking will only have an effect when the sheet is protected.

 

[philiplynch]

Ok, I'll give that a try, thanks.

 

[al_b_cnu]

Hi,

There is, of course, the 'poor mans' cell protection, namely Data Validation.

Set data validation to custom with the formula =And(false(),true())

You can even output a customised error message.

 

[Lewiy]

There is, of course, the 'poor mans' cell protection, namely Data Validation.

Set data validation to custom with the formula =And(false(),true())

You can even output a customised error message.

Not sure as that really helps because you can still clear the value of the cell, you just can’t change it. If you attempt to change the value of the cell, you will get an error message but the only thing you can then do is make the cell blank, you can’t even return it to it’s original value.

 

[philiplynch]

It doesn't seem to be working. I lock the file, but I can still edit it. Is that because I am opening it on the same computer?

 

[al_b_cnu]

Hi Lewiy,

You're right of course.
I suspect that sheet protection will be much more efficient too!

 

[philiplynch]

Hi guys, I really appreciate your help so far. Thank you so much. I am still having trouble with this. I will tell you what I did. I selected 5 of the 6 columns, and under Protection selected Locked. The first cell is user specified so I kept that one unlocked. Then I Protect Workbook and select Structure, and type in a password. Everything is fine, until I close and open the document again, then I can STILL change the values and save it.