Spell Check Protect Reprotect with same settings

P

PatriciaT

Hello. I've been able to use below vb to spell check 'while protected'. My
issue is that i want to allow the user to be able to formatt columns and rows
(resize). When i execute the macro it clears those check marks from
protection list. Hoping someone can advise. Thank you
------------
Requires VBA to unprotect the sheet, do the spellcheck then reprotect the
sheet.

Similar to.......

Sub Spell_Check()
ActiveSheet.Unprotect Password:="justme"
Cells.CheckSpelling SpellLang:=1033
ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub


Gord Dibben Excel MVP
 
G

Gord Dibben

Using the macro recorder will generally give you the code you need for
allowing elements when protection is enabled.

Try this................

Sub Spell_Check()
With ActiveSheet
.Unprotect Password:="justme"
.Cells.CheckSpelling SpellLang:=1033
.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True, AllowFormattingRows:=True, _
AllowInsertingColumns:=True, AllowInsertingRows:=True
.EnableSelection = xlNoRestrictions
End With
End Sub

For EnableSelection you have 3 properties. Edit to suit.

xlNoSelection
xlNoRestrictions
xlUnlockedCells


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top