Check for protection before continuing (VBA) ?

BAlGaInTl

Well-known Member
Joined
May 7, 2003
Messages
1,082
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.

Code:
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?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can use the ProtectContents property, like this:

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

BAlGaInTl

Well-known Member
Joined
May 7, 2003
Messages
1,082
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

Well-known Member
Joined
May 7, 2003
Messages
1,082
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

Active Member
Joined
Feb 27, 2003
Messages
320
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,101
Messages
5,768,100
Members
425,453
Latest member
bince

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top