Protect closed workbook with password

user_7657180 0
Viewed 206

I have searched but didn't find a way to put an external password on closed workbook. Suppose I have closed workbook named "Sample.xlm" .. and I am opening a workbook named "Test.xlm". Is there a code that enables me to put a password on the Sample.xlsm? I mean external password .. I tried the following but got error (method protect of object workbook failed)

Sub Test()
    Dim wb As Workbook

    Set wb = Workbooks.Open(ThisWorkbook.Path & "\Sample.xlsm")
    wb.Protect Password:="123"
    wb.Close True
End Sub

** I was protecting the Sample workbook with Protect and Share Workbook and I have changed to UnShare. This was the problem of the error that appears.

Now the code is working but when opening the Sample workbook after running the code, I found it unprotected. I expect to get it protected.

** It seems I am wrong too at my approach. This would protect the structure of the workbook not to set the external password as the snapshot displays.

Thanks advanced for help

enter image description here

Posted here too https://www.mrexcel.com/board/threads/set-password-to-closed-workbook.1133150/

vba excel
1 Answers

You're looking for Workbook.SaveAs - one of the optional parameters, Password, will do what you're after... but you need to open the workbook first - and as with any code dealing with the file system, we need to handle errors here:

    On Error GoTo CleanFail
    Application.DisplayAlerts = False
    Dim path As String
    path = ThisWorkbook.Path & "\Sample.xlsm"
    With Workbooks.Open(path)
        .SaveAs path, Password:="P@$$w0rd"
        .Close
    End With
CleanExit:
    Application.DisplayAlerts = True
    Exit Sub
CleanFail:
    Debug.Print Err.Description
    Resume CleanExit
user_11885130
Related