Gusung
02-17-2017, 12:46 AM
Hi
I would like to run two different macros on the same sheet. How do I do so?
It is not a shared workbook but it will be edited by different individuals thus the need for the password and audit trail.
1) Cells in column E are locked after the first edit. Column A-C tracks the date, time and username of the corresponding column E.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range, B As Range, Inte As Range, r As Range
Set A = Range("E:E")
Set Inte = Intersect(A, Target)
If Inte Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each r In Inte
If r.Value > 0 Then
ActiveSheet.Unprotect Password:="123"
r.Offset(0, -4).Value = Date
r.Offset(0, -4).NumberFormat = "dd-mm-yyyy"
r.Offset(0, -3).Value = Time
r.Offset(0, -3).NumberFormat = "hh:mm:ss AM/PM"
r.Offset(0, -2).Value = Application.UserName
Target.Locked = True
ActiveSheet.Protect Password:="123"
Else
End If
Next r
Application.EnableEvents = True
End Sub
2) I want to lock columns D, F and G after the first edit but need not state the date, time or person who did the edits. I don't know how to set the range and incorporate the code into the above.
ActiveSheet.Unprotect Password:="123"
Target.Locked = True
ActiveSheet.Protect Password:="123"
Thanks
I would like to run two different macros on the same sheet. How do I do so?
It is not a shared workbook but it will be edited by different individuals thus the need for the password and audit trail.
1) Cells in column E are locked after the first edit. Column A-C tracks the date, time and username of the corresponding column E.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range, B As Range, Inte As Range, r As Range
Set A = Range("E:E")
Set Inte = Intersect(A, Target)
If Inte Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each r In Inte
If r.Value > 0 Then
ActiveSheet.Unprotect Password:="123"
r.Offset(0, -4).Value = Date
r.Offset(0, -4).NumberFormat = "dd-mm-yyyy"
r.Offset(0, -3).Value = Time
r.Offset(0, -3).NumberFormat = "hh:mm:ss AM/PM"
r.Offset(0, -2).Value = Application.UserName
Target.Locked = True
ActiveSheet.Protect Password:="123"
Else
End If
Next r
Application.EnableEvents = True
End Sub
2) I want to lock columns D, F and G after the first edit but need not state the date, time or person who did the edits. I don't know how to set the range and incorporate the code into the above.
ActiveSheet.Unprotect Password:="123"
Target.Locked = True
ActiveSheet.Protect Password:="123"
Thanks