2 macros in one sheet targeting same cells [Solved/Closed]

Posts
2
Registration date
Wednesday November 26, 2014
Last seen
November 28, 2014
- - Latest reply: chester4207
Posts
2
Registration date
Wednesday November 26, 2014
Last seen
November 28, 2014
- Nov 28, 2014 at 05:56 PM
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 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!
See more 

2 replies

Best answer
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
914
1
Thank you
Hi
I have not tested it, but it should work

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

                'since at this point AB cell is already populated, we just want to see if AC column is not empty
                If (Not IsEmpty(.Offset(0, 2))) Then
                    'have some thing to so first unprotect sheet
                    ActiveSheet.Unprotect ("")

                     Rows(.Row).Locked = True
                    ActiveSheet.Protect ("")

                End If
            End If

            Application.EnableEvents = True
        End If
    End With
End Sub

Say "Thank you" 1

A few words of thanks would be greatly appreciated. Add comment

CCM 4525 users have said thank you to us this month

Posts
2
Registration date
Wednesday November 26, 2014
Last seen
November 28, 2014
0
Thank you
THANK YOU rizvisa1! I used your code, made a couple of tweaks and it worked like a charm. I sincerely appreciate the help!!!