Consulting

Results 1 to 5 of 5

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

  1. #1

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

    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

  2. #2
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    382
    Location
    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

  3. #3
    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.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,028
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    382
    Location
    @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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •