Spell Check a protected sheet?

[weefisheads]

This should be a very easy question -

Is it possible to protect a sheet and still have the option to Spell check unlocked cells?

I've tried a number of options, but it seems that anytime I protect a sheet, the F7 Spelling options gets greyed out.

I don't want folks to spell check LOCKED cells, but it would be nice to spell check text in unlocked cells.

Possible?

dB

 

[mvptomlinson]

Hi dB,

Your best bet is to use a macro. You can assign it to a button, or have the user open the Macros window to run it. (In 2007, if you know how, you can create a custom toolbar icon or add it to the QAT.)

Sub spellchk()
Sheets("Sheet1").Unprotect Password:="your_password"
Range("A1, A3, A5").CheckSpelling
Sheets("Sheet1").Protect Password:="your_password"
End Sub

Add the addresses of your unprotected cells to the Range entry list, change your sheet name, and the password of course.

 

[AusSteelMan]

dB,

I have used this code for highlighting the unlocked cells.

Sub SelectAllUnlockedCells()
    Dim WorkRange As Range
    Dim FoundCells As Range
    Dim Cell As Range
    Set WorkRange = ActiveSheet.UsedRange
    For Each Cell In WorkRange
        If Cell.Locked = False Then
            If FoundCells Is Nothing Then
                Set FoundCells = Cell
            Else
                Set FoundCells = Union(FoundCells, Cell)
            End If
        End If
    Next Cell
    If FoundCells Is Nothing Then
        MsgBox "All cells are locked."
    Else
        FoundCells.Select
    End If
End Sub

You could possibly use this if there is many unlocked cells that you would like to be available for spell check.

This sould be worked into mvptomlinson's code.

Darren

 

[AusSteelMan]

Maybe something like

Sub SpellCheckAllUnlockedCells()
 
ActiveSheet.Unprotect
      'Alternatively use something like
      'Sheets("Sheet1").Unprotect Password:="your_password"
      'if there is a password.  Also if not just the ActiveSheet
 
    Dim WorkRange As Range
    Dim FoundCells As Range
    Dim Cell As Range
    Set WorkRange = ActiveSheet.UsedRange
    For Each Cell In WorkRange
        If Cell.Locked = False Then
            If FoundCells Is Nothing Then
                Set FoundCells = Cell
            Else
                Set FoundCells = Union(FoundCells, Cell)
            End If
        End If
    Next Cell
    If FoundCells Is Nothing Then
        MsgBox "All cells are locked."
    Else
        FoundCells.Select
    End If
Selection.CheckSpelling
ActiveSheet.Protect
     'Alternatively use something like
     'Sheets("Sheet1").Protect Password:="your_password"
     'if there is a password.  Also if not just the ActiveSheet
 
End Sub

Or better

Sub SpellCheckAllUnlockedCells()
 
ActiveSheet.Unprotect
     'Alternatively use something like
     'Sheets("Sheet1").Unprotect Password:="your_password"
     'if there is a password.  Also if not just the ActiveSheet
 
Call SelectUnlockedCells
Selection.CheckSpelling
ActiveSheet.Protect
     'Alternatively use something like
     'Sheets("Sheet1").Protect Password:="your_password"
     'if there is a password.  Also if not just the ActiveSheet
 
End Sub
Sub SelectUnlockedCells()
    Dim WorkRange As Range
    Dim FoundCells As Range
    Dim Cell As Range
    Set WorkRange = ActiveSheet.UsedRange
    For Each Cell In WorkRange
        If Cell.Locked = False Then
            If FoundCells Is Nothing Then
                Set FoundCells = Cell
            Else
                Set FoundCells = Union(FoundCells, Cell)
            End If
        End If
    Next Cell
    If FoundCells Is Nothing Then
        MsgBox "All cells are locked."
    Else
        FoundCells.Select
    End If
End Sub

if you think you may want to select all unlocked cells for some other task.

Darren

 

[weefisheads]

Thanks all for the reply - both suggestions work like a charm.

AusSteelMan, I do have one question though. After running your code, all Non protected cells are still selected. Short of coming up with a specific cell to select, is there any way to UNSELECT all ranges that are selected? I saw somewhere that Set WorkRange = Nothing does something like this, but that doesn't seem to work for me.

Regardless, I really appreciate the code.

dB

 

[AusSteelMan]

Sorry, but I do not know of a way to simply deselect cells. I think that "something" needs to be the currently selected active cell.

My only suggestions would be to:
1. alter the code so that whatever was the selected cell before the code was executed was then reselected just before the end of the code (so basically the user would never anything happen except the spell check box popping up)
2. alter the code to select a cell to finish on each time.

Maybe someone else has an idea.

Regards,
Darren

 

[pstraton]

If you use UserInterfaceOnly protection, you can spell-check to your hearts content on a protected sheet. But be aware that, to use UserInterfaceOnly protection effectively, you must deal with the fact that you have to re-establish it every time the workbook is opened. For an excellent article on protection in general and UserInterfaceOnly protection in particular, see Chip Pearson's page: Worksheet And Workbook Protection