View Full Version : VBA code for determing if worksheets (OR) cells within a worksheets are protected?

Ram Malladi
04-15-2019, 02:36 PM

I am in urgent need of excel VBA code for determining if:

1) workbook has any worksheets (OR) cells within any work sheet are password protected?
2) if there any VBAProject Modules that are password protected?

My excel files are large in memory/size therefore determining the above details without opening the excel file is really appreciate it.

Any help is really appreciated.


04-15-2019, 07:55 PM
Hi Ram Malladi!
Welcome to vbax forum.
something like below:

Sub aaa()
Dim wb As Workbook, sh As Worksheet
Set wb = Workbooks("Book1.xlsm") 'Input workbook name here or use do ...loop
If wb.VBProject.Protection = vbext_pp_locked Then MsgBox "Workbook " & Chr(34) & wb.Name & Chr(34) & "'s project is protected"
For Each sh In wb.Sheets
If sh.ProtectContents = True Then MsgBox "Worksheet " & sh.Name & " is protected": Exit For
Next sh
End Sub

Ram Malladi
04-16-2019, 02:22 PM
Thank you very much.

After customizing this code to meet my needs, i am getting "Programmatic access cannot be trusted" and run ends. I am thinking that vbext_pp_locked is the problem area.

Is there any fix for this? Your help is appreciated. Also, Is there anyway, i can do all this without opening the file itself.?

Thank you

Bob Phillips
04-16-2019, 02:27 PM
You need to grant access

File>Options>Trust Center>Trust Center Settings>Macro Settings and check the 'Trust access to the VBA project object model' checkbox.

04-16-2019, 08:11 PM
@Ram Malladi

i can do all this without opening the file itself?

Or it may be achieved by binary file analysis, but not within the scope of VBA. and there's no easy way.