Spell Check Macro for Protected Workbook 1

[shelby55]

Hello

I am using Excel 2010.

I found the following code on the internet and have it working to spell check a protected worksheet:

Sub spellcheck()
ActiveSheet.Unprotect
Cells.CheckSpelling customdictionary:="CUSTOM.DIC", ignoreuppercase:=False, _
alwayssuggest:=True, SpellLang:=1033
ActiveSheet.Protect
End Sub

In the same way that I would spell check multiple sheets if not protected, I selected many worksheets and ran this macro but it didn't look at all worksheets. How can I get it to spell check as many worksheets as I select?

Thanks very much.

 

[SkipVought]

hi,

Is it necessary to only spell check a few selected sheets? Why not all?

Or why not spell check any sheet on the Sheet Deactivate event? Why wait and try to do a group?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shelby55]

Hi Skip

Brilliant questions as always! There are a few worksheets I don't need spell checked but for those that I do, you're right that I can check them all at one time.

I think having a button to check all instead of the sheet deactivate event might be preferable. Thanks.

 

[shelby55]

Any suggestions on how to make the button check all worksheets and not just current?
Thanks.

 

[PHV]

Something like this ?

For Each sh In ActiveWorkbook.Worksheets
    sh.UnProtect
    sh.Cells.CheckSpelling customdictionary:="CUSTOM.DIC", ignoreuppercase:=False, _
                           alwayssuggest:=True, SpellLang:=1033
    sh.Protect
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

Hi PHV

Thanks very much. Is there any way to exclude some sheets from the spell check? I'm trying to create a select case statement but can't get it to work:

Sub spellcheck()
Dim sh As Object

Select Case sh.Name

Case "Master", "Reabstracters", "Master_NewB", "Mapping_NewC", "Master_NewC", _
        "RawData_A", "Mapping_NewA", "RawDataA_Map", "Values", "Master_NewA", "Readme_Clients", _
        "Mapping_NewB", "Rat_Val", "Features"

Case Else


For Each sh In ActiveWorkbook.Worksheets
    Sh.Unprotect
    sh.Cells.CheckSpelling customdictionary:="CUSTOM.DIC", ignoreuppercase:=False, _
                           alwayssuggest:=True, SpellLang:=1033
    Sh.Protect

End Select

Next
End Sub

I get the error message that "End Select without select case". Thanks.

 

[PHV]

What about this ?

Sub spellcheck()
Dim sh As Object
For Each sh In ActiveWorkbook.Worksheets
    Select Case sh.Name
    Case "Master", "Reabstracters", "Master_NewB", "Mapping_NewC", "Master_NewC", _
         "RawData_A", "Mapping_NewA", "RawDataA_Map", "Values", "Master_NewA", "Readme_Clients", _
         "Mapping_NewB", "Rat_Val", "Features"
    Case Else
        sh.UnProtect
        sh.Cells.CheckSpelling customdictionary:="CUSTOM.DIC", ignoreuppercase:=False, _
                               alwayssuggest:=True, SpellLang:=1033
        sh.Protect
    End Select
Next
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

You rock, PHV, thanks so much!!

 

[Hoaokapohaku]

Shelby...I notice several posts where worksheet protection is an issue for your macros. If you put this event procedure in your "ThisWorkbook" object, your macros can run without constantly having to protect and unprotect sheets. It also ensures that if you unprotect a worksheet and then forget to re-protect it, the next time the file opens, all sheets will be protected. It does have the disadvantage that if someone knows enough to look in the right place using the VBA editor, they can find out your password. But the person who knows to do this probably knows how to crack your passwords anyway, or knows how to find out how.

Private Sub Workbook_Open()
Dim wSheet As Worksheet
    For Each wSheet In Worksheets
        wSheet.Protect Password:="password", UserInterFaceOnly:=True
    Next wSheet
End Sub

Of course you must replace "password" with whatever your desired password is.

 

[shelby55]

Hi Hoaokapohaku

Thanks very much for your reply. I have many worksheets in my workbook and only some columns of each (all the same ones of K5 to X119) are to be unlocked for data entry. Can I put the code in the workbook On Open event as above to protect the workbook but have these columns unlocked? Note that I have them formatted as unlocked but for whatever reason, it doesn't stay that way once the book is protected.

Thanks.

 

[Hoaokapohaku]

Shelby,

Yes. While in the normal user interface, the sheets will be protected as always. Unlocked cells will be editable, lock cells will not.

I'm not sure what's going on with your issue of unlocked cells becoming locked when you protect the workbook. How are you unlocking the cells and protecting the workbook when this problem occurs? Are you using the user interface or VBA? Do you perhaps have some code someplace you forgot about that is changing the cells to locked?