2 macros in one sheet targeting same cells

Question

This is probably a really 'newbie' question but in all honesty, I'm new to macros and need some assistance.

I have a macro written to automatically add a date/time stamp to cell AB10 if cell AA10 shows "Approved". This macro also automatically deletes the contents of AB10 & AC10 if AA10 is blank.

I now need to also make the entire row lock once cell AA10 shows Approved and has auto populated the date/time in AB10 and once there is a value (from a predetermined drop down list) in cell AC10.

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 someone show me how to add the necessary code to lock the row as required above? Everything I've tried to add just disables the macro above.

Sincere thanks for any help that you can provide!

[Interesting Read](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) Also to lock a row, you can use `Target.EntireRow.Locked = True` and then protect the sheet. — Siddharth Rout, Nov 27 '14 at 17:03
@chester4207 it seems you dont need 2 macro after all. Have you tried suggestions in other comments? — ZAT, Dec 01 '14 at 17:09
@Siddharth's suggestion is accurate, however, you will not notice the affect of locked cells if you do not protect the sheet. My recommendation would be to manually unlock all cells in the sheet, then protect the sheet and run your code. This will make it so your other rows will still be editable. — mongoose36, Dec 02 '14 at 14:18
To have a protected sheet in which you wish to dynamically alter which cells are locked, you would first need to protect the sheet using the argument `UserInterfaceOnly:=True` (e.g. `Me.Protect Password:="mypass", UserInterfaceOnly:=True`). This would need to be done as part of the `Worksheet_Activate` or `Workbook_Open` events, as the `UserInterfaceOnly` setting is not saved with the workbook. The only other option would be to unprotect and reprotect the sheet each time you wished to add more locked cells. — blackworx, Jan 08 '15 at 16:54

blackworx · Answer 1 · 2015-01-08T17:19:39.647

Only required minor modifications to your code. Paste the following into the worksheet's 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 all cells which are required to be editable start in an unlocked state (NB: Excel's default is locked). As per your initial code, the timestamp will be entered and the row will be locked regardless of what is typed in column AA. If you want the user to be able to subsequently clear the contents of column AA in order to remove the timestamp and unlock the row for editing, then restore the two remmed out lines in the code.

Hope this helps.

2 macros in one sheet targeting same cells

1 Answers1