Check for protection before continuing (VBA) ?

[BAlGaInTl]

Ok...

I have come up with the following (partial) code that allows a user to protect a workbook. It is part of a userform that I have come up with to simplify things for our quality division.

Private Sub btnQADProtect_Click()
Dim rng As Range, ws As Worksheet, Exist As Boolean
Dim strQADPass As String

Exist = True
    
    'Checks for the defined Range "QADPass"
    On Error Resume Next
    Set rng = Range("QADPass")
    If Err Then Exist = False
        
    If txtQADPass.Text = txtVerifyQADPass.Text Then
        strQADPass = txtQADPass.Text
        
        If Exist = True Then
            If strQADPass = Range("QADPass").Value Then
                For Each ws In Worksheets
                    With Sheets(ws.Name)
                        .Protect Password:=strQADPass, DrawingObjects:=True, Contents:=True, Scenarios:=True
                        .EnableSelection = xlUnlockedCells
                    End With
                Next ws
            Else
                response = MsgBox("Entered password does not match stored password!", 48, "Error!")
                ClearForm
                Exit Sub
            End If
            
        Else
            ActiveWorkbook.Protect Password:=strQADPass
            For Each ws In Worksheets
                With Sheets(ws.Name)
                    .Protect Password:=strQADPass
                End With
            Next ws
        End If
        ActiveWorkbook.Protect Password:=strQADPass
        response = MsgBox("Workbook successfully protected.", 64, "Success!")
        ClearForm
        Exit Sub
    Else
        response = MsgBox("Passwords do not match!", 48, "Error!")
        ClearForm
        Exit Sub
    End If

End Sub

Everything works great except for the fact that a user can enter a password and hit protect and it will say that the workbook is protected even if it has prevously been been protected.

Basically, that gives a false sense that you protected the workbook. It remains protected, but with the previous password.

Is there a way that I can add an If statement to check for protection before continuing. That way, I can return a different message if the sheet has already been protected.

Any help?

 

[Andrew Poulsom]

You can use the ProtectContents property, like this:

If Worksheets("Sheet1").ProtectContents = True Then
    MsgBox "The contents of Sheet1 are protected."
End If

 

[BAlGaInTl]

Ok... that works...

Is there a way the I can use something generic instead of ("Sheet1")?

The names of sheets vary in our workbooks, and if you use "Sheet1" and the name has changed from "Sheet1", it will always return true for ProtectContents for some reason and then exit the sub.

 

[BAlGaInTl]

I think I figured it out myself...

I just used an index number of 1 to check if the first sheet is protected.

If there is a better way that you can think of, let me know.

Thanks.

 

[CraigM]

If you look in the Properties for each sheets, you'll see there are two types of names for a sheet. The one labeled "Name" is the name on the tab that users can change, however the other (at the top) called "(Name)" can only be changed using the VBE.
So you can use this without much fear of anyone changing it, ie:

Sheet1.ProtectContents=True