Password Protect / Unprotect Multiple Workbooks?

[manny77]

Hi there,

My first time posting so apologies if I don't have the format down pat yet.

I have about 50 workbooks, each with a unique password that I know.

Once a month I have to update a few fields in each, which means I have to open the file, enter the password, make the changes, save and close.

Is there a way I could automate the process of unprotecting the files at the start? For example, could I do something to remove the protection for the given files, and then at a later point add protection back to all files?

I use Excel 2007

Thanks in advance,
Manny

 

[Jameo]

Hi manny,

Try looking at the thread:

http://www.mrexcel.com/forum/showthread.php?t=546155&highlight=protect+multiple+worksheets

Cheers

 

[manny77]

Hi there,

Thanks for this, but the example given has all of the workbooks to be opened having the same password, which is not the case in my situation.

To clarify, i need to
1) open each file with a unique password
2) remove workbook protection and save and close so the next person to use it doesn't need to enter a password
and then at a later point-
3) open the files, protect them each with their unique password, save and close.

The file names and passwords won't change often, so if you can give the code to complete these steps for an individual file- let's call it example.xls with password "examplepw" i can replicate for each individual file.

thanks,
Manny

 

[jasonb75]

How about password based on windows login

Private Sub Workbook_Open()
    If Environ("username") = "manny1" Then
        ActiveWorkbook.Sheets("Sheet1").Unprotect Password:="your password"
    End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ActiveWorkbook.Sheets("Sheet1").Protect Password:="your password"
End Sub

Enter the code into each workbook with the relevant password, etc. then hide the code module from viewing.

This should be used with caution, if you share a windows login with other users this would allow them access without the passwords, users on other logins would have to enter the password manually as normal.

I haven't tested this method extensively, but it appears to be working as it should.

 

[manny77]

Hi Jason,

Your suggestion could do the trick- I was wondering if it could be modified to work for my login and 1 other person's login?

Let's call them Anna, for example.

I'm very new to code so forgive me if this is obvious.

Thanks,
Manny

 

[jasonb75]

Private Sub Workbook_Open()
    If Environ("username") = "manny1" Or Environ("username") = "Anna" Then
        ActiveWorkbook.Sheets("Sheet1").Unprotect Password:="your password"
    End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ActiveWorkbook.Sheets("Sheet1").Protect Password:="your password"
End Sub

Should do the trick.

As you're new to this, something else that might not be instantly obvious is the correct place to put the code.

When you look at the project tree in the code editor, you should see a module called "ThisWorkbook" which is where the code should be, if you put it anywhere else then it won't run when you open the file.

Once you're happy with it, right click the module in the project tree, then click on "VBAProject Properties..." then go to the "protection" tab.

Check the box and enter a password, note this can be any password, just remember you will need it should you need to edit the code at any time, it's purpose is to hide the code so other users can't view it.

It might appear as if it didn't work to begin with, the code remains visible for the current session, once you save and close the workbook it will take effect and be hidden when you re-open it.

Hopefully that will get you working.

 

[manny77]

Thanks so much, I will try this out when I am in the office tomorrow.
Manny

 

[manny77]

Hi there,

I'm not having any luck- am I wrong to think that the code won't run until the workbook is open, and to open the workbook you need the password?

Anyway- it still asks for password to open file.

Manny

 

[kpark91]

No, the code should run with or without the password unless it has to do with your macro security settings.

Although it is not recommended, you can go to Trust Center of excel from Excel Options and then change the macro security setting to running all the macro.

Then I think the workbook should be unprotected automatically when you open and protected before you close.

 

[RoryA]

If the password is to open the file, then yes you need to supply it in the code when you open the workbook. (it would be fairly useless otherwise)