Spell Checking on a protected sheet

M

mooresk257

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!
 
S

Simon Lloyd

Thats simply because you have the PROTECT code within the IF statement,
put it after the END IF and all should be fine.


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)
 
S

Simon Lloyd

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

--------------------






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)
 
M

mooresk257

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 said:
You also have this:
Click to expand...
VBA Code:
Click to expand...
If ActiveSheet.Protection.AllowFormattingRows = False Then
ActiveSheet.Protect AllowFormattingRows:=True
End If
Click to expand...
VBA Code:
Click to expand...
If ActiveSheet.Protection.AllowFormattingRows = False Then
ActiveSheet.Protect Password:="password", AllowFormattingRows:=True
End If
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/showthread.php?t=184741

Excel Live Chat

.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Spell Checking on a protected sheet 4
including macro in protected worksheet 2
Spell Check Macro - Move to Cell 0
Sorting a protected worksheet 1
spellchecking only the unlocked cells of a protected spreadsheet? 5
Spell Check Macro Help 1
Modifying a locked/protected sheet 3
Spell Check 3

Top