Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
spellchecking only the unlocked cells of a protected spreadsheet?
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 doesnt 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("D2:D5").CheckSpelling CustomDictionary:="CUSTOM.DIC", IgnoreUppercase:=False, AlwaysSuggest:=True Worksheets("Sheet1").Range("D9:D16").CheckSpelling CustomDictionary:="CUSTOM.DIC", IgnoreUppercase:=False, AlwaysSuggest:=True Worksheets("Sheet1").Range("D19:D25").CheckSpellin g CustomDictionary:="CUSTOM.DIC", IgnoreUppercase:=False, AlwaysSuggest:=True Worksheets("Sheet1").Range("D28:D34").CheckSpellin g 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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
spellchecking only the unlocked cells of a protected spreadsheet?
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 "Radcon trainer" wrote in message ... 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("D2:D5").CheckSpelling CustomDictionary:="CUSTOM.DIC", IgnoreUppercase:=False, AlwaysSuggest:=True Worksheets("Sheet1").Range("D9:D16").CheckSpelling CustomDictionary:="CUSTOM.DIC", IgnoreUppercase:=False, AlwaysSuggest:=True Worksheets("Sheet1").Range("D19:D25").CheckSpellin g CustomDictionary:="CUSTOM.DIC", IgnoreUppercase:=False, AlwaysSuggest:=True Worksheets("Sheet1").Range("D28:D34").CheckSpellin g 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spellchecking a protected document in Excel | Excel Discussion (Misc queries) | |||
Unlocked Cells in Protected Sheet | Excel Worksheet Functions | |||
paste locked cells and unlocked cells in protected sheet | Excel Worksheet Functions | |||
allow comments, unlocked cells, protected sheet | Excel Programming | |||
Spell Check an Unlocked Cell in a Protected Spreadsheet | Excel Worksheet Functions |