Using a VBA script to validate if an excel workbook is protected and read what password was applied to it while doing so [closed]

Question

I am writing for an application that validates if users perform tasks successfully in various Microsoft applications. One of these skills is to protect a file with a password. Is there some property or method I can use to verify if the current file is protected and to verify what password was used to encrypt it?

Answer 1

As @findwindow mentioned, we wont be able to see the password of a protected sheet or workbook. To check if a workbook/worksheet is protected using VBA, you can do this. Code is borrowed from here

Worksheet protected check

Sub IsWorksheetProtected()
With ActiveSheet
  If .ProtectContents Or .ProtectDrawingObjects Or .ProtectScenarios Then
    MsgBox "This worksheet is password protected"
  Else
    MsgBox "This Worksheet is not password protected"
  End If
End With

End Sub

Worksbook protected check

Sub IsWorkbookProtected()
With ActiveWorkbook
  If .ProtectWindows Or .ProtectStructure Then
    MsgBox "This workbook is password protected"
  Else
    MsgBox "This workbook is not password protected"
  End If
End With

End Sub

UPDATE FOR WORKBOOK PWD You are right, as we have the workbook open, looks like it will not detect the details. You can use .HasPassword to check if the workbook is password protected. Note that it is possible to lock the workbook without password, but it is counterintuitive to protect a worksheet without a password. But, as long as a password is set, you can use this.

Just replace

If .ProtectWindows Or .ProtectStructure Then

with

If .HasPassword Then