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

Lixion

New Member
Joined
Mar 23, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,148,282
Messages
5,745,836
Members
423,981
Latest member
ph1l

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