Spell Check - locked sheet

[trevolly]

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...

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

 

[JoeMo]

You don't need a workaround, just a simple change to one line. Try:

Sub SpellCheckCell()
'Lock sheet spellcheck
    With ActiveSheet
        .Protect "pass", userinterfaceonly:=True
        .Range("D5:D250").CheckSpelling
         MsgBox "Spell Check Complete"
    End With
End Sub

 

[trevolly]

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

 

[JoeMo]

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.