Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Macro to open Password protect worksheets

0

Hi,

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.

Thanks, 

Iain

Answer
Discuss

Answers

0
Selected Answer

Get your macro for opening workbooks working on regular workbooks and then add the below password field addition to the workbooks.open 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.

Discuss

Discussion

Ok - here goes a completely stupid question.

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

thanks
Iain
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: 1989) 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