| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| if the file is shared, do not execute macro | Excel Programming | |||
| VB code stalls when file is shared | Excel Programming | |||
| VB Code Works Perfect...but Not when the File is Shared | Excel Programming | |||
| Macro error when file is shared | Excel Discussion (Misc queries) | |||
| Shared file - macro error | Excel Programming | |||
Linear Mode
