Temporarily unlock VBAProject password to run code

[Sargad_Strut]

Hi Experts,

I have a number of workbooks (20+) that are being used by multiple users, and the code is password protected in order to hide sensitive information. Currently, I'm working on implementing a UserForm, that will be the same for all workbooks. The form is be imported on open, and that way I only need to make changes once, instead of doing it for all workbooks one by one. Everything went fine until I password protected the VBA code, which won't allow me to import the form. The solution that comes to mind would be to remove the password, import the form, and then password protect the VBAProject again.

Anyone know if this can be done, or have other suggestions on how to solve this problem? Solutions involning Extensibility are not feasable.

Any help is greatly appreciated.

 

[jkpieterse]

If those 20 workbooks share the same VBA I would opt for moving the code to an add-in. Updating an add-in is easier as there is only one file left to update rather than 20. All it takes is replacing the add-in. See: Excel: Building an Excel add-in and Excel: Update An Addin

 

[Sargad_Strut]

If those 20 workbooks share the same VBA I would opt for moving the code to an add-in. Updating an add-in is easier as there is only one file left to update rather than 20. All it takes is replacing the add-in. See: Excel: Building an Excel add-in and Excel: Update An Addin

Hi Jan,

Thank for your quick reply.

The 20 workbooks all contain different code, and also different passwords. The 'centralized' UserForm I am working on will be used as a standardized prompt to enter ODBC-credentials, and I just thought that it would be a nice feature to load it upon open every time, as that could spare me some headache in the future if things change. I was thinking something like this:

Private sub Workbook_open()

'code to remove password
call loadUserForm
'code to set password again

End Sub

Right now I leaning towards just having control over some of the items on the form externally, but will look into the use of add-ins in any case.

Thanks again!
/Tobias

 

[jkpieterse]

You could place the userform (and supporting code) in an add-in and create a reference in your other files to this add-in and then show the form from the other workbooks. But I think this adds to the complexity of your project rather than making it simpler.