Protected Workbook with links too complicated to open

**bjohnsonac** · 03-04-2009, 11:35 AM

I have 7 Individual sales reports that all link into a summary report and vice versa - the summary links the quotas into the individual sales reports. They are all stored on our server, but have to be protected so only myself, the manager and the particular salesperson who uses it can open it. The sheets also have to be protected so the user doesn't screw up any of my formulas. I know about protecting the sheets, but when I protect the workbooks so you have to have a password to open it, each time you open the summary report you have to enter all the passwords for each report that links into it. Is there a way around this so that the reports automatically link without having to input passwords?

**Richard Buttrey** · 03-04-2009, 12:07 PM

Hi, and welcome to the forum

It sounds like you need to use a VBA procedure to open the workbooks. If you do this you can include the password in the code (or prompt for it once if you prefer), and have the macro open the workbooks.

HTH

**bjohnsonac** · 03-04-2009, 12:11 PM

I thought about that, but can you do that with multiple passwords? i.e. the summary report has 7 different sheets that link into it (and this number could grow in the future if we add new sales people) that would all need to be password protected. Can you help me with how to do this? Thanks!

**bjohnsonac** · 03-04-2009, 12:35 PM

I thought about that, but can you do that with multiple passwords? i.e. the summary report has 7 different sheets that link into it (and this number could grow in the future if we add new sales people) that would all need to be password protected. Can you help me with how to do this? Thanks!

**Richard Buttrey** · 03-04-2009, 12:52 PM

Hi,

One way, provided you have a reference somewhere in the workbook file name to the sales person's name or staff number etc., would be to hold a table of all the names (or indeed the complete filename for each person), and associated passwords in the summary workbook.

Then in VBA you would use the relevant string from the filename you are trying to open to set a password variable, and use this variable in the
Password = variable
instruction

Rgds