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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
spellchecking only the unlocked cells of a protected spreadshe
Excellent makro!, but
it would be very nice if Excel could do this without any macro. -- Bengt Nilsson, Staffanstorp, Sweden "Paul B" wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
spellchecking only the unlocked cells of a protected spreadsheet?
it would be very nice if Excel could do this without any macro 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 -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=478912 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
spellchecking only the unlocked cells of a protected spreadshe
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. -- Bengt Nilsson, Staffanstorp, Sweden "davidm" wrote: it would be very nice if Excel could do this without any macro 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 -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=478912 |
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 |