Macro will not work in protected Excel 2003 Worksheet

[LiveUser]

Columns D, I , and J are locked (Although my Macro only has to do with B, I,
and J). When I protect the sheet so that only unlocked cells can be selected
and edited my macro will not work.

What do I need to do? This form will be going out to other employees and I
don't want it to be difficult for them and I am protecting it from them for a
reason, obviously.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect([B:B], Target) Is Nothing Then
Application.EnableEvents = False
Cells(Target.Row, 9) = Date
If Range("J" & Target.Row).Value = "" Then
Cells(Target.Row, 10) = Now
End If
Application.EnableEvents = True
End If
End Sub

 

[Chip Pearson]

In situations like that, I use the UserInterfaceOnly parameter on the
Protect statement. E.g.,

Worksheets(1).Protect password:="abc",UserInterfaceOnly:=True

With UserInterfaceOnly set to True, VBA code can do anything, regardless of
protection setting. The protection applies only to actions taken by the user
(e.g., by the keyboard or mouse). The UserInterfaceOnly property isn't
persistent, so it needs to be set with code with the workbook opens.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)