Prevent Modification of a Range via VBA Instead of Sheet Protection

[seanduffy]

I need to prevent the modification of four cells in my worksheet w/o using the built in Excel protection settings (the users of the document need full, unrestricted access to everything else in the worksheet). Ideally this would prevent modifications to even the font and color of the cells, but at a minimum, it'd need to prevent their deletion.

I've tried the code below but the problem with it is if that I select a range that includes the cells, I'm able to still delete them or clear their contents. I can also simply delete the whole row that the cells I want to "protect" are in.

Any ideas!? Thanks!

[VBA]Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$R$1" Or Target.Address = "$T$2" Or Target.Address = "$T$3" Or Target.Address = "$T$4" Then
Application.EnableEvents = False
MsgBox "This cell is protected, sorry!"
Application.Undo
Application.EnableEvents = True
End If
End Sub[/VBA]

[lucas]

I have to ask, why?

[seanduffy]

Haha. I'm putting together a training tutorial that needs to be "registered" to each individual user (to the best of my ability, even if there are ways that advanced users could skirt around it). Since part of the training tutorial involves the ability to delete and insert individual cells, using the built in worksheet protection wont work (it doesn't allow that for some reason).

[Jan Karel Pieterse]

My preference would be to protect the sheet and provide the needed functionality through code.

As to testing your range:

If Not Intersect(Target.Address, Range("R1,T2:T4")) Is Nothing Then

[Paul_Hossler]

Ditto - I'd protect the sheet

1. Unlock all cells on the WS
2. Lock the 4 that you want
3. When you protect the WS, there are a lot of options that you can allow on the Locked cells, or not allow

Seems like you would not even need an event handler

Since part of the training tutorial involves the ability to delete and insert individual cells, using the built in worksheet protection wont work

Do you have an example WB and where this fails?



Paul