PDA

View Full Version : Solved: Spell Check Unlocked Cells on Protected Sheet



CaptRon
07-01-2008, 11:32 AM
I have used procedures similar to the one I have posted here to perform spell checking on selected cells on a given worksheet. This works OK but often requires a separate procedure for different sheets as the location of unlocked cells varies from sheet to sheet.

Is there a way to create a single procedure that can be run on every worksheet by designating only unlocked cells? Would the sheet protection still have to be removed prior to running the spell checker?

Thanks, Ron

Sub SpellCkBack()
'Spell check routine for certain sections of the back page
On Error GoTo HandleErrors
Pwd = ThisWorkbook.Sheets("HQ-31 (22)").Range("A1").Value
Application.EnableEvents = False
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:=Pwd
Range("C2:AL8,AA11:AL17,Q36:AL42").CheckSpelling CustomDictionary:="CUSTOM.DIC", IgnoreUppercase:= _
False, AlwaysSuggest:=True, SpellLang:=1033
ActiveSheet.Protect Password:=Pwd, DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("A1").Select
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
HandleErrors:
End Sub

CaptRon
07-01-2008, 09:35 PM
I fished around until I found a solution. :reading: :type

This seems to work. :clap2:

Sub SpellCkUnlockedCells()
Dim WorkRange As Range
Dim FoundCells As Range
Dim Cell As Range
Set WorkRange = ActiveSheet.UsedRange

ActiveSheet.Unprotect
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."
ActiveSheet.Protect
Exit Sub
Else
FoundCells.CheckSpelling CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, AlwaysSuggest:=True, SpellLang:=1033
MsgBox "Spell checking complete."
End If
ActiveSheet.Protect
End Sub

Perhaps someone else might find this useful.

Ron