0

Following the query raised here: VBA Excel spellchecking for protected workbook

I would like to make my workbook protected in the same shape as it was at the beginning.

Basically most of my sheets are protected from row 1 to 5.

It has been marked as the Private_Sub for each sheet separately as follows:

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Const pw As String = "Secret"

 Dim rFormulaCheck As Range

 On Error Resume Next

 With Target
 .Parent.Unprotect pw
 .Locked = False
 .FormulaHidden = False

 If .Cells.Count = 1 Then
 If .HasFormula Then
 .Locked = True
 .FormulaHidden = True
 .Parent.Protect pw, , , , 1
 End If

 ElseIf .Count > 1 And .Count < 5 Then

 With .SpecialCells(xlCellTypeFormulas)
 .Locked = True
 .FormulaHidden = True
 .Parent.Protect pw, , , , 1
 End With

 End If

 End With

 On Error GoTo 0
End Sub

As you can see, the password has been set as "Secret" and it covers only the first 5 rows.

In terms of the Spellchecking I have to unlock them, as per the code below:

Sub Spellcheck()
   For Each sh In Worksheets
       sh.Unprotect "Secret"
       sh.CheckSpelling
       sh.Protect "Secret"
   Next sh
End Sub

However, when I use sh.Protect "Secret". I am unable to edit any cell throughout the document. It's expressed by error: The cell or chart you're trying to change is on a protected sheet. To make a change, unprotect the sheet. You might be requested to enter a password.

So effectively so far I must switch off the sh.Protect "Secret" if I want to make my document editable. Unfortunately the Private Sub Worksheet_SelectionChange(ByVal Target As Range) doesn't work anymore.

Is there a chance to bring these protection settings back to the initial stage after the spellchecking process?

UPDATE:

I was trying to do something like this:

 Sub Spellcheck()
    For Each sh In Worksheets
    sh.Unprotect "Secret"
    sh.CheckSpelling
    'sh.Protect "Secret"
   Next sh
  Call Worksheet_SelectionChange
End Sub

But now I am getting error: Sub or function not defined.

It's not defined in module, but it's defined in the most of the sheets.

Geographos
  • 827
  • 2
  • 23
  • 57
  • 2
    Note that `Worksheet_SelectionChange` this will run automatically when ever you click/select a cell/range in your worksheet (while `Target` is the selected area). Actually I don't get the purpose of all this. What is the overall goal you are trying to archieve with that? • Also note that with `On Error Resume Next` you just hide your error messages, but the errors still occur. Remove it and fix your errors instead. – Pᴇʜ Feb 26 '20 at 12:32
  • No, you are not right. When I reopen the document I have all my cells locked. On the other hand, they are all unlocked when I take down the last line of the code. The Private Sub Worksheet_SelectionChange is gone. – Geographos Feb 26 '20 at 12:48
  • Note that what I said is a fact in Excel (and has nothing to do with your *specific* code), it is just like that by definition. So please explain how I cannot be right. • Your code just doesn't make any sense to me. Therefore please explain in words what it should you. What is the purpose of doing all that? Also explain what do you mean by "Private Sub Worksheet_SelectionChange is gone"? • Maybe do that by [edit]ting your question, because I think a comment is not enough for that kind of explanation. – Pᴇʜ Feb 26 '20 at 12:54
  • OK, I edited my question now. I can add some pics too. – Geographos Feb 26 '20 at 12:57
  • Note You cannot call `Worksheet_SelectionChange` this is an **Event** and runs automatically if the selection of a cell/range in your worksheet changes. • Good I ask *again* for it. Please describe in words what the purpose of your code is and what it shoul do! – Pᴇʜ Feb 26 '20 at 12:57
  • Not sure of you are aware of `UserInterfaceOnly:=True`. You can protect the worksheet using say `Sheet1.Protect Password:="Secret", UserInterfaceOnly:=True` and then you do not need to unprotect it to do the spellcheck... – Siddharth Rout Feb 26 '20 at 12:58
  • @SiddharthRout don't forget that this comes with the odd thing that `UserInterfaceOnly` state get's lost on every closing of the workbook and has to be reset in the _Open event ^^ (still hate this feature for its behavior). – Pᴇʜ Feb 26 '20 at 12:59
  • You can read about `UserInterfaceOnly` in [Worksheet.Protect method (Excel)](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.protect) – Siddharth Rout Feb 26 '20 at 13:00
  • @Pᴇʜ: Yup you are right. Can be easily handled in `Workbook_Open()` – Siddharth Rout Feb 26 '20 at 13:00
  • It looks like @Pᴇʜ is right. The sheet becomes protected again, as I click somewhere. I didn't notice it before. You can downvote me now, once this query hadn't make sense. – Geographos Feb 26 '20 at 13:07
  • 2
    @MariuszKrukar why down vote? If you don't need help anymore you can just delete your question or post an answer yourself if you found one (and if you think it could be useful for someone else). If you still need help I recommend to delete this one and ask a new more clear question. Don't forget do include your expected behaviour then ([mcve] might help to ask good questions in the future). – Pᴇʜ Feb 26 '20 at 13:10
  • Because it looks like this question was my fault since I "discovered" that Private Sub Worksheet_SelectionChange(ByVal Target As Range) brings back my protect automatically. I picked up the wrong ones to test. They weren't equipped with this code and that's why I escalated this problem. – Geographos Feb 26 '20 at 13:33

0 Answers0