excel worksheet protection questions

[jpmckitrick]

I have a couple of worksheets that have columns with formulas and columns that are manual input data. I want to protect the formulas and allow users to input data in the other cells. I also need them to be able to select the formula cells, plus filter and sort the data. I thought I had it all figured out then found another issue. I first unlocked the entire sheet. Then I used Find & Select to select all the cells with formulas. I set all those to locked. I then read on a forum about using data validation to set it where the locked formula cells could be selected but not changed. I thought that had worked. If anyone tries to type in the cell, an error message pops up that says the cell contains a formula and cannot be edited. BUT, I think discovered I could copy and paste data into that cell. Any idea or suggestions on what I can do differently?

 

[Logit]

How to prevent copy/paste in excel only for some columns or cells

 

[jpmckitrick]

How to prevent copy/paste in excel only for some columns or cells

Thanks but I think that solution makes the cells un-selectable. I need the user to be able to select the cell but not edit it. I thought the data validation solution fixed that, but it appears not to stop copy/paste.

 

[Logit]

The following will allow Column A to be selected but not changed :

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Worksheets("Sheet1").Cells.Locked = False
    Worksheets("Sheet1").Range("A1:A20").Locked = True
    Worksheets("Sheet1").Protect Password:="pass", UserInterfaceOnly:=True
End Sub