VBA code for determing if worksheets (OR) cells within a worksheets are protected?

[Ram Malladi]

Hello,

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.

Thanks
Ram

[大灰狼1976]

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]

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
Ram.

[Bob Phillips]

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.

[大灰狼1976]

@Ram Malladi

i can do all this without opening the file itself?

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


--Okami