Detect any (entire) row selection in entire sheet and unprotect?

Question

Looking for the following code:

sheet is currently locked (enabled for locked cell selection).

VBA detects if any whole row(s) say 21, 22 are selected and automatically unprotects sheet.

THEN:

if these exact rows are deleted.. sheet automatically protects again.

If the user deselects these rows.. sheet protects again.

(this is design to perform specific row deletion)

very crudely:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    IF Rows("1:1").Select AND/OR Rows("2:2").Select AND/OR Rows("3:3").Select then
        ActiveSheet.Unprotect
    End If

    ActiveCell.Row.Delete
    ActiveSheet.Protect

End Sub

Something like this?? Sub team() If Selection.Address(0, 0) = "1:1" Then ActiveSheet.Unprotect End Sub — user1717622

Larry Larry · Accepted Answer · 2013-01-31T09:21:59

Remember to set Application.enableEvents = True first

EDIT Changed Code as OP's new spec in discussion

Restriction: The ENTIRE row ( every cell must be unlocked in order to be able to select the whole row)

' remember the event's name is `Worksheet_SelectionChange`
' NOT Worksheet1_SelectionChange
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ActiveSheet.Unprotect
    ' the rows to be selected
    Dim row1 As Range
    Dim row2 As Range
    Dim row3 As Range
    Dim mergedRange As Range
    Set row1 = Me.Rows("1:1")
    Set row2 = Me.Rows("3:3")
    Set row3 = Me.Rows("5:5")
    Dim found As Boolean
    Dim Match As Boolean
    Set mergedRange = Application.Union(row1, row2)
    Set mergedRange = Application.Union(mergedRange, row3)
    Match = False


    ' check if it selects only 1 row
    If Target.Areas.Count <> 1 Then
        ActiveSheet.Protect
        Exit Sub
    End If


    ' check if it's select the first 500 rows
    If Target.Areas.Item(1).Row > 0 And Target.Areas.Item(1).Row <= 500 Then
        'check if it's selecting the WHOLE row
        If Me.Rows(Target.Areas.Item(1).Row & ":" & Target.Areas.Item(1).Row).Areas.Item(1).Count = Target.Areas.Item(1).Count Then
            ' check if the "B" Column of this row's backgound color is blue
            If Me.Cells(Target.Areas.Item(1).Row, 2).Interior.Color = RGB(197, 217, 241) Then
                Match = True
            End If
        End If
    End If


    If Match Then

        'MsgBox "ActiveSheet.Unprotect"
        ActiveSheet.Unprotect
    Else
        Debug.Print "notMatch"
        'ActiveCell.Row.Delete
       ActiveSheet.Protect
    End If


End Sub

Detect any (entire) row selection in entire sheet and unprotect?

1 Answers