PDA

View Full Version : Prevent Modification of a Range via VBA Instead of Sheet Protection



seanduffy
03-21-2010, 08:11 PM
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!

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

lucas
03-21-2010, 08:32 PM
I have to ask, why?

seanduffy
03-21-2010, 08:46 PM
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
03-21-2010, 10:21 PM
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
03-23-2010, 07:49 AM
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