Option 1 - User Run Macro
Option 2 - Worksheet Event Lock & Unlock
- User selects (clicks on, tabs, drags) another location on the worksheet.
- Selection Change Event triggers, picking up the selected range as "Target"
- Code checks the condition of Target's Locked property
- If Target is unlocked, i.e. Target.Locked is False, then the sheet protection is turned off
- If the Target is not unlocked (see note below) the then the sheet protection is turned on
- All Locked? Worksheet is protected because Target.Locked is True.
- All Unlocked? Worksheet is unprotected because Target.Locked is False.
- A mixture of Locked and Unlocked? Worksheet is protected because Target.Locked is Null.
Option 3 - Workbook Event to Lock & Unlock Worksheets
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)