Public password not recognized in a protected sheet

Question

I have the sub "ProtectAllSheets" in Module1 and another one on Sheet1 object, which is meant to delete the whole rows if there is a #REF! Error in column C of Sheet1. It works fine when the sheet is protected... BUT somehow when I close the workbook and open it again(Which is still protected) it doesn't delete the rows with Errors eventhough it is "UserInterface=True". If I unprotect the sheet, it works fine. It seems like as soon as I close the workbook some information is lost "somehow"... I just cannot grasp what the hell could be wrong with the code.

Option Explicit
Public pwd1 As String, pwd2 As String

Sub ProtectAllSheets()
Dim ws As Worksheet

  pwd1 = InputBox("Enter your password", "")
    If pwd1 = "" Then Exit Sub
  pwd2 = InputBox("Enter the password again", "")
    If pwd2 = "" Then Exit Sub
    'Checks if both the passwords are identical
    If InStr(1, pwd2, pwd1, 0) = 0 Or _
    InStr(1, pwd1, pwd2, 0) = 0 Then
    MsgBox "Please type the same password. ", vbInformation, ""
      Exit Sub
    End If
For Each ws In ActiveWorkbook.Sheets
    If ws.ProtectContents = False = True Then
      ws.Protect Password:=pwd1, UserInterFaceOnly:=True
    End If
        Next ws
MsgBox "Sheets are protected."
End Sub

Option Explicit

Sub Worksheet_Activate()
Dim sh As Worksheet
Dim c As Long
Set sh = ActiveSheet

    For c = 400 To 2 Step -1
        If IsError(Cells(c, 3)) Then
         Rows(c).EntireRow.Delete
        End If
    Next c

End Sub

`If ws.ProtectContents = False = True Then` is bad code. Use `If not ws.ProtectContents Then` — , Sep 13 '18 at 12:32
See https://stackoverflow.com/questions/14162483/excel-vba-userinterfaceonly-true-not-working — Axel Richter, Sep 13 '18 at 13:04
and `InStr(1, pwd2, pwd1, 0) = 0 ` can be covered by `pwd1=pwd2` — Nathan_Sav, Sep 13 '18 at 13:04
Possible duplicate of [EXCEL VBA UserInterfaceOnly:= True not working](https://stackoverflow.com/questions/14162483/excel-vba-userinterfaceonly-true-not-working) — Comintern, Sep 13 '18 at 13:06

score 0 · Answer 1 · answered Sep 14 '18 at 07:19

Ok Folks, for those of you who may have a similar problem. I know is not the best solution but it works. It seems like the UserInterFaceOnly feature is not being saved in the file, as soon as you close and reopen the workbook, it is gone. So, this is what I did. Step 1. Deleted Sub ProtectAllSheets () Step 2. Inserted A Workbook_Open procedure.

Now... someone can still see the pasword "1214" if they hit Alt+F11. I just can't do much about it.

Sub Workbook_Open()

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
      If Not ws.ProtectContents Then
      ws.Protect Password:=1214, UserInterFaceOnly:=True
      End If
        Next ws
        
    MsgBox "Sheets are protected."

End Sub

Step 3. Kept the same Sub by Worksheet_Activate ()

Sub Worksheet_Activate()

Dim sh As Worksheet
Dim c As Long
Set sh = ActiveSheet

    For c = 400 To 2 Step -1
        If IsError(Cells(c, 3)) Then
            Rows(c).EntireRow.Delete
        End If
            Next c
End Sub

Public password not recognized in a protected sheet

1 Answers1