Excel File Users

[y2k]

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]

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]

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]

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

 

[y2k]

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]

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]

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

 

[y2k]

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.