Combine two macros on the same sheet

[Gusung]

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

[p45cal]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range, Inte As Range, r As Range, DFG As Range, DFGRng As Range, cll As Range
Set A = Range("E:E")
Set DFG = Range("D:D,F:G")
Set DFGRng = Intersect(Target, DFG)
Set Inte = Intersect(A, Target)

ActiveSheet.Unprotect Password:="123"
Application.EnableEvents = False

If Not Inte Is Nothing Then
For Each r In Inte.Cells
If r.Value > 0 Then
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
r.Locked = True
End If
Next r
End If

If Not DFGRng Is Nothing Then
For Each cll In DFGRng.Cells
If Len(cll.Value) > 0 Then cll.Locked = True
Next cll
End If

ActiveSheet.Protect Password:="123"
Application.EnableEvents = True
End Sub

[SamT]

Scenarios:
1.) If a person opens the workbook, but does not make any changes in Column "E", You don't need to log it

2.) If a person opens the workbook, makes a change in Column "E", then closes the workbook, then opens the workbook again (and no other person has made any changes,) Then you only need to log the first time that person opened the book and made changes.

3.) If a person opens the Workbook and makes many changes in Column "E", you only need to log the date/time of the first change.



Please verify or deny all three scenarios.

[Gusung]

Thanks :)

[Gusung]

Scenarios:
1.) If a person opens the workbook, but does not make any changes in Column "E", You don't need to log it

2.) If a person opens the workbook, makes a change in Column "E", then closes the workbook, then opens the workbook again (and no other person has made any changes,) Then you only need to log the first time that person opened the book and made changes.

3.) If a person opens the Workbook and makes many changes in Column "E", you only need to log the date/time of the first change.



Please verify or deny all three scenarios.

1) No log required if no changes are made in column E.
2) Say person A types in empty cell E1 and closes the workbook. When he or any other people reopens the workbook, they are unable to edit cell E1 anymore.
3) Since each cell in Column E can only be edited once the date/time will only be logged for that single time.

Thanks.

[SamT]

Looks like p45cal has done it for you.

[Gusung]

VBA code works! Thanks p45cal!