VBA to Lock Entire Row based on Helper Column Value

[JohnLute]

Good day! I've been scouring for support on this and I'm having trouble finding examples that align with my situation. And unfortunately, I'm failing to arrive at a solution myself! I'm hoping someone here has an example that aligns and will be willing to share.

I use a "helper" column "S" in a table. The column has a formula that checks its row for errors. If it finds no errors it displays "LOCK" and if it finds errors it displays "OPEN".

On change events, I need to lock rows that display "LOCK" in the helper column.

Does anyone have anything that might help me with this?

 

[Anthony47]

This will prevent selection of the row which column S shows "LOCK", and move the selection 1 row below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
    If Target.Column <> 19 Then
        If Not Application.Intersect(Target, Range("Table1")) Is Nothing Then
            If UCase(Cells(Target.Row, "S")) = "LOCK" Then
                Target.Offset(1, 0).Select
                'more?
            End If
        End If
    End If
End If
End Sub

The macro should be inserted into the vba module that belongs to the specific worksheet: risght click on the tab with the name of the sheet, select View code and the approriate vba module will open
I assumed the name of the tabble is Table1; modify Range("Table1") above if you use a different name

 

[JohnLute]

This will prevent selection of the row which column S shows "LOCK", and move the selection 1 row below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
    If Target.Column <> 19 Then
        If Not Application.Intersect(Target, Range("Table1")) Is Nothing Then
            If UCase(Cells(Target.Row, "S")) = "LOCK" Then
                Target.Offset(1, 0).Select
                'more?
            End If
        End If
    End If
End If
End Sub

The macro should be inserted into the vba module that belongs to the specific worksheet: risght click on the tab with the name of the sheet, select View code and the approriate vba module will open
I assumed the name of the tabble is Table1; modify Range("Table1") above if you use a different name

Thanks, Anthony! This works really well and offers a solution I hadn't considered! Unfortunately, there may be a need to select a row with a "LOCK" condition and therefore, this VBA doesn't allow this. Also, the table will eventually have thousands of rows and therefore, a user may accidentally click a "LOCKED" row and need to wait while the VBA fires down through thousands of rows until it reaches the last row. I see a solution to this by simply selecting D1 (below) however this still doesn't allow selection of the row. I regret not specifying this in my original post.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
    If Target.Column <> 19 Then
        If Not Application.Intersect(Target, Range("Table1")) Is Nothing Then
            If UCase(Cells(Target.Row, "S")) = "LOCK" Then
                Range("D1").Select
                'more?
            End If
        End If
    End If
End If
End Sub

 

[Anthony47]

The macro lets you select column S, and change its contents; but now I realize column S contains a formula so it's not so easy to use this feature (you should delete the formula, select and modify the row, restore the formula). Also, I see the problem with a long table with most rows "locked"

This new version has the following approach:
-a free cells is used to flag the "allow selection for locked cells"; I used Z1
-if col S says Lock and Z1 is empty, then selection is moved to column S
-if col S says Lock and Z1 is not empty then a selection on the row is allowed and Z1 is cleared (thus Z1 allows only 1 selection; if you prefer you may remove the line marked ?? and you will be allowed to select as many cells of a Locked row until you manually clear Z1

The code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Allow As String
'
Allow = "Z1"                                    'A free cell
If Target.Count = 1 Then
    If Target.Column <> 19 Then
        If Not Application.Intersect(Target, Range("Table1")) Is Nothing Then
            If UCase(Cells(Target.Row, "S")) = "LOCK" And Range(Allow) = "" Then
                Cells(Target.Row, "S").Select
'                Target.Offset(1, 0).Select
                'more?
            Else
                Range(Allow).ClearContents      '?? Clear the "Allow" cell?
            End If
        End If
    End If
End If
End Sub

 

[JohnLute]

The macro lets you select column S, and change its contents; but now I realize column S contains a formula so it's not so easy to use this feature (you should delete the formula, select and modify the row, restore the formula). Also, I see the problem with a long table with most rows "locked"

This new version has the following approach:
-a free cells is used to flag the "allow selection for locked cells"; I used Z1
-if col S says Lock and Z1 is empty, then selection is moved to column S
-if col S says Lock and Z1 is not empty then a selection on the row is allowed and Z1 is cleared (thus Z1 allows only 1 selection; if you prefer you may remove the line marked ?? and you will be allowed to select as many cells of a Locked row until you manually clear Z1

The code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Allow As String
'
Allow = "Z1"                                    'A free cell
If Target.Count = 1 Then
    If Target.Column <> 19 Then
        If Not Application.Intersect(Target, Range("Table1")) Is Nothing Then
            If UCase(Cells(Target.Row, "S")) = "LOCK" And Range(Allow) = "" Then
                Cells(Target.Row, "S").Select
'                Target.Offset(1, 0).Select
                'more?
            Else
                Range(Allow).ClearContents      '?? Clear the "Allow" cell?
            End If
        End If
    End If
End If
End Sub

Thanks, Anthony. I appreciate your engagement with this! This is another angle I hadn't thought about. I will mark this as the answer and fiddle with it until I get it working! Thanks!