Spell Check - locked sheet

trevolly

Board Regular
Joined
Aug 22, 2021
Messages
124
Office Version
  1. 365
Platform
  1. Windows
Hi all

I have an excel worksheet that has several buttons made that when pressed runs a macro (to change the colour and size of text in cells when the cell is highlighted). This works fine when the sheet is locked and the right options are selected from the "protect sheet" menu.

I have discovered that the spellcheck is disabled when a worksheet is protected so I have found a macro that when runs uses my worksheet password to unlock the sheet, run spellcheck and then re-lock the worksheet. This works fine as well. This is the code...

VBA Code:
Sub SpellCheckCell()
'Lock sheet spellcheck
    With ActiveSheet
        .Unprotect ("pass")
        .Range("D5:D250").CheckSpelling
        .Protect ("pass")
        MsgBox "Spell Check Complete"
    End With
End Sub

The thing I have discovered is that when the spellcheck macro runs and unlocks and then re-locks the worksheet it also resets the "protect sheet" menu - not selecting the original settings and therefore not letting the cells to be formatted and the macros mentioned above to run.

Has anyone else encountered this and is there a workaround? Or do I just have to realise that if I lock the worksheet I cant have spellcheck.

Thanks all
T
 
Last edited:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
You don't need a workaround, just a simple change to one line. Try:
VBA Code:
Sub SpellCheckCell()
'Lock sheet spellcheck
    With ActiveSheet
        .Protect "pass", userinterfaceonly:=True
        .Range("D5:D250").CheckSpelling
         MsgBox "Spell Check Complete"
    End With
End Sub
 
Upvote 0
Solution
Thanks @JoeMo ! The only thing I've also discovered is that it changes the ability to add or delete a row in to the sheet. I use it as a daily log and sometimes you have to retrospectively add a row to type an entry earlier in the day. Thanks for your time mate
 
Upvote 0
Thanks @JoeMo ! The only thing I've also discovered is that it changes the ability to add or delete a row in to the sheet. I use it as a daily log and sometimes you have to retrospectively add a row to type an entry earlier in the day. Thanks for your time mate
You are welcome - thanks for the reply.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top