+ Reply to Thread
Results 1 to 2 of 2

VBA code to password protect all excels files in a folder

Hybrid View

jaincool123 VBA code to password protect... 10-03-2014, 05:08 AM
Olly Re: VBA code to password... 10-03-2014, 07:19 AM
  1. #1
    Registered User
    Join Date
    10-03-2014
    Location
    India
    MS-Off Ver
    2010
    Posts
    1

    VBA code to password protect all excels files in a folder

    Hi Friends,

    I appreciate for your help in advance.

    I have a folder that has around 8 files and i need to password protect these files everyweek and send it to my manager. Please help if i can password protect all these files in one shot.

    Steps to be included in the macros are as follows.

    Prompt you to select a folder
    Prompt you to select PROTECT or UNPROTECT action on the files in that folder
    Prompt you to enter a password to use
    Process all the files in that folder
    Give a count of how many files were affected

    Thank you in advance.

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: VBA code to password protect all excels files in a folder

    Here's code to loop through all (Excel) files in a folder, and set protection on / off, based on the parameters passed to the subroutine:

    Sub ToggleProtection(stPath As String, stPassword As String, bProtect As Boolean)
        Dim stFile As String, wb As Workbook, lPass As Long, lFail As Long
        Application.ScreenUpdating = False
        stFile = Dir(stPath & "\")
        While stFile <> ""
            If InStr(Right(stFile, 4), "xls") > 0 Then
                Set wb = Workbooks.Open(stPath & stFile)
                On Error GoTo ErrHandler
                If bProtect Then
                    wb.Protect (stPassword)
                Else
                    wb.Unprotect (stPassword)
                End If
                wb.Save
                wb.Close (True)
                lPass = lPass + 1
                Debug.Print "PASS: " & stFile & " protection set to " & bProtect
            End If
    NextFile:
            stFile = Dir
        Wend
        Application.ScreenUpdating = True
        MsgBox "Changing protection to " & bProtect & " in" & vbCr & stPath & vbCr & vbCr & lPass & _
            " files succeeded" & vbCr & lFail & " files failed", vbInformation + vbOKOnly, "File Protection"
        Exit Sub
    ErrHandler:
        lFail = lFail + 1
        Debug.Print "FAIL: " & stFile & " caused an error: " & Err.Number & " - " & Err.Description
        wb.Close (False)
        Err.Clear
        Resume NextFile
    End Sub
    The neatest way to collect the parameters need would be via userform - see attached file for an example of how it may work.
    Attached Files Attached Files
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to password protect a complete folder in excel
    By daisys_dad in forum Excel General
    Replies: 0
    Last Post: 08-10-2006, 09:35 AM
  2. [SOLVED] Password protect a folder with several workbooks
    By tbugs in forum Excel General
    Replies: 2
    Last Post: 08-07-2006, 04:10 AM
  3. How can I password protect a folder?
    By NSharp in forum Excel General
    Replies: 2
    Last Post: 05-18-2005, 06:06 PM
  4. [SOLVED] Can you password protect a folder.
    By Shrimp913 in forum Excel General
    Replies: 1
    Last Post: 03-04-2005, 04:06 PM
  5. [SOLVED] Can you password protect a folder?
    By Shrimp913 in forum Excel General
    Replies: 1
    Last Post: 03-04-2005, 02:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1