• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / VBA / Spell Check a Protected Excel Worksheet

Spell Check a Protected Excel Worksheet

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

  1. Press Alt + F11 to open the Visual Basic Editor
  2. Click Insert > Module to insert a new code module
  3. 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.

Related Posts:

  • SEQUENCE Function in Excel
    SEQUENCE Function in Excel
  • Import Multiple Excel Files with Multiple Sheets in Excel
    Import Multiple Excel Files with Multiple Sheets in Excel
  • Excel FILTER Function – The Best Function in Excel
    Excel FILTER Function – The Best Function in Excel
  • Moving Average in Excel
    Moving Average in Excel

Reader Interactions

Comments

  1. Jo W-M says

    10 April 2017 at 3:17 pm

    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

    Reply
    • computergaga says

      12 April 2017 at 12:05 pm

      Hi Jo,
      I suspect you may have inadvertently deleted a double quote ”
      Alan

      Reply
      • Robert says

        13 April 2017 at 1:34 pm

        When I try it, it gives me the same code on the Unprotect sheet line

        Reply
        • Robert says

          13 April 2017 at 1:35 pm

          Error code is
          Compile Error
          Syntax code

          Reply
          • computergaga says

            14 April 2017 at 5:48 am

            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.

  2. Baljit says

    6 June 2017 at 2:09 pm

    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

    Reply
    • computergaga says

      6 June 2017 at 3:57 pm

      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

      Reply
      • Baljit says

        8 June 2017 at 3:12 pm

        Great,Thanks a lot.

        Reply
  3. Michele says

    14 February 2018 at 6:56 pm

    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.

    Reply
    • computergaga says

      15 February 2018 at 10:11 pm

      Sure. Maybe don’t protect the worksheet immediately after spell checking like the code does. Remove the line after the checkspelling line.

      Reply
  4. Julie says

    26 June 2020 at 6:40 pm

    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!

    Reply
    • Alan Murray says

      23 July 2020 at 9:11 pm

      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

      Reply
  5. Josh says

    28 September 2022 at 2:13 pm

    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?

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Popular Posts

  • Excel Fixtures and League Table Generator
  • 5 Reasons Why your Excel Formula is Not Calculating
  • Excel IF Function Contains Text – A Partial Match in a Cell
  • Excel Formula to Display the Sheet Name in a Cell
  • 4 Alternatives to Nested IF Formulas
  • How to Hyperlink to a Hidden Worksheet in Excel
  • How to Create a Reverse FIND Formula
  • Check if a Value is an Even Number
  • IF Function in Power Query Including Nested IFS
  • Conditional Formatting Multiple Columns – 3 Examples

Recent Posts

  • Moving Average in Excel
  • Excel IMAGE Function – Insert Images from a Cell Value
  • SEQUENCE Function in Excel
  • Sort by Drop Down List in Excel
  • Advanced SUM Function Examples – The Power of SUM
Advanced Excel Success book

Footer

Contact

[email protected]

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2020 Computergaga · All Rights Reserved ·