VBA Excel spellchecking for protected workbook

Question

I would like to run an automatic Spell checking for my Excel workbook.

Previously I used the method explained in this query:

Everything seems to be alright, although at the very end I am getting an error:

Run-time error '1004': CheckSpelling method of Range class failed

However, my spellchecking looks like is done, since I have no same errors anymore. Unfortunately, it's quite awkward, because I cannot run another VBA code on my workbook since the debugger shows error.

I found the code relevant for Spellchecking of the protected sheet here:

https://www.computergaga.com/blog/spell-check-a-protected-excel-worksheet/

and finally I prepared my own code including them two:

 Sub Spellcheck3()
 For Each sh In Worksheets
 Sheets(sh.Name).Cells.Unprotect "excel"
 ActiveSheet.CheckSpelling
 Sheets(sh.Name).Cells.Protect "excel"
 Next
 End Sub

Unfortunately, I am getting error: Object doesn't support this property or method.

The debug line is rough:

   Sheets(sh.Name).Cells.Unprotect "excel"

Maybe should I target some set of cells on each worksheet as per the query below: Excel - VBscript for Spellcheck

I would like to have correct spellchecking with the comment at the end, that everything has been finished correctly. I have some cells protected across my workbook, and that's why the debugger appears.

Pᴇʜ Pᴇʜ · Accepted Answer · 2020-02-26T11:32:33

Note that ActiveSheet is not the sheet you unprotect Sheets(sh.Name) and Cells as no method .Unprotect that is why you get the error

Object doesn't support this property or method.

It should be

Sub Spellcheck3()
    For Each sh In Worksheets
        sh.Unprotect "excel"
        sh.CheckSpelling
        sh.Protect "excel"
    Next sh
End Sub

Avoid using ActiveSheet at any cost (see also How to avoid using Select in Excel VBA).

Also note that Sheets(sh.Name) is a bit overkill. You try to get the name sh.Name of a sheet sh just to then get the sheet with that name Sheets(sh.Name) but you already had that sheet in just sh.

VBA Excel spellchecking for protected workbook

1 Answers