Worksheet protection is used in Excel to protect cells containing formulas, hide sensitive data and much more.
Unfortunately one of the problems that arises from protecting a worksheet is the inability to spell check a worksheet.
To be able to spell check a protected worksheet you need a macro to unprotect the sheet, perform the spell check, and then protect the sheet again.
Spell Check a Protected Worksheet
- Press Alt + F11 to open the Visual Basic Editor
- Click Insert > Module to insert a new code module
- Copy and paste the code below into the module window being sure to replace Stock Sheet with the necessary sheet name and excel with the necessary password.
Sub SpellCheckSheet()
Sheets("Stock Sheet").Unprotect "excel"
ActiveSheet.CheckSpelling
Sheets("Stock Sheet").Protect "excel"
End Sub
This code generates a macro called SpellCheckSheet. To run the code easily from the sheet, assign the macro to a button. This button can be placed on the toolbar and run at any time.
Hi there Alan,
I have tried this tutorial and when I change to name of the sheet to mine I get an error message which says “Compile error: Expected: list separator or )
I copied and pasted the code so I’m not sure what I have done wrong – is there anyway you could help please?
Thanks, Jo
Hi Jo,
I suspect you may have inadvertently deleted a double quote ”
Alan
When I try it, it gives me the same code on the Unprotect sheet line
Error code is
Compile Error
Syntax code
Sorry Robert, not sure I understand the problem. Each statement needs to be on a separate line as displayed in the blog post. That may be the issue.
I have many tabs on my workbook “Sunday, Monday, Tuesday…
I was able to run the spellcheck macro on “Sunday Sheet” under Module 1, HOW to set up the macro on other sheets? I followed the same method for other sheets and created a Module 2 for Monday but the spell check is given a error. Please help
Hi Baljit,
Change the code to not mention the sheet name and it may work such as;
Sub SpellCheckSheet()
activesheet.Unprotect “excel”
ActiveSheet.CheckSpelling
activesheet.Protect “excel”
End Sub
Alan
Great,Thanks a lot.
Is there a way to highlight the cell in which there is a misspelled word, like the REVIEW ribbon-Spell check works? Sometimes it is difficult to see if a word needs to be changed without the context.
Sure. Maybe don’t protect the worksheet immediately after spell checking like the code does. Remove the line after the checkspelling line.
If i need to spell check multiple locked sheets in the same workbook, do I need to type in every worksheet name? Or is there a way to edit this macro to check all tabs (which are all protected)?
Thanks!
Hi Julie,
Sure. We could set up a loop to move through all the worksheets.
Dim shtCount As Integer
Dim sht As Integer
shtCount = ActiveWorkbook.Worksheets.Count
For sht = 1 To shtCount
with Sheets(sht)
.Unprotect “excel”
.CheckSpelling
.Protect “excel”
End With
Next sht
I have a protected worksheet and the macro works correctly, per se. My issue is that I have text colors on the template to identify what needs to be added, but once the spell check macro runs, it protects the worksheet without keeping the format cell and format row options checked. How to I make sure that remains after the macro runs?