|
macro code to a shared file
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 |
macro code to a shared file
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 On 10 Aug., 02:59, newbie_010108 wrote: 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 |
macro code to a shared file
Hi Jessen,
the cols remain unprotected. "Per Jessen" wrote: 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 On 10 Aug., 02:59, newbie_010108 wrote: 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 |
| All times are GMT +1. The time now is 01:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com