Can I protect a cell without protecting a sheet?

[lucky12341]

I need to protect virtually an entire column of cells from being edited but I cannot protect the sheet because I need the auto filter option...is this possible?

 

[Damon Ostrander]

Hi lucky12341,

You can protect a cell without protecting the sheet using data validation. However, this is just a protection against inadvertent editing, but not against deliberate or malicious editing because the validation can be undone by the user.

If you want to validate a cell against editing simply validate to allow only text strings with fewer than 1 characters to be entered. You can also use validation to give the user a message when the user selects a cell within the validated range, like "this cell is locked against inadvertent editing".

Damon

 

[Joe Was]

Here are samples on how to do it by code. The easy way to to make it so the user can never select your Range of cells:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Place in the sheet module you want this control in, like: Sheet1.

'Make cell always closed for changes even if protected!
If Target.Address = "$A$2" Then
Target.Offset(1, 0).Select
End If

'Make cell always open for changes even if protected!
If Target.Address = "$B$2" Then
ActiveSheet.Unprotect Password:="admin"
End If

'Keep sheet protected!
If Target.Address <> "$B$2" Then
ActiveSheet.Protect Password:="admin"
Else
ActiveSheet.Unprotect Password:="admin"
End If
End Sub

In your case you may want:

'Make Column always closed for changes even if protected!
myCol = 2 'Column: 2 is Column "B" as numbered from left to right.

If Target.Column = myCol Then
Target.Offset(0, 1).Select
End If