Previous Topic Next Topic

2 macros in one sheet targeting same cells

Solved/Closed
chester4207 Posts 2 Registration date Wednesday November 26, 2014 Status Member Last seen November 28, 2014 - Nov 26, 2014 at 05:37 PM
chester4207 Posts 2 Registration date Wednesday November 26, 2014 Status Member 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!
Related:

2 responses

Answer 1 / 2
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Nov 28, 2014 at 11:09 AM
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
1
Answer 2 / 2
chester4207 Posts 2 Registration date Wednesday November 26, 2014 Status Member Last seen November 28, 2014
Nov 28, 2014 at 05:56 PM
THANK YOU rizvisa1! I used your code, made a couple of tweaks and it worked like a charm. I sincerely appreciate the help!!!
0