Sheet protection

[Rev.]

Several days ago I did a newsgroup query and got an excellent response
to my coding problem that really works well. Now I want to protect the
worksheet and when I do I get a run time error at line 9. Is there a
line of code that I can insert to allow me to do this?

Thank You
Carl

Private Sub Worksheet_Change(ByVal _
Target As Excel.Range)
Dim rng as Range
If Target.Count > 1 Then Exit Sub
If Target.row => 2 and Target.Row <=10 then
If Target.column = 5 Then
If Not IsEmpty(Target) Then
If IsNumeric(Target) Then
Application.EnableEvents = False
set rng = Target.Offset(0,4)
rng.Value = rng.value + Target.Value
Target.ClearContents
Application.EnableEvents = True
End If
End If
End If
End If
End Sub

 

[Kris]

Hello Rev,

You can unprotect the worksheet in VBA before you make
modifications to the worksheet, then reprotect it after
you are done with your modifications. Like the following:

Worksheets("Sheet1").Unprotect
'Code to change the worksheet
Worksheets("Sheet1").Protect

You could do this around all of your code if you want, if
you make several modifications in several places, or you
could just put this around the code that actually
modifies the worksheet. Look up the .protect
and .unprotect methods in the VBA help for additional
information.

Hope this helps,

Kris