Excel File Users

y2k · Mar 6th, 2002, 03:57 AM

Ok... I think I'm really looking for too much here! Is it possible to create a table (either excell or access) of users and passwords so that when they open a file they're prompted for this? I can't just put a password on the file as different people need to see different things etc. Any ideas? I'm sure I'm asking for the impossible here!

Dave Hawley · Mar 6th, 2002, 04:36 AM

Hi Yk2

Not impossible at all! You will need to use and Excel UserForm with 2 textboxes and a CommandButton (Enter button). The usernames and passwords would be on a Sheet who's Visible Property is set to xlveryHidden. The UserForm would need to appear via the Workbook_Open Event and then the user would type in a UserName and password. Once the commandButton is clicked the code would use the Find Method to find an exact match to the Username and also check that the cell on the same row but different column matches. It can even be set up so that they are ditched out if type the wrong password 3 times. If they get it right then the appropriate sheets would be made visible for them.

In a nutshell it can be done but would require quite a bit of coding and the coding would have to be quite precise and efficient.

y2k · Mar 6th, 2002, 06:58 AM

Excellent! I'll have to have a go at that. Is there any way to hide a line of code which contains a password? Eg ActiveWorkbook.Protect Password:="password"....

Thanks
Martin

Mudface · Mar 6th, 2002, 07:28 AM

You can protect your VBA project with a password by clicking on Tools- VBA Project Properties and 'locking the project for viewing'.

y2k · Mar 6th, 2002, 07:37 AM

That's not working for me... what am I doing wrong? I can slelect the macro and select "step into" or I can select VB editor from tools>macros menu. Any ideas?

Mudface · Mar 6th, 2002, 07:46 AM

You'll need to save and close the file first. When you re-open it, it should ask you for a password if you try and open it via the editor and the Step Into button will be greyed out from the Run Macro dialog box.

y2k · Mar 6th, 2002, 07:53 AM

Sorry!!!! You're right of course!

y2k · Mar 19th, 2002, 03:41 AM

I never had a chance to try out the above, but now I'm in a situation where I may have to. I have 90 files which are updated by 3 different people in a HR department. Each file is specific working week, and each employee has a sheet within each file. They want each employee to be able to view just their own sheets for each of the files. But the people who update them need to have full access. If necessary (and I think it will be) rather than having a file for each working week and a sheet for each employee, we could have a file for each employee and a sheet for each week. But Can I create a seperate file listing all the users and passwords? Also, there are certain rows that the employees must not see, but I can manage that by just hiding them after the user has been identified. Can anybody help me out on this one at all? Thanks for all your help so far everybody.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 6th, 2002, 03:57 AM	#1
y2k Board Regular Join Date: Feb 2002 Location: Ireland Posts: 133	Ok... I think I'm really looking for too much here! Is it possible to create a table (either excell or access) of users and passwords so that when they open a file they're prompted for this? I can't just put a password on the file as different people need to see different things etc. Any ideas? I'm sure I'm asking for the impossible here!

Mar 6th, 2002, 04:36 AM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Yk2 Not impossible at all! You will need to use and Excel UserForm with 2 textboxes and a CommandButton (Enter button). The usernames and passwords would be on a Sheet who's Visible Property is set to xlveryHidden. The UserForm would need to appear via the Workbook_Open Event and then the user would type in a UserName and password. Once the commandButton is clicked the code would use the Find Method to find an exact match to the Username and also check that the cell on the same row but different column matches. It can even be set up so that they are ditched out if type the wrong password 3 times. If they get it right then the appropriate sheets would be made visible for them. In a nutshell it can be done but would require quite a bit of coding and the coding would have to be quite precise and efficient.

Mar 6th, 2002, 06:58 AM	#3
y2k Board Regular Join Date: Feb 2002 Location: Ireland Posts: 133	Excellent! I'll have to have a go at that. Is there any way to hide a line of code which contains a password? Eg ActiveWorkbook.Protect Password:="password".... Thanks Martin

Mar 6th, 2002, 07:28 AM	#4
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	You can protect your VBA project with a password by clicking on Tools- VBA Project Properties and 'locking the project for viewing'.

Mar 6th, 2002, 07:37 AM	#5
y2k Board Regular Join Date: Feb 2002 Location: Ireland Posts: 133	That's not working for me... what am I doing wrong? I can slelect the macro and select "step into" or I can select VB editor from tools>macros menu. Any ideas?

Mar 6th, 2002, 07:46 AM	#6
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	You'll need to save and close the file first. When you re-open it, it should ask you for a password if you try and open it via the editor and the Step Into button will be greyed out from the Run Macro dialog box.

Mar 6th, 2002, 07:53 AM	#7
y2k Board Regular Join Date: Feb 2002 Location: Ireland Posts: 133	Sorry!!!! You're right of course!

Mar 19th, 2002, 03:41 AM	#8
y2k Board Regular Join Date: Feb 2002 Location: Ireland Posts: 133	I never had a chance to try out the above, but now I'm in a situation where I may have to. I have 90 files which are updated by 3 different people in a HR department. Each file is specific working week, and each employee has a sheet within each file. They want each employee to be able to view just their own sheets for each of the files. But the people who update them need to have full access. If necessary (and I think it will be) rather than having a file for each working week and a sheet for each employee, we could have a file for each employee and a sheet for each week. But Can I create a seperate file listing all the users and passwords? Also, there are certain rows that the employees must not see, but I can manage that by just hiding them after the user has been identified. Can anybody help me out on this one at all? Thanks for all your help so far everybody.