macro code to a shared file

[newbie_010108]

hi,

How can i make this macro code run in a shared file and col M and N are
protected cell.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("K:L")) Is Nothing Then
If Target.Count = 1 Then
If Target.Offset(0, 2) = "" Then Target.Offset(0, 2) = Now
If Target.Column = 12 Then
If Target.Offset(0, 1) = "" Then
Target.Offset(0, 2) = ""
Else
Target.Offset(0, 3) = Now - Target.Offset(0, 1)
End If
End If
End If
End If
Application.EnableEvents = True
End Sub

 

[Per Jessen]

Hi

You have to unprotect the sheet before you can change it:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="JustMe" 'Change to your own password
If Not Application.Intersect(Target, Range("K:L")) Is Nothing Then
If Target.Offset(0, 2) = "" Then Target.Offset(0, 2) = Now
If Target.Column = 12 Then
If Target.Offset(0, 1) = "" Then
Target.Offset(0, 2) = ""
Else
Target.Offset(0, 3) = Now - Target.Offset(0, 1)
End If
End If
End If
ActiveSheet.Protect Password:="JustMe"
Application.EnableEvents = True
End Sub

Hopes this helps.
....
Per

 

[newbie_010108]

Hi Jessen,

the cols remain unprotected.

Hi

You have to unprotect the sheet before you can change it:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="JustMe" 'Change to your own password
If Not Application.Intersect(Target, Range("K:L")) Is Nothing Then
If Target.Offset(0, 2) = "" Then Target.Offset(0, 2) = Now
If Target.Column = 12 Then
If Target.Offset(0, 1) = "" Then
Target.Offset(0, 2) = ""
Else
Target.Offset(0, 3) = Now - Target.Offset(0, 1)
End If
End If
End If
ActiveSheet.Protect Password:="JustMe"
Application.EnableEvents = True
End Sub

Hopes this helps.
....
Per