Need VBA to unlock other workbooks after password in open workbook is entered

[Lixion]

Hello all,

Complete beginner here with VBAs...
Basically I have various workbooks that capture quality scoring data for individuals, and one workbook that collates the information for a team.
Workbooks are password protected so they cannot access eachothers files.

I need the workbook that collates all the information to have a VBA that reads when the user enters the password for this workbook, the VBA will enter the password prompts for the other workbooks.
At the moment when the workbook is open it is asking to enter multiple passwords to update the workbook...

For example:

Sub Macro1()
ThisWorkbook_BeforeOpen.Unprotect Password:=("Team11")
Workbooks("Tony Quality Report").Unprotect Password:="TM5c"
Workbooks("Peter Quality Report").Unprotect Password:="PD3x"

ThisWorkbook_BeforeClose.Protect Password:=("Team11")

Workbooks("Tony Quality Report").Protect Password:="TM5c"
Workbooks("Peter Quality Report").Protect Password:="PD3x"


End Sub


Unfortunately this is coming up with a Run-time error '424':
Object required

Any help or suggestions out there?

 

[bobsan42]

The workbooks need to be opened to remove the protection. Probably the workbooks you want to unlock are not opened so there is no such workbook object - so you get an error.
An approach can be to open them with code providing the password, extract the information and close them, no unprotection needed.

 

[bobsan42]

I also don't see a lot of sense in the first line of your macro.
First i am almost certain it won't work.
Second, why put a password to remove it.