Protect certain cells if sheet not protected

Jim:
I doubt it. But you can designate those particular cells to be protected, and the rest of the cells to be unprotected (i.e. unlocked), so that when you protect the sheet only the unlocked cells can be changed.

You could try the worksheets events eg

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NoChgRg As Range
Dim chg As Range

Set NoChgRg = [C5:E13]
Set chg = Application.Intersect(Target, NoChgRg)
If Not chg Is Nothing Then
Application.EnableEvents = False
MsgBox "Can't do!"
Application.Undo
Application.EnableEvents = True
End If
Set chg = Nothing
End Sub

Just change the range to protect

Ivan

Isn't it nice when Ivans onboard

The "other Tom" has a question for Ivan

Hi Ivan,

I could not get the Worksheet_Change event code to protect against formatting changes or spell check changes. Have you found any way around that challenge using the Change event?

PS, I'm looking at February for my web site to be up, how's yours?

Tom Urtis

Re: The "other Tom" has a question for Ivan

Hi Tom
Thats a GOOD question!
You are quite correct....although the event IS
triggered via the replace it is NOT via format.
The only way around it I can think of @ this time
is to disable the ALL find&replace + format.

Let me know when your site is up.....mine will not
be up for a while....trying to find extra time.

Have a happy Cristmas

Ivan

: You could try the worksheets events eg : Private Sub Worksheet_Change(ByVal Target As Range) : Dim NoChgRg As Range : Dim chg As Range : Set NoChgRg = [C5:E13] : Set chg = Application.Intersect(Target, NoChgRg) : If Not chg Is Nothing Then : Application.EnableEvents = False : MsgBox "Can't do!" : Application.Undo : Application.EnableEvents = True : End If : Set chg = Nothing : End Sub : : Just change the range to protect : Ivan