Excel VBA - Check if a worksheet is protected WITH A PASSWORD

excel worksheet vba
D. O. picture D. O. · Nov 29, 2016 · Viewed 11.6k times · Source

We can check if a sheet is protected using ProtectContents property. But how check if it is protected with a password?

if ws.ProtectContents then
    ''do something
end if

Answer

JNevill picture JNevill · Nov 29, 2016

I don't believe there is a direct way of doing this by way of a property. Alternatively, though, you could attempt to unprotect the worksheet with a blank password and catch the error should it fail:

Function isSheetProtectedWithPassword(ws As Worksheet) As Boolean
    If ws.ProtectContents Then
        On Error GoTo errorLabel
        ws.Unprotect ""
        ws.Protect
    End If
errorLabel:
    If Err.Number = 1004 Then isSheetProtectedWithPassword = True
End Function

You can call this like:

isSheetProtectedWithPassword(Worksheets("Sheet1"))

And it will return True or False

Related questions

How to add a named sheet at the end of all Excel sheets?

I am trying to add an Excel sheet named "Temp" at the end of all existing sheets, but this code is not working: Private Sub CreateSheet() Dim ws As Worksheet ws.Name = "Tempo" Set ws = Sheets.Add(After:=Sheets(Sheets.…

Read More
Excel VBA For Each Worksheet Loop

I am working on code to basically go through each sheet in my Workbook, and then update column widths. Below is the code I wrote; I don't receive any errors, but it also doesn't actually do anything. Any help is …

Read More
Is there a macro to conditionally copy rows to another worksheet?

Is there a macro or a way to conditionally copy rows from one worksheet to another in Excel 2003? I'm pulling a list of data from SharePoint via a web query into a blank worksheet in Excel, and then I want …

Read More