Worksheet SelectionChange locking sheet

[drag1c]

Hi,

I am using code for sheet lock, but if selection goes (in this specific case) from A6 to A1 ( -> Range("A6:A1") <- ) it will keep unlocked file which is wrong.

If there is any kind of intersection with A6:AN35, it should not allow file to be unlocked. How to change this code into that?

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "A6:AN35"     '<== change to suit

    On Error GoTo ws_exit
    Application.EnableEvents = False

    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
        With Target
        
            Call UnlockMe
        End With
    Else
        With Target
        
            Call LockMe
        End With
    End If

ws_exit:
    Application.EnableEvents = True
End Sub



Private Sub UnlockMe()
Sheet2.Unprotect "123"
End Sub

Private Sub LockMe()
Sheet2.Protect "123"
End Sub

 

[Micron]

Switch these 2 lines
Call UnlockMe
Call LockMe
or if you don't want to lock the sheet when there is an intersect, use Exit Sub.

 

[drag1c]

Switch these 2 lines
Call UnlockMe
Call LockMe
or if you don't want to lock the sheet when there is an intersect, use Exit Sub.

It doesn't work properly. I've set Calls right but there should be an addition:
If selected range is in both ranges, it should lock cells.

If any cell in range A6:AN35 is selected or any range in A6:AN35 is selected -> Unlock
If any cell in range A6:AN35 is selected but range is extended to cells outside of A6:AN35 -> Lock
If any cell outside of range A6:AN36 is selected -> Lock
If any cell outside of range A6:AN36 is selected but range is extended to cells inside A6:AN35 -> Lock

Example of lock:

Example of unlock:

Do you have an idea how to do it?

 

[drag1c]

I've wrote something like this, and it seems works:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const LandscapeRange As String = "A6:AN35"

    On Error GoTo ws_exit
    Application.EnableEvents = False

    If Not Intersect(Target, Me.Range(LandscapeRange)) Is Nothing Then
         With Target
            Call UnlockMe
        End With
    Else
        With Target
            Call LockMe
        End With
    End If

    If Not Intersect(Target, Me.Range("A1:AN5")) Is Nothing Then
        With Target
            Call LockMe
        End With
    End If

    If Not Intersect(Target, Me.Range("A36:AN45")) Is Nothing Then
        With Target
            Call LockMe
        End With
    End If


ws_exit:
    Application.EnableEvents = True
End Sub