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.

 

[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.

 

[Lewiy]

This might be a stupid question, but did you save it after protecting and before you closed and opened it again?

 

[Lewiy]

Try this step by step:
Open a brand new worksheet
Highlight cells A1:A10 and go Format>Cells>Protection
Un-tick the Locked box
Click OK
Go to Tools>Protection>Protect Sheet

At this point it depends what version of Excel you are running, but in 2003 ensure that the “Protect worksheet and contents of locked cells” box is ticked. Then enter a password and click OK.

Confirm the password and click OK

You do not then need to close and/or save the worksheet. The result should be instant. Try entering something in A1:A10, this should be fine. Any other cell should tell you that the worksheet is protected and you can’t edit its contents.

 

[ADAMC]

EDIT

Hi all

Leading on from this funnily enough this is something i have been playing around with also and i had an idea which may help if it can be adapted.

I have columns i dont want changing so playing around with VBE (Which i know very little of)...i was thinking.....
I have my data in column A.....I have the same data somewhere obscure somewhere like A:AZ perhaps hidden..?

Some code like:

Private Sub Worksheet_Change(ByVal Target As Range)
If Sheets("Sheet1").Range("A1:A10") <> Range("AZ1:AZ10") Then
MsgBox "You cannot change the values", vbOKOnly, Warning!
End If
End Sub

Then perhaps when you press OK it either performs edit undo last action OR it copies the whole column from the hidden range and puts it back in the column where the deletion was attempted......?

I know the code needs lots of work i just thought id add to this post rather than starting a new one!

Thanks

 

[philiplynch]

Try this step by step:
Open a brand new worksheet
Highlight cells A1:A10 and go Format>Cells>Protection
Un-tick the Locked box
Click OK
Go to Tools>Protection>Protect Sheet

At this point it depends what version of Excel you are running, but in 2003 ensure that the “Protect worksheet and contents of locked cells” box is ticked. Then enter a password and click OK.

Confirm the password and click OK

You do not then need to close and/or save the worksheet. The result should be instant. Try entering something in A1:A10, this should be fine. Any other cell should tell you that the worksheet is protected and you can’t edit its contents.

Oh man Lewiy, thanks so much.... I made a stupid mistake. When I went to protect the sheet, I selected Protect workbook instead of sheet. Oops. Thanks for your help!!!