| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spell Checking on a protected sheet
Hi Folks,
I have a sheet that is protected, and have the following code on a control_click event to allow spell checking. It unprotects the sheet, runs the spelling check function, then re-protects the sheet with the original password. It also allows row height to be formatted when the sheet is protected. Private Sub SpellCheck_Click() ActiveSheet.Unprotect Password:="Password" Cells.CheckSpelling _ CustomDictionary:="CUSTOM.DIC", _ IgnoreUppercase:=False, _ AlwaysSuggest:=True ActiveSheet.Protect Password:="Password" ' Allow rows to be formatted (autofit) on a protected worksheet. If ActiveSheet.Protection.AllowFormattingRows = False Then ActiveSheet.Protect AllowFormattingRows:=True End If End Sub This works fine if there is at least one spelling error. However, if there are zero errors on the sheet, then this code re-protects the sheet without a password. Any user can run the spell checker to remove the password, then just unprotect the sheet. Any suggestions on how to fix this? I'd like to allow users to run spell checking and adjust row height, but I want it to stay password protected too! Thanks! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Spell Checking on a protected sheet
Thats simply because you have the PROTECT code within the IF statement, put it after the END IF and all should be fine. mooresk257;661544 Wrote: Hi Folks, I have a sheet that is protected, and have the following code on a control_click event to allow spell checking. It unprotects the sheet, runs the spelling check function, then re-protects the sheet with the original password. It also allows row height to be formatted when the sheet is protected. Private Sub SpellCheck_Click() ActiveSheet.Unprotect Password:="Password" Cells.CheckSpelling _ CustomDictionary:="CUSTOM.DIC", _ IgnoreUppercase:=False, _ AlwaysSuggest:=True ActiveSheet.Protect Password:="Password" ' Allow rows to be formatted (autofit) on a protected worksheet. If ActiveSheet.Protection.AllowFormattingRows = False Then ActiveSheet.Protect AllowFormattingRows:=True End If End Sub This works fine if there is at least one spelling error. However, if there are zero errors on the sheet, then this code re-protects the sheet without a password. Any user can run the spell checker to remove the password, then just unprotect the sheet. Any suggestions on how to fix this? I'd like to allow users to run spell checking and adjust row height, but I want it to stay password protected too! Thanks! -- Simon Lloyd Regards, Simon Lloyd 'Excel Chat' (http://www.thecodecage.com/forumz/chat.php) ------------------------------------------------------------------------ Simon Lloyd's Profile: 1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=184741 Excel Live Chat |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Spell Checking on a protected sheet
You also have this: VBA Code: -------------------- If ActiveSheet.Protection.AllowFormattingRows = False Then ActiveSheet.Protect AllowFormattingRows:=True End If -------------------- Which is protecting without a password, it should read VBA Code: -------------------- If ActiveSheet.Protection.AllowFormattingRows = False Then ActiveSheet.Protect Password:="password", AllowFormattingRows:=True End If -------------------- mooresk257;661544 Wrote: Hi Folks, I have a sheet that is protected, and have the following code on a control_click event to allow spell checking. It unprotects the sheet, runs the spelling check function, then re-protects the sheet with the original password. It also allows row height to be formatted when the sheet is protected. Private Sub SpellCheck_Click() ActiveSheet.Unprotect Password:="Password" Cells.CheckSpelling _ CustomDictionary:="CUSTOM.DIC", _ IgnoreUppercase:=False, _ AlwaysSuggest:=True ActiveSheet.Protect Password:="Password" ' Allow rows to be formatted (autofit) on a protected worksheet. If ActiveSheet.Protection.AllowFormattingRows = False Then ActiveSheet.Protect AllowFormattingRows:=True End If End Sub This works fine if there is at least one spelling error. However, if there are zero errors on the sheet, then this code re-protects the sheet without a password. Any user can run the spell checker to remove the password, then just unprotect the sheet. Any suggestions on how to fix this? I'd like to allow users to run spell checking and adjust row height, but I want it to stay password protected too! Thanks! -- Simon Lloyd Regards, Simon Lloyd 'Excel Chat' (http://www.thecodecage.com/forumz/chat.php) ------------------------------------------------------------------------ Simon Lloyd's Profile: 1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=184741 Excel Live Chat |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Spell Checking on a protected sheet
Thanks for your help - problem solved!
This was how I re-wrote the code with your suggestions: Private Sub SpellCheck_Click() ActiveSheet.Unprotect Password:="0000" Cells.CheckSpelling _ CustomDictionary:="CUSTOM.DIC", _ IgnoreUppercase:=False, _ AlwaysSuggest:=True ' Allow rows to be formatted (autofit) on a protected worksheet. If ActiveSheet.Protection.AllowFormattingRows = False Then ActiveSheet.Protect Password:="0000", AllowFormattingRows:=True End If ActiveSheet.Protect Password:="0000" End Sub "Simon Lloyd" wrote: You also have this: VBA Code: -------------------- If ActiveSheet.Protection.AllowFormattingRows = False Then ActiveSheet.Protect AllowFormattingRows:=True End If -------------------- Which is protecting without a password, it should read VBA Code: -------------------- If ActiveSheet.Protection.AllowFormattingRows = False Then ActiveSheet.Protect Password:="password", AllowFormattingRows:=True End If -------------------- mooresk257;661544 Wrote: Hi Folks, I have a sheet that is protected, and have the following code on a control_click event to allow spell checking. It unprotects the sheet, runs the spelling check function, then re-protects the sheet with the original password. It also allows row height to be formatted when the sheet is protected. Private Sub SpellCheck_Click() ActiveSheet.Unprotect Password:="Password" Cells.CheckSpelling _ CustomDictionary:="CUSTOM.DIC", _ IgnoreUppercase:=False, _ AlwaysSuggest:=True ActiveSheet.Protect Password:="Password" ' Allow rows to be formatted (autofit) on a protected worksheet. If ActiveSheet.Protection.AllowFormattingRows = False Then ActiveSheet.Protect AllowFormattingRows:=True End If End Sub This works fine if there is at least one spelling error. However, if there are zero errors on the sheet, then this code re-protects the sheet without a password. Any user can run the spell checker to remove the password, then just unprotect the sheet. Any suggestions on how to fix this? I'd like to allow users to run spell checking and adjust row height, but I want it to stay password protected too! Thanks! -- Simon Lloyd Regards, Simon Lloyd 'Excel Chat' (http://www.thecodecage.com/forumz/chat.php) ------------------------------------------------------------------------ Simon Lloyd's Profile: 1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=184741 Excel Live Chat . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Spell Checking on a protected sheet | Excel Programming | |||
| spell check on protected sheet | Excel Discussion (Misc queries) | |||
| Spell checking a protected worksheet | Excel Discussion (Misc queries) | |||
| How can I use spell checking in a protected worksheet? | Excel Worksheet Functions | |||
| Spell checking a protected sheet | Excel Programming | |||
Linear Mode
