2 macros in one sheet, targeting the same cells
This is probably really a "beginner", but to be honest, I am new to macros and need some help.
I have a macro that automatically adds a date / time stamp to a cell AB10
if the cell AA10
shows " Approved ". This macro also automatically removes content AB10 & AC10
if AA10
empty .
Now I need to also do all row locking as soon as the cell AA10
displays Approved and auto-populates the date / time in AB10
and after in the cell.
This is my existing macro:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("AA10:AA10000"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
.Offset(0, 2).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub
Can anyone show me how to add the necessary code to lock the row as above? Everything I tried to add just disables the macro above.
Sincere thanks for any help you can provide!
source to share
Only minor modification to your code is required. Paste the following into the worksheet code module:
Option Explicit
Private Const strPassword As String = "password"
Private Sub Worksheet_Activate()
Me.Protect Password:=strPassword, userinterfaceonly:=True
End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("AA10:AA10000"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
.Offset(0, 2).ClearContents
'.EntireRow.Locked = False
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm"
.Value = Now
End With
.EntireRow.Locked = True
'.Locked = False
End If
Application.EnableEvents = True
End If
End With
End Sub
The above assumes that all cells that should be editable start in an unlocked state (NB: Excel is locked by default). As per your original code, a timestamp will be entered and the row will be locked no matter what is printed in column AA. If you want the user to be able to subsequently clear the contents of the AA column to remove the timestamp and unlock the row for editing, then restore the two recovered rows in the code.
Hope it helps.
source to share