Distributing a Macro to Update Multiple Workbooks

[dccarm]

We have a spreadsheet that is used by many people throughout our organisation. It is password protected. We want to make several changes to the sheet, nothing too complicated just a few column heading changes and one or two simple formulas.

My problem is, how do I go about distributing this macro to the numerous users of the workbook? I can easily build one to create the changes required, but need to send it out so that each user can somehow update their own copies of the workbook. The users do not have access to the passwords, so this needs to remain hidden from them.

 

[John Davis]

We have a spreadsheet that is used by many people throughout our organisation. It is password protected. We want to make several changes to the sheet, nothing too complicated just a few column heading changes and one or two simple formulas.

My problem is, how do I go about distributing this macro to the numerous users of the workbook? I can easily build one to create the changes required, but need to send it out so that each user can somehow update their own copies of the workbook. The users do not have access to the passwords, so this needs to remain hidden from them.

Does this help or give you some ideas?

http://www.dotxls.com/excel-securit...n/how-to-run-a-vba-macro-on-a-protected-sheet

Then you can set a separate password for the project Tools>Macro>Visual Basic Editor>Tools>VBAProject Properties>Protection

 

[dccarm]

Not entirely John, but thanks. It helps with the password protection part, but I had a rough idea of how to do that already.

My issue is more with how I get a macro to the users to update spreadsheets they are already using. Since I posted, I've been thinking along the lines of;

1. Create the macro in a blank spreadsheet
2. Lock the VBA code so no one can access the password
3. Issue it to the users, with a list of instructions on how to update their sheets. This would involve

Opening the spreadsheet they want to update.
Opening the new spreadsheet
How to run the macro in the original spreadsheet, either by going through Tools, or perhaps by me giving the macro a shortcut key.

Are there any flaws / pitfalls with doing this? I want to keep it as simple as possible.

 

[John Davis]

Not entirely John, but thanks. It helps with the password protection part, but I had a rough idea of how to do that already.

My issue is more with how I get a macro to the users to update spreadsheets they are already using. Since I posted, I've been thinking along the lines of;

1. Create the macro in a blank spreadsheet
2. Lock the VBA code so no one can access the password
3. Issue it to the users, with a list of instructions on how to update their sheets. This would involve

Opening the spreadsheet they want to update.
Opening the new spreadsheet
How to run the macro in the original spreadsheet, either by going through Tools, or perhaps by me giving the macro a shortcut key.

Are there any flaws / pitfalls with doing this? I want to keep it as simple as possible.

That's what I thought. You said you'd have no problem creating the macro - issue 1. Then I suggested placing a password in the macro so that average users would not have access to the code (not full proof BTW), but it does add another step of security - issue 2. When you create the macro just have it to operate on the Activesheet. I'm using excel 2003, therefore I'd just include in the instructions on adding a button to the standard toolbar and calling it from there. You probably have a newer version of excel, but I'm sure there is something similar available. I agree with your ideas, and don't see any pitfalls except if one or more of your users are programmers and know how to bypass the VB Project password code.