Protection

You can protect cells by selecting the cell, go to format cell, go to protection, select locked.
Then go to tools>protection>protect sheet.

Hi rick

What you are saying will protect the entire sheet what i am trying to do
is to ptotect one cell or a range without protecting the whole sheet.
you will need some kind of a script to do that.

Thank you

First select all cells and go to Format>Cells>Protection and de-select Locked.
Then select the cells you want to lock and go to Format>Cells>Protection and select Locked.
Then go to Tools>Protectio>ProtectSheet.

Hi Rayn

As has been said you can Unlock the cells you do not want protected and then Protect the sheet. The downside to this of course is that a lot of Excels standard features are not available to any of the cells Locked or Unlocked. Here is some code to overcome this.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Written by OzGrid Business Applications
'www.ozgrid.com

Select Case Target.Address
Case "$A$1", "$A$5", "$A$20", "$B$10", "$C$1", "$D$11"
Me.Unprotect PassWord:="secret"
Target.Cells.Locked = True
Me.Protect PassWord:="secret"
Case Else
Me.Unprotect PassWord:="secret"
Target.Cells.Locked = False
End Select

End Sub

This code needs to be housed within the Worsheet Module, to get there right click on the Sheet name tab and select "View Code" then paste in the code. Change the cell addresses and/or add more. Then push Alt+Q to return to Excel.
This particular code will apply sheet protection ONLY when: "$A$1", "$A$5", "$A$20", "$B$10", "$C$1", "$D$11"
are selected.

If your cells you want protected are in one or more contiguous ranges let me know and I can modify it.

DaveOzGrid Business Applications

Another way you can do it is to use the Workbook_SheetChange event. Essentially when a cell (or range) is changed this event is fired. If you have some naming scheme for your ranges you can then decide that this particular range is protected and then you call the Undo method of the application object. You will need to have a flag indicating the state of the action you are taking, because calling the Undo will also cause the Workbook_SheetChange event to occur. Then you can get into an endless loop.

You will need to decide if you want to take action at a worksheet level or the workbook level.
I chose the workbook level, so that I could consolidate my code. I call a single procedure for all changes.

I also created a series of named ranges with a prefix that identifies them as "protected" ranges. If the target range, that is the range that is being changed, is in the protected named range then I undo the change. You'll need to figure out a naming scheme to use, especially if you have real complicated workbooks. For my named ranges, I use something like P_Rates_Marg to indicate that it is a Protected range, for the Rates information, on the Margin worksheet.
Be aware that the Workbook_SheetCalculate event will come into play as well. It actually fires before the Workbook_SheetChange event and can bite you. I highly recommend using John Green's Excel VBA 2000 Programmer's Reference (Wrox Press).

This is a useful solution for me, because if I want to make a range protected, I can just make a new named range with the proper prefix and I'm done. If I want to remove it, then all I have to do is remove the named range from the names collection and then I'm done. If I want to create the range dynamically, I can do that as well. If the user inserts a row or column then the named range can respond accordingly.

I did use the protection feature of the worksheets, but only to prevent my users from inserting columns or rows. I did not want them to change the structure of my worksheet, so I protected the worksheets, after unlocking all the cells on the worksheet. They can change their values as much as they would like (so long as it is not a protected named range, or a calculated named range (numeric values only)).

Thanks dave

That works perfect. however is there a way of having the ability of protecting cells
without going into the code. for exmple being able to choose cells than press a button

Thank you
Regards
Rayn

Rayn, yes you could do this, but you would be back in the same boat as before in that when you apply Protection you are Protecting the Sheet not just cells. This is because a Cell is an Object of the Worksheet.

You could assing this procedure to a button (Use button from the Forms toolbar).

Sub ProtectMe()

ActiveSheet.Unprotect _
Password:="secret"

ActiveCell.Locked = True

ActiveSheet.Protect _
Password:="secret", DrawingObjects:=True, _
Contents:=True, Scenarios:=True

End Sub

As you can see there are three levels of protection available, adjust these to suit. There is a fourth level available to VBA, but this wont concern you unless you are using VBA to make changes. Besides it has too many loop holes.

I also have some methods of preventing accidental overtyping in cells on my Website under "Handy Hints" that my suit your needs.

Dave
OzGrid Business Applications