Spell Checking Protected Worksheets

The Problem

When a worksheet is protected (locked) the Spellcheck feature is unavailable, even for unlocked cells.  When the purpose of locking is to protect all data this makes sense.  However, when the purpose is to have the user add information to a form, where locking of labels and their locations is important, the spellchecker can be a useful feature for the users.

Option 1 - User Run Macro

This is all over the internet, including Microsoft's support site.

This solution adds a VBA procedure to unlock the worksheet, run spell check, then re-lock it.  All versions look something like this.

Sub SpellCheckSheet()

   ActiveSheet.Unprotect "password"

   ActiveSheet.CheckSpelling

   ActiveSheet.Protect "password"

End Sub

This requires the user to open the macros, select the macro from the list, and running it.

If this is the only Public procedure, then it's relatively simple.  However, with each additional Public procedure it becomes more onerous for the user.  Imagine the frustration if this were added as a worksheet procedure because there were different protections for different sheets, or different passwords because there were different owners.

Option 2 - Worksheet Event Lock & Unlock

Thinking about the issue a little more, it came to me we we only need to spell check the Unlocked cells.  By default, all cells of a worksheet are locked.  When we want people to add information while the worksheet is protected, we change those to unlocked.

Right?

Since there is a Worksheet Event that triggers when the selected cell(s) changes, we can use that to automatically Protect/Unprotect the Worksheet.

• 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

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    With Target

        If Not .Locked Then

            .Parent.Unprotect "password"

        Else

            .Parent.Protect "password"

        End If

    End With

End Sub

This can be added to any worksheet that needs protection and spellchecking, leaving the others alone.  For example, if there are worksheets available for managerial reporting that roll up to one for the director the Worksheet module can be included in all of the managers' worksheets.

Two notes 

The Worksheet is Protected whenever any cell in a selected range (one or more cells) is Locked.  If the cells in the selected range are...

• 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.

Selecting Objects on the worksheet does not trigger the Worksheet's SelectionChange Event.  This event triggers only when a cell, or range of cells, is selected.

Option 3 - Workbook Event to Lock & Unlock Worksheets

With minor modification the same VBA code can be attached to the Workbook's SheetSelectionChange Event.  

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    If Not Target.Locked Then

        Sh.Unprotect "password"

    Else

        Sh.Protect "password"

    End If

End Sub

The issue with this is it locks and unlocks all worksheets.  So, if a new worksheet is added it will need to be Unprotected, and at least one cell Unlocked before anything can be added to it.

Option 3.1 - Control with Worksheet Naming Convention

To get around the limitation of worksheets being locked when they shouldn't, a naming convention for worksheets can be used.  For example, using the idea that departmental managers are providing inputs, each of those worksheets could have "Dept" in their names, e.g. "Acctg Dept", "HR Dept", or "Dept of Hip".

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    If Instr(1,sh.Name,"Dept") > 0 then

        If Not Target.Locked Then

            Sh.Unprotect "password"

        Else

            Sh.Protect "password"

        End If

    End If

End Sub

Option 3.2 - Control with a Worksheet-Scoped Name

...in development