Macro to open Password protect worksheets



New here - and a huge Macro novice (so spologises now for my lack of understanding!), however i have a master workbook that reads from 20+ other workbooks.

At present 2 of these workbooks are password protected (likely to increase once our staff input more data) and i wanted to know if there is a coding that would allow me not to have to input the passwords every time i open up the spreadsheet?!

I have all the passwords so not looking to 'crack' anything.





Selected Answer

Get your macro for opening workbooks working on regular workbooks and then add the below password field addition to the method.

Use something liket this to open the workbook:

Dim wb1 as Workbook
Set wb2 = Workbooks.Open(Filename:="C:\File\Path\file.xlsx", Password:="yourpassword")

This should get you started!

Here is a link to the Microsoft website page on using Workbooks.Open: reference.

I'm aware that that link might be a little scary at first, so come back if you have any more questions!

Unprotect and Protect Worksheet Using VBA

Sub DoStuff()

' Un-Protect Worksheet
Worksheets("Sheet1").Unprotect Password:="YourPassword"

' Do Stuff

' Re-Protect Worksheet
Worksheets("Sheet1").Protect Password:="YourPassword"

End Sub

Obviously, your password is kind of exposed using this so, if you want to protect it more, you can also password protect your vba code.



Ok - here goes a completely stupid question.

Is the macro only applied to the master spreadsheet? As its still asking me for the password.

Iain McBride (rep: 2) Sep 27, '18 at 8:57 am
Not stupid! Sorry for the delay, been too busy lately :/. So, you can password protect workbooks AND also, separately, worksheets. Check my updated answer for help on password protected worksheets.
don (rep: 1705) Oct 9, '18 at 3:50 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login