Protected Workbooks and VBA to Unlock Them

Hi there,

See if the following code does what you need:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your requirements.


Regarding:

Separately there needs to be a code that will run that in reverse. Search 'Passwords' and replace the password on the protected workbook. This is so other macros can run through the now unlocked workbooks and then once finished, lock them again.

you say that:

column B contains passwords to open the workbook

so I am assuming that the structure of the workbooks is not protected. If this is the case (i.e. not protected) then it is not necessary to protect the workbooks when your routines have finished executing - when the workbooks are closed they will still need a password to be entered before they can be re-opened.


Hope this helps - please let me know how you get on.

Regards,

Greg M

Thank you for the help on this. I am extremely grateful. It is working better than my last code but it seems to have an issue locating the workbooks. It brings up the error you placed 'cannot locate XXXX workbook' on all 9 workbooks that I have. I went through and made sure that all my workbook names match the 'Password' sheet in column A, and I can still manually unlock them with the passwords in column B. My filepath is C:\Users\XXXXXXX\Documents\OneDrive. I cant really think of a reason it cannot locate them based on how the code is written.

Again, thank you for all of the assistance and I hope you are having a wonderful day.

Hi again,

Thanks for your feedback.

Is there any chance that you could post your "Passwords" worksheet here? I'm more interested in the format of the workbook names than the passwords.

Regards,

Greg M

Hello,

Thank you for responding. While I cant include the actual copy of the workbooks (they contain company info all through it), I made a mock up of what I am working with. The passwords for the other workbooks are Test1 and Test2. The formula will need to go through more than just two workbooks but for testing I just included those. The sheets are also locked as they are in the original copy. Please let me know if you need anything else and I will do my best to supply it.
Thank you again for the help. I am very grateful.

Hi again,

Ok, I suspected what the problem might be, so that's why I asked to see the format of the workbook names as listed on the "Passwords" worksheet.

You must include the file extension as part of the workbook name, i.e. Column A should contain values such as "Workbook1.xlsx", "Workbook2.xlsx" etc.

I think that the problem will be solved if you amend the workbook names in this way.


NOTE: The "Passwords" worksheet of the "Main Data Workbook.xlsm" workbook shows a workbook name and password on row one, but your original post suggests that the list of workbooks and passwords begins on row two.


Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

Thank you so much for the reply. That was exactly the issue. I do have one last question though. Is it possible to have these unlock without actually opening the workbooks?

Hi again,

Regarding:

Please Login or Register  to view this content.

I don't think this is possible - to remove an opening-password you need to open the workbook (using the password) and then use the SaveAs method to save the workbook without an opening-password.

Regards,

Greg M

Thank you so much for the assistance. This works exactly like I need it to. You are a wonderful smart intelligent person who I aspire to be

Hi again,

Many thanks for your feedback, for the Reputation increase (much appreciated), and also for your kind words which have me blushing furiously!

You're welcome - glad I was able to help.

Best regards,

Greg M

Hi again,

Ok - now we need to be a bit more precise regarding what we're talking about.

1) Workbook protection against opening (this is what I've been dealing with up to now);

2) Workbook protection/locking against Structure/Windows modification, and

3) Worksheet protection/locking against various types of change.

If I've understood your latest post correctly, you have implemented all three types of protection in your workbooks. Can I assume that you use the same password for each type of protection (not necessarily the same password for each workbook)?

So far, we've successfully opened workbooks which are password-protected against opening, but now you want to unprotect those opened workbooks, unprotect their worksheets, make changes to them, and then reprotect them after making those changes, is that correct?

Are all worksheets protected, or are some of them unprotected? (All protected would be easier to handle.)

Are opened workbooks closed by your code or are they manually closed by the Users?

Regards,

Greg M

I really cannot express how much I appreciate the assistance in this. Seriously, this is a such a big help. All workbooks and sheets are locked. You've already solved the workbook opening with different passwords issue, but the code also needs to unlock the sheets in those workbooks to make an adjustment (copying/inserting row). The sheets in those workbooks are all protected with the same password "Pass1", with the only option being to modify unlocked cells. Essentially these sheets are for viewing only besides a couple unlocked cells here and there.


Hopefully I am detailing it enough but if not... Button click in the master workbook should: 1. Unlock workbooks with column A and B from its 'Passwords' sheet 2. Unlock all sheets in the now unlocked workbook (same password for all sheets 'Pass1') 3. copy/insert range as value (copies L4:W4 and inserts values to L5:W5 on all sheets) 4. Save 5. lock the sheets 6. Lock the workbooks 7. Close all workbooks except 'Master' 8. Make sure all workbooks processed.

On the flip side, the user hits 2 buttons in the 'Master' workbook... First Button: Updates a pivot table in 'Master' with new data. Second Button (this is the macro we're working on): A) opens locked workbooks/sheets in same folder B) goes through all sheets, finds a range that is linked to pivot table and inserts its values a row below C)Saves Workbooks D)Locks all sheets/workbooks E) Closes all workbooks except 'Master' F) Confirms all workbooks processed.

Hopefully that helps explain it a bit more. This is essentially for a formula that will be ran every morning and generate metric numbers for individuals from the previous days work. Their numbers are all linked from the 'Master' Workbook pivot table (linked to L4:W4 on all workbooks from the 'Master' pivot table). This formula will simply move that linked data down one to allow for the next days numbers. The 9 workbooks are the teams and the sheets are the individuals in those teams.

Please let me know if I can provide any other information and truly and deeply... Thank You.

Hi again,

Many thanks indeed for that careful explanation of your requirements.

See if the following code helps to progress matters further. I have tried to structure the code so that the various stages are isolated from each other - this should help if you need to change the code (Sub ModifyWorkbook) which actually modifies the workbooks - i.e. you can make the changes there without needing to bother about the code which opens/unprotects/re-protects/closes the workbooks.

Please Login or Register  to view this content.

The highlighted values may be altered to suit your requirements.


Hope this helps - as always, please let me know how you get on.

Also, please feel free to ask for any further information or explanation you need.

Regards,

Greg M

Thank you. This looks complicated and looks like you spent some time so I wish I could give you double reputation or something. Everything is working like it should but the insert function seems to have changed (probably a simple fix but ive never seen an insert code written like this one). It is this part of the 'ModifyWorkbook' Private sub:

Please Login or Register  to view this content.

It is pasting over the data that is already in range L5:W5 but it should just move the data already in L5:W5 down one row. Sorry if didn't properly explain, but that should insert the range from L4:W4 to L5:W5, moving the data that was in L5:W5 to the next row down (copying L4:W4 and inserting it a row below instead of appearing over the data already in L5:W5... creating a long list in L:W eventually). Essentially we do not want to lose the data in L:W when this macro is ran and instead just keep pushing the data further down.

Actually. I got that issue corrected. I just added

Please Login or Register  to view this content.

In place of the pasting script that was included. After changing the reference it seems to be working flawlessly. I know ive said it multiple times already but I cannot express how much I appreciate your help. I would have never been able to come up with a code that does all this and works so quickly/error free. I dont know what you do as your profession but you should definitely consider doing this as a career.

Hi again,

Ok on moving any existing values "downwards" before copying the new values - I did think it was a bit strange to just overwrite the existing values, but maybe I hadn't read your description carefully enough!

The following version of the "ModifyWorkbook" routine should do what you need:

Please Login or Register  to view this content.

I think you may now appreciate the approach of structuring the code in dedicated "chunks" - i.e. only a single, easily-identifiable routine needed to be modified, instead of digging around inside an "overall" routine to identify which portions needed changes.

Also, the "copy and paste" method I used (assigning source range values to an array and then assigning those array values to a target range) is fairly efficient, and has the added slight advantage of not leaving the "pasted" area highlighted after the operation.


Hope this helps - as always, please let me know how you get on.

Regards,

Greg M

Hi again,

Regarding:

I dont know what you do as your profession but you should definitely consider doing this as a career.

I'm a retired electrical (HV) engineer, who used (and more importantly, enjoyed using!) Excel for many aspects of my work.

I'm delighted to have helped you out - I get quite a buzz out of saving people time when they use their computers more efficiently - after all, computers are supposed to make life easier for us!

Regards,

Greg M

Good Day Greg. The code above works wonderfully but I had a quick question. I was informed to implement a new feature in our workbook (I got the other feature working) but it requires this code to have a very small change that I am having issues changing on my own. Essentially when the worksheets are locked again, it needs to leave the option 'Format Columns' checked. This is because the user has an option now that lets them change the column size. All other options can stay locked.

I am pretty sure this part of the code

Please Login or Register  to view this content.

is what needs altered but I am unsure how to have VBA differentiate what options are locked and which are unlocked. They should only be able to 'select unlocked cells' and 'format columns'. If its a big change then I can definitely just create a new post but I think it could be a relatively simple change.

Thank you for all of your help. Seriously. You have saved my company so much time already its almost unreal.