Check protection of all sheets in only active workbook-CodePudding

I am working on a macro to protect all sheets in a workbook. It will exit the sub if there are already sheets protected, which is a feature I want. However, it checks not only the active workbook but all sheets in any open workbooks for protection. Is there a way to specify only the sheets in the active workbook?

Here is part of my code:

For i = 1 To Worksheets.Count
If Worksheets(i).ProtectContents = True Then GoTo Error
Next
For Each s In ActiveWorkbook.Worksheets
s.Protect Password:=pass
Next
Exit Sub
Error: MsgBox "I think you have some sheets that are already protected. Please unprotect all sheets before using this function.", vbCritical, "Error"
End Sub

CodePudding user response：

Try using a variable and ThisWorkbook instead of ActiveWorkbook.

Option Explicit

Const pass = "yourpassword" 'don't forget to password protect the VBA project!

Public Sub ProtectionCheck()

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook

For Each ws In wb.Worksheets
    If ws.ProtectContents Then
        MsgBox "I think you have some sheets that are already protected. Please unprotect all sheets before using this function.", vbCritical, "Error"
        Exit Sub
    End If
Next ws

'we got this far, now let's protect the sheets

For Each ws In wb.Worksheets

    ws.Protect Password:=pass
    
Next

End Sub

CodePudding user response：

In the end all I had to do was change ActiveWorkbook to ThisWorkbook. Simple mistake. I appreciate you all pointing that out. I had just copy and pasted part of that code from another post and didn't really consider that. My final code for protecting all sheets is as follows:

Sub protect_all_sheets()
Dim pass As String
top:
pass = InputBox("Password to protect sheet")
If StrPtr(pass) = 0 Then
MsgBox "Canceled."
Exit Sub
ElseIf pass = vbNullString Then
MsgBox "No password entered. Try again."
Exit Sub
End If
repass = InputBox("Confirm Password")
If StrPtr(unpass) = 0 Then
    MsgBox "No password entered."
ElseIf unpass = vbNullString Then
End If
If Not (pass = repass) Then
MsgBox "Confirmation password is not identical"
GoTo top
End If
For i = 1 To Worksheets.Count
If Worksheets(i).ProtectContents = True Then GoTo Error
Next
For Each s In ThisWorkbook.Worksheets
s.Protect Password:=pass
Next
Exit Sub
Error: MsgBox "I think you may have some sheets that are already protected. Please unprotect all sheets before using this function.", vbCritical, "Error"
End Sub