Protect certain cells if sheet not protected
Posted by Jim on December 15, 2001 10:22 AM
Is it possible in vba to write a script of code that
will protect certain cells while the sheet is not
Posted by Tom on December 15, 2001 11:20 AM
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.
Posted by Ivan F Moala on December 15, 2001 12:55 PM
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.EnableEvents = True
Set chg = Nothing
Just change the range to protect
Posted by Jim on December 15, 2001 1:15 PM
Isn't it nice when Ivans onboard
Posted by Tom Urtis on December 15, 2001 1:39 PM
The "other Tom" has a question for 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?
Posted by Ivan F Moala on December 15, 2001 9:19 PM
Re: The "other Tom" has a question for Ivan
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
: 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