Making certain cells unchangeable

philiplynch

New Member
Joined
Aug 20, 2007
Messages
11
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.
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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

New Member
Joined
Aug 20, 2007
Messages
11
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

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Have you actually protected the worksheet or just locked the cells? The locking will only have an effect when the sheet is protected.
 

philiplynch

New Member
Joined
Aug 20, 2007
Messages
11

ADVERTISEMENT

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

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,494
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

Well-known Member
Joined
Jan 5, 2007
Messages
4,284

ADVERTISEMENT

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

New Member
Joined
Aug 20, 2007
Messages
11
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

Well-known Member
Joined
Jul 18, 2003
Messages
4,494
Hi Lewiy,

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

philiplynch

New Member
Joined
Aug 20, 2007
Messages
11
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,400
Messages
5,601,467
Members
414,452
Latest member
Dannysamworth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top