VBA to lock row based on column U value

[JohnLute]

Good day! I recently posted this and worked out some possible solutions however I'm still stuck so I'm posting it again.

I use column U as a "helper" in a table. The cells in this column have a formula that checks its row for errors. If it finds errors, it displays "OPEN"; if it finds no errors, it displays "LOCK".

On change event, I need to lock rows that display "LOCK" in the U helper column. Simply put: If target U = "LOCK" then lock entire row else do nothing.

Does anyone have anything that might help me with this?

 

[NoSparks]

Not to worry JohnLute, if you're happy I'm happy,

One, if not both of the errors is caused by HelpArr not being populated
If the code was installed as indicated, the workbook saved, closed and re-openned,
then HelpArr has to be populated via Workbook_Open so don't know what would have happened there.
Guess it doesn't matter now.

The cells beyond the last column of the table (U) are already formatted as locked so I just need to lock columns A-N (columns O-U are all helper columns that are formatted as locked and are hidden).

If the locked state of those cells is important to you, check and see if it is being maintained.

 

[kvsrinivasamurthy]

No. Number of rows will not be a problem.

 

[JohnLute]

Not to worry JohnLute, if you're happy I'm happy,

One, if not both of the errors is caused by HelpArr not being populated
If the code was installed as indicated, the workbook saved, closed and re-openned,
then HelpArr has to be populated via Workbook_Open so don't know what would have happened there.
Guess it doesn't matter now.


If the locked state of those cells is important to you, check and see if it is being maintained.

To confirm - it was installed as indicated and the file was closed and re-opened. I closed and re-opened a couple of times to validate but kept getting the errors when trying to execute.

I did check the lock state and it is being maintained. Thanks again for the insight and support! I've been tinkering with this on/off for a few weeks and it's great to see other solutions and finally have it resolved!

 

[JohnLute]

No. Number of rows will not be a problem.

Thanks. I noticed a slight delay but it was likely my machine. After working in VBA for any length of time, my machine tends to get drowsy. I frequently have to close all open Excel files and re-load.

Anyway, here is the complete solution. Thanks again for your support! I don't know that I would've arrived at the same solution. I had a real mess going on for quite awhile!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Dim adr$, S$, T&
Dim M

If Cells(ActiveCell.Row, "U").Value = "LOCK" Then
    Msg = "This audit is complete!" & vbCrLf & _
    "Would you like to add a new audit?"    ' Define buttons.
        Style = vbYesNo Or vbInformation Or vbDefaultButton1    ' Define buttons.
        Title = "Add a new audit?"    ' Define title.
        Help = "DEMO.HLP"    ' Define Help file.
        Ctxt = 1000    ' Define topic context.
            ' Display message.
        Response = MsgBox(Msg, Style, Title, Help, Ctxt)
            If Response = vbYes Then    ' User chose Yes.
                ActiveSheet.Unprotect Password:="xyz"
                MyString = "Yes"    ' Perform some action.
                    ' Lock row.
                    Cells.Locked = False
                    adr = ActiveSheet.ListObjects("tblOBLA").ListColumns("AUDHLPR").DataBodyRange.Address
                    M = Filter(Evaluate("transpose(If(" & adr & "=""LOCK"",""A""&Row(" & adr & "),false))"), False, False)
                    For T = 0 To UBound(M)
                    S = S & "," & M(T)
                    If Len(S) > 240 Or T = UBound(M) Then
                        Range(Mid(S, 2)).EntireRow.Locked = True
                        S = ""
                    End If
                    Next T
                    ' Procedure to add row to table
                    InsertNewAuditTBL
            Else    ' User chose No.
                ActiveSheet.Unprotect Password:="xyz"
                MyString = "No"    ' Perform some action.
                    ' Lock row.
                    Cells.Locked = False
                    adr = ActiveSheet.ListObjects("tblOBLA").ListColumns("AUDHLPR").DataBodyRange.Address
                    M = Filter(Evaluate("transpose(If(" & adr & "=""LOCK"",""A""&Row(" & adr & "),false))"), False, False)
                    For T = 0 To UBound(M)
                    S = S & "," & M(T)
                    If Len(S) > 240 Or T = UBound(M) Then
                        Range(Mid(S, 2)).EntireRow.Locked = True
                        S = ""
                    End If
                    Next T
                    ActiveSheet.Protect Password:="xyz", DrawingObjects:=True, Contents:=True, Scenarios:=True _
                    , AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
            End If
End If

End Sub