How to make a range reaonly or locked or non editable???

[sankartalam]

Hi all,
Could any one help me !!!
I want to make a range of cells to be locked or non editable or read only. and I dnt want to lock whole sheet and I dnt want to make whole sheet as protected. Only a particular range I want to make it as readonly.
Could anyone let me know how to start coding???

[dalea]

sankartalam,

As you are aware, you've put some pretty serious restrictions on the methodology you want since most protection schemes are only active if the worksheet, at least, is protected.

I've devised a workaround utilizing the data validation feature of Excel. Please examine the attached workbook.

The procedure is that you would already have entered into the cells that you want to make "read only" whatever the data you want in them.

Go to an unused cell on your worksheet. In the attachement I've used cell A2. Select "Data Validation" from the drop-down menu under "Data" on the Standard toolbar. On the "Setting" tab select "Custom" on the drop down menu under "Allow:" Enter any text string - doesn't make any difference what it is - make it short. Don't enter a number. Select the "Input Message" tab and then the "Error Alert" tab and populate them something like I have with whatever message you want to display to the person trying to change the cell.

Close the "Data Validation" dialogue box. Now copy the cell where you just entered the data validation into. Select the cells that you want to make "Read Only".

Select "Edit" then "Paste Special" then click on the "Validation" check box then click on "OK"

Those cells will now display the "Input Message" when they select a cell and the "Error Alert" message if they try to change any thing.

At this point go back to the first blank cell you used to set up the validation and remove the validation from that cell. Then delete that cell.

Like most security procedures in Excel they can be overcome by a knowledgeable, motivated user. In this case all you they would have to do is select a cell and remove the data validation. To make this a little more difficult, you can remove "Validation" as a selection on the "Data" menu.

To do this, just right click any where in the tollbar area and then select "Customize" from the drop down menu. Select "Data" from the "Standard" toolbar. Just drag the "Validation" from the toolbar down to the "Customize" dialogue box and drop it there. This way they would have to put "Validation" back on the toolbar before they could even see that it was data validation that was prohibiting them from making changes. Of course you would have to do this yourself to make changes. What makes this work is that "Data Validation" only works on entries made after you set up the validation criterea. A cell can have data in it that is not allowed by "Data Validation" if it is there before "Data Validation" is established.

[mdmackillop]

[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("H1:H10")) Is Nothing Then
Target.Offset(, 1).Select
MsgBox "you may not enter that cell"
End If
End Sub
[/VBA]