MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Protect certain cells if sheet not protected


Posted by Jim on December 15, 2001 10:22 AM

Hi ,
Is it possible in vba to write a script of code that
will protect certain cells while the sheet is not
protected.
Thanks, Jim


Posted by Tom on December 15, 2001 11:20 AM

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.

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.Undo
Application.EnableEvents = True
End If
Set chg = Nothing
End Sub


Just change the range to protect

Ivan

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

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

Posted by Ivan F Moala on December 15, 2001 9:19 PM

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