Add password to open excel file via VBA

Harshil Mehta

Board Regular
Joined
May 14, 2020
Messages
64
Office Version
  1. 2013
Platform
  1. Windows
I am looking for a code which adds PASSWORD TO OPEN FILE in the current workbook before closing it.

This will help if the user has removed the protection by applying the password automatically before closing it.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

kennypete

Active Member
Joined
Apr 19, 2008
Messages
260
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Create BeforeClose code on the Workbook object, i.e. right click ThisWorkbook, View Code
1618219516023.png

Then add the code:
VBA Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.DisplayAlerts = False
    ThisWorkbook.SaveAs Password:="abcd"
    Application.DisplayAlerts = True
End Sub
And replace the "abcd" with whatever password you are wanting the Workbook to always have.
 

Harshil Mehta

Board Regular
Joined
May 14, 2020
Messages
64
Office Version
  1. 2013
Platform
  1. Windows
Create BeforeClose code on the Workbook object, i.e. right click ThisWorkbook, View Code
View attachment 36476
Then add the code:
VBA Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.DisplayAlerts = False
    ThisWorkbook.SaveAs Password:="abcd"
    Application.DisplayAlerts = True
End Sub
And replace the "abcd" with whatever password you are wanting the Workbook to always have.
Can this be saved in the same path?
 

Harshil Mehta

Board Regular
Joined
May 14, 2020
Messages
64
Office Version
  1. 2013
Platform
  1. Windows
Sorry, can what be saved in the same path?
I am looking for a code which saves the file with same name & path along with password to open

Basically, replaces the files without any prompt.


VBA Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim a110w As Variant
Dim path As String


a110w = "123" 'P
path = Application.ThisWorkbook.path



Application.DisplayAlerts = False

With ThisWorkbook
   .SaveAs Filename:=path, FileFormat:=50, Password:=a110w
   
End With
     
      
Application.DisplayAlerts = True

      

End Sub
 

kennypete

Active Member
Joined
Apr 19, 2008
Messages
260
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I am looking for a code which saves the file with same name & path along with password to open

Basically, replaces the files without any prompt.


VBA Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim a110w As Variant
Dim path As String


a110w = "123" 'P
path = Application.ThisWorkbook.path



Application.DisplayAlerts = False

With ThisWorkbook
   .SaveAs Filename:=path, FileFormat:=50, Password:=a110w
 
End With
   
    
Application.DisplayAlerts = True

    

End Sub
Did you test it? The code I provided will save to the same path (where the file is already) when you close the file, so it looks like your modified code should too?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,691
Messages
5,637,846
Members
416,986
Latest member
zmartee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top