Results 1 to 3 of 3

Thread: Make a created file protected by password

  1. #1
    Junior Member
    Join Date
    Oct 2023
    Posts
    1
    Rep Power
    0

    Lightbulb Make a created file protected by password

    I have searched many forums and have tried many things that I have found but am having no luck. I know that it will likely be something very simple but I cannot figure it out. Would someone please help me adjust the following code, to save the files that are being created as protected files (may be opened and viewed but not edited)? If a message is necessary if someone tries to change values, I would want the message to say only 'changes cannot be made without the appropriate password'. Everything else in my coding works perfectly, would someone please help me? I have cross-posted this to ExcelForum here: https://www.excelforum.com/excel-pro...ml#post5876178

    Code:
       For r = FirstRow To sws.Cells(sws.Rows.Count, "A").End(xlUp).Row
            sws.Rows(r).Copy dfCell
            dName = CStr(sws.Cells(r, "A").Value) & NAME_DELIMITER _
                & CStr(sws.Cells(r, "B").Value)
            If Len(dName) > ndLen Then
                dws.Name = dName
                dFilePath = dFolderPath & dName & ".xlsx"
                Application.DisplayAlerts = False
                    dwb.SaveAs dFilePath, xlOpenXMLWorkbook
                Application.DisplayAlerts = True
                dCount = dCount + 1
            End If

    Thank you so much for your assistance!! I appreciate any help you can offer.
    Last edited by KLBecker; 10-10-2023 at 03:04 AM. Reason: Accidentally submitted too quickly

  2. #2
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,422
    Rep Power
    10
    Hi
    Welcome to ExcelFox
    I have never looked at any sort of file protection before, but I took a quick initial look, and made some notes here: https://www.excelfox.com/forum/showt...ll=1#post23399
    Initially, it seems that to get what you asked for at excelforum and here is fairly simple, at the SaveAs line, which is what Trevor ( TMS ) was suggesting finally in his response at excelforum ( https://www.excelforum.com/excel-pro...ml#post5879059 )

    The TooLongDidn'tRead short answer is change this ,
    dwb.SaveAs dFilePath, xlOpenXMLWorkbook
    , to something like this
    dwb.SaveAs dFilePath, xlOpenXMLWorkbook, , "123456"

    Don’t forget that extra comma , and keep those arguments in the correct order as shown. Of course, the password is 123456 , in that example. You can use something different. I don’t know what the limits is on what and how many characters you can use.
    Alternatively, I personally prefer to use named arguments, because it helps me to remember later what is/ was going on, and the order is then not important. But that is just personal choice. So an alternative would be:
    dwb.SaveAs Filename:=dFilePath, FileFormat:=xlOpenXMLWorkbook, WriteResPassword:="123456"

    So that it's it. (Check out those extra notes I did for more detail ( https://www.excelfox.com/forum/showt...ll=1#post23399 ) )



    I think you mentioned originally that you did not want to make it obvious to people that the file was protected.
    I don’t know if that is possible using the built in things. If it isn’t, we still might be able to think of a fiddle to get something close to what you want.



    One thing to bear in mind is that file protection in Excel and VBA using the built in ways, is generally regarded as something to prevent people accidentally changing things. It is usually very easy to remove the protection or hack the password if you have very basic VBA knowledge and skills. And even if you are a beginner, you can usually find some information on the internet to give you clear instructions on how to get over the protection.


    Alan






    Ref
    https://www.thespreadsheetguru.com/p...rotection-vba/
    https://www.excelforum.com/excel-pro...-password.html
    https://www.excelfox.com/forum/showt...ll=1#post23399
    https://www.excelfox.com/forum/showt...3400#post23400
    https://www.youtube.com/channel/UCnx...RbjOo_MO54oaHA
    http://www.vbaexpress.com/forum/show...-upon-start-up
    Last edited by DocAElstein; 10-10-2023 at 02:20 PM. Reason: Spam links
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  3. #3
    Junior Member
    Join Date
    Mar 2024
    Posts
    1
    Rep Power
    0
    To save the files as protected files and display a message when someone tries to make changes without the appropriate password, you can use the following adjusted code:
    Code:
    For r = FirstRow To sws.Cells(sws.Rows.Count, "A").End(xlUp).Row
        sws.Rows(r).Copy dfCell
        dName = CStr(sws.Cells(r, "A").Value) & NAME_DELIMITER _
            & CStr(sws.Cells(r, "B").Value)
        If Len(dName) > ndLen Then
            dws.Name = dName
            dFilePath = dFolderPath & dName & ".xlsx"
            Application.DisplayAlerts = False
                dwb.SaveAs dFilePath, xlOpenXMLWorkbook
            Application.DisplayAlerts = True
            
            ' Protect the workbook
            Dim wb As Workbook
            Set wb = Workbooks.Open(dFilePath)
            wb.Protect Password:="YourPassword", Structure:=True, Windows:=False
            
            ' Add a message to prevent unauthorized changes
            wb.ShowPivotTableFieldList = False ' This line is optional, it hides the PivotTable Field List
            wb.CustomViews.Add ViewName:="ProtectedView"
            MsgBox "Changes cannot be made without the appropriate password.", vbInformation, "Protected File"
            
            dCount = dCount + 1
        End If
    Next r
    Replace "YourPassword" with the appropriate password you want to use to protect the files. This code will protect the saved files with the specified password and display a message if someone tries to make changes without the appropriate password.

Similar Threads

  1. Replies: 3
    Last Post: 09-14-2022, 02:41 PM
  2. Replies: 2
    Last Post: 05-13-2013, 12:03 AM
  3. Password Holder
    By littleiitin in forum Download Center
    Replies: 3
    Last Post: 01-01-2013, 03:22 PM
  4. Replies: 3
    Last Post: 12-20-2012, 11:10 AM
  5. CheckBox to see password
    By Ingolf in forum Excel Help
    Replies: 9
    Last Post: 08-26-2012, 11:56 PM

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
  •