2010 Excel VBA - If Workbook is already Protected End Sub Issue

Question

I have a button that uses the sub ProtectAll_ADMIN() code below.

When the administrator has already protected the workbook an error will occur without checking if the WorkBook is already protected. So I want a message box to tell the administrator that the Workbook has already been protected. The following code will give me the message that it is already protected even if it has not been. If I strip out the following lines of code out it will run fine, but I am back to square one again and it will error out because a column is already hidden from my Call mcr_HideRowsColumns_ADMIN(). i.e. I wanted this sub routine separate, because there are a lot of sheets that need hidden columns and rows.

 If ActiveWorkbook.ProtectStructure Then End
    MsgBox ActiveWorkbook.Name & " is already protected.", _
        vbCritical
 Exit Sub

Here is the entire code below and I would truly appreciate someone's keen eye:

Sub ProtectAll_ADMIN()

     Dim S As Object

     If ActiveWorkbook.ProtectStructure Then End
       MsgBox ActiveWorkbook.Name & " is already protected.", _
        vbCritical
     Exit Sub

     ' To Hide all rows and columns for editing
     Call mcr_HideRowsColumns_ADMIN

     Dim pWord1 As String, pWord2 As String
        pWord1 = InputBox("Please Enter the password")
     If pWord1 = "" Then Exit Sub
        pWord2 = InputBox("Please re-enter the password")

     If pWord2 = "" Then Exit Sub
     'Make certain passwords are identical
     If InStr(1, pWord2, pWord1, 0) = 0 Or _
        InStr(1, pWord1, pWord2, 0) = 0 Then
          MsgBox "You entered different passwords. No action taken!"
     Exit Sub
     End If
     For Each ws In Worksheets
         ws.Protect Password:=pWord1
     Next
         MsgBox "All Sheets are Protected."
     Exit Sub

    '-------------------------------------------
    Sheets("Home").Select
    Range("A1").Select

 End Sub

Any Thoughts? THANK YOU!

I am not sure why you use End? shouldn't it be something like: If ActiveWorkbook.ProtectStructure Then MsgBox ActiveWorkbook.Name & " is already protected.", vbCritical Exit Sub End If — RADO
@RADO Thanks for trying, that will actually give me a Compile Error: End If without block If — T-Rex
I redesigned your code and tested it, it works. Why it did not work before: you tested for workbook protection, and then set worksheet protection. These are two different objects. — RADO
As @RADO says ProtectStructure is only telling you whether the order of the sheets is protected ("True if the order of the sheets in the workbook is protected.") and, if it was, you were ending code execution. If it wasn't, you were displaying a message saying it was, and then returning to whatever called the subroutine. — YowE3K

RADO RADO · Accepted Answer · 2017-05-10T21:28:52

I slightly redesigned your code:

Sub ProtectAll_ADMIN()
Dim ws As Worksheet
Dim pWord1 As String
Dim pWord2 As String

For Each ws In Worksheets
    If ws.ProtectContents Then
        MsgBox ActiveWorkbook.Name & " is already protected.", vbCritical
        Exit Sub
    End If
Next ws

' To Hide all rows and columns for editing
Call mcr_HideRowsColumns_ADMIN

pWord1 = InputBox("Please Enter the password")
If pWord1 = "" Then Exit Sub

pWord2 = InputBox("Please re-enter the password")
If pWord2 = "" Then Exit Sub

'Make certain passwords are identical
If InStr(1, pWord2, pWord1, 0) = 0 Or InStr(1, pWord1, pWord2, 0) = 0 Then
    MsgBox "You entered different passwords. No action taken!"
    Exit Sub
End If

For Each ws In Worksheets
     ws.Protect Password:=pWord1
Next

MsgBox "All Sheets are Protected."

End Sub

2010 Excel VBA - If Workbook is already Protected End Sub Issue

1 Answers