Not your usual password question!

[Andy]

Hello Everyone.

I need some help. I will explain the situation first and then the question.

I have a folder. In this folder i have 14 Excel 2003 files. One is the
controler called Main.XLS. So this automatically opens when windows loads. To
open it fully a password is needed. Enter the password and you can access,
via links, to the other 13 Excel files and back again to the Main file.

The question....Is it possible to password protect the other 13 files so
that if they are opened directly from the folder they are in, you have to
enter a password, however, if you use the Main xls file, you dont need to
input a password as access has already been granted. Because of the system i
have created, i need quick access once the password is entered into the Main
xls file, without being asked for a password every time i go into one of the
other 13 files. But still have security so that if they are opened indivually
users are asked for a password! i hope this makes sence. I have had to break
the Main file down. I have everything in one work book with over 400 sheets
and the system was unbelievably slow.

Any help or VBA code would be great to help me solve this.

Regards

 

[Bernie Deitrick]

Andy,

You could use macros stored in Main.xls to open the files - maybe a sheet
with buttons, or a commandbar. How you do that depends on what the
passwords are - probably the easiest is to have a hidden sheet with a lookup
table that you can read from the macros.

HTH,
Bernie
MS Excel MVP

 

[Bill Sharpe]

Hello Everyone.

I need some help. I will explain the situation first and then the question.

I have a folder. In this folder i have 14 Excel 2003 files. One is the
controler called Main.XLS. So this automatically opens when windows loads. To
open it fully a password is needed. Enter the password and you can access,
via links, to the other 13 Excel files and back again to the Main file.

The question....Is it possible to password protect the other 13 files so
that if they are opened directly from the folder they are in, you have to
enter a password, however, if you use the Main xls file, you dont need to
input a password as access has already been granted. Because of the system i
have created, i need quick access once the password is entered into the Main
xls file, without being asked for a password every time i go into one of the
other 13 files. But still have security so that if they are opened indivually
users are asked for a password! i hope this makes sence. I have had to break
the Main file down. I have everything in one work book with over 400 sheets
and the system was unbelievably slow.

Any help or VBA code would be great to help me solve this.

Regards

Be aware that Excel's password protection is pretty weak and can be
readily circumvented.

Bill

 

[Pete_UK]

Perhaps you can use the techniques described here:

http://www.cpearson.com/excel/EnableMacros.aspx

Although this article is about trying to force macros to be enabled,
the concept of hiding the main sheets in the 13 files that you want to
protect and having one sheet that says something like "Do not open
this file directly" could be applied in your case.

Hope this helps.

Pete

 

[Andy]

Thanks Bill. Appreciate your input. I have disabled on open the shortcut for
vba, and enabled a password to access the code, and a password to open the
file in the firstplace, which is located in a password protected folder.
Hopefully this will be enough to stop anyone trying to access it.
Thanks again!

 

[Andy]

Thanks Bernie, Dont surpose you have some coding that may assist with this?
One the Main page it auto opens a command bar that allows you to select the
page you want. once a button is clicked it opens the file it is being asked
for. Is it possible to have a macro copy and paste a password from a hidden
sheet into the open password box which asked for a password when a file is
opened. For example, i click on button 1, the program then asks for the
password to open and the password for read only and have the password pasted
in before it then deletes the password so it cant be pasted into a cell and
view.

Regards,

Andrew

 

[Bernie Deitrick]

Andy,

Each of your workbook opening macros should have code like

Workbooks.Open Filename:= _
"C:\Documents and Settings\Excel\Filename.xls"

Change that code to this if you know the password:

Workbooks.Open Filename:= _
"C:\Documents and Settings\Excel\Filename.xls", Password:="test"

Or, if you have a lookup table, something like

Dim myPW As String

myPW = Application.Vlookup("Filename",Range("PWLookup"),2,False)
Workbooks.Open Filename:= _
"C:\Documents and Settings\Excel\Filename.xls", Password:=myPW

HTH,
Bernie
MS Excel MVP