spellchecking only the unlocked cells of a protected spreadsheet?

[Radcon trainer]

Paul B



I created a form in Excel 2002 that is distributed to multiple users as a
protected form with locked and unlocked cells that allow users to make
entries in the unlocked cells. Being protected, the form doesn’t allow users
to spell check after completing their entries. I found your correspondence
on-line concerning this issue and used the code that you suggested. This,
however, performs the spell check on the locked cells as well as the unlocked
cells. So, I modified the code to try and only spell check the unlocked cells
as follows:



Sub Spell_Check()

ActiveSheet.Unprotect Password:="123"

Worksheets("Sheet1").Range("B2:B6").CheckSpelling
CustomDictionary:="CUSTOM.DIC", IgnoreUppercase:=False, AlwaysSuggest:=True

Worksheets("Sheet1").Range("D25").CheckSpelling
CustomDictionary:="CUSTOM.DIC", IgnoreUppercase:=False, AlwaysSuggest:=True

Worksheets("Sheet1").Range("D916").CheckSpelling
CustomDictionary:="CUSTOM.DIC", IgnoreUppercase:=False, AlwaysSuggest:=True

Worksheets("Sheet1").Range("D1925").CheckSpelling
CustomDictionary:="CUSTOM.DIC", IgnoreUppercase:=False, AlwaysSuggest:=True

Worksheets("Sheet1").Range("D2834").CheckSpelling
CustomDictionary:="CUSTOM.DIC", IgnoreUppercase:=False, AlwaysSuggest:=True

Worksheets("Sheet1").Range("A36").CheckSpelling
CustomDictionary:="CUSTOM.DIC", IgnoreUppercase:=False, AlwaysSuggest:=True

ActiveSheet.Protect Password:="123"

End Sub



This progressed thru each range of cells as desired, but after the last
range was checked the spell checker displayed the Dialog Box “Do you want to
continue checking at the beginning of the sheet?†Selecting “Yes†then sends
the Spell Checker through the Locked Cells allowing the user to make a change
each time the spell checker finds a word or abbreviation or acronym that is
not in the user dictionary.



Is it possible to only spell check unlocked cells?

Also, how do I protect the macro from being edited by the user?

 

[Paul B]

Radcon, try this,

Sub spell_check()
'spell check only unprotected cells
'Original code By: Dick Kusleika
Dim rng As Range
Dim cell As Range
ActiveSheet.Unprotect Password:="123"


For Each cell In Sheet1.UsedRange
If Not cell.Locked Then
If rng Is Nothing Then
Set rng = cell
Else
Set rng = Union(rng, cell)
End If
End If
Next cell
rng.CheckSpelling
ActiveSheet.Protect Password:="123"

End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Paul B]

Radcon, try this,

Sub spell_check()
'spell check only unprotected cells
'Original code By: Dick Kusleika
Dim rng As Range
Dim cell As Range
ActiveSheet.Unprotect Password:="123"


For Each cell In Sheet1.UsedRange
If Not cell.Locked Then
If rng Is Nothing Then
Set rng = cell
Else
Set rng = Union(rng, cell)
End If
End If
Next cell
rng.CheckSpelling
ActiveSheet.Protect Password:="123"

End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Bengt Nilsson, Staffanstorp, Sweden]

Excellent makro!, but
it would be very nice if Excel could do this without any macro.

 

[davidm]

Interesting reflection this one! I guess Microsoft's object is to
provide, in all cases, a "basic" tool but flexibile enough to be
modified, adapted, remodeled by the user via VBA. Which is why it is
invaluable to know one's way around VBA to some degree.


David

 

[Bengt Nilsson, Staffanstorp, Sweden]

If you create an Excel sheet, add the excellent macro, protect the sheet,
test if the macro spell check the unprotected cells, and then send the file
to a user that will enter data in to it.

The macro will not work since Excel standard settings for macros are;
disabled.