Spell Check Unlocked Cells Only

[Dorci]

Excell 2003: I'm using the macro found in the posts to unprotect and spell
check a worksheet. However, it checks the entire sheet, when I only want it
to check the unlocked cells.

Thanks in advance. ~Dorci

 

[Dorci]

Sorry, I was rushed -- I misspelled Excel and didn't post an actual question.
Is there a way to specify "only unlocked cells" in the following spell check
code?

ActiveSheet.Unprotect

Cells.CheckSpelling _
CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, _
AlwaysSuggest:=True

ActiveSheet.Protect

 

[Jim Thomlinson]

Sub test()
Dim rngUnlocked As Range
Dim rng As Range

For Each rng In UsedRange
If rng.Locked = False Then
If rngUnlocked Is Nothing Then
Set rngUnlocked = rng
Else
Set rngUnlocked = Union(rng, rngUnlocked)
End If
End If

Next rng

ActiveSheet.Unprotect

rngUnlocked.CheckSpelling _
CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, _
AlwaysSuggest:=True

ActiveSheet.Protect

End Sub

 

[Dorci]

I'm getting the error "Object required" but it's not specifying where in the
code it's failing.

 

[Dorci]

Any idea why I would get theerror "Object required" when I insert the code
below?

 

[Gord Dibben]

Try this change............

For Each rng In ActiveSheet.UsedRange


Gord Dibben MS Excel MVP

 

[Dorci]

THAT WORKED!!! Thanks so much!
~Dorci

Try this change............

For Each rng In ActiveSheet.UsedRange


Gord Dibben MS Excel MVP

 

[Dorci]

I discovered a problem with the code. When it protects the sheet at the end,
it doesn't keep all of the protection settings it had originally. The
original settings allow users to (1) select locked cells, (2) select unlocked
cells, (3) format cells, (4) format columns, (5) format rows. After the
spell check, the only options set are (1) and (2).

Can I specify the settings in the ActiveSheet.Protect command?

Thanks!

 

[Gord Dibben]

Remove the "ActiveSheet.Protect" line above "End Sub"

Replace with this construction

With ActiveSheet
.Protect AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
.EnableSelection = xlNoRestrictions
End With


Gord

 

[Dorci]

*Whew* That worked! Thanks Gord!

~Dorci

Remove the "ActiveSheet.Protect" line above "End Sub"

Replace with this construction

With ActiveSheet
.Protect AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
.EnableSelection = xlNoRestrictions
End With


Gord