Hey guys, i'm new here, but not that new to Excel. I need to make a password system for my workbook. How would i do this using Macros. Screenies of example code would be great if you could.

I am also wondering whether i could write a code for a login system aswell. So you must enter a valid Login name and password to enter the workbook (or worksheet) any help would be good!

Thanks.

P.s. I've spent about an hour trying to work it out myself!

Yes, I have done this. Double entry check, first for a valid name, and if that name is valid, then user password which would only provide specific data for that person. I have to leave for about 2 hours, but will post later today.

Ok Thanks mate!

And is it also possible to put in a list or 2 option boxes for admin or guest?

This process is setup using VBA and hidden sheets, one in particular is the Admin sheet which contains all the essential data. Are you familiar with VBA? If you are, you would be able to make adjustments on the Admin Sheet.

BTW, welcome to the Board!!!

Kool, i'm kinda new to VB! Just started using it in college, got about 6 hours of experience. So i'm not that familiar. Especially with using it inside Excel. Is there any example code i could view?

And thanks, i'm hoping this Forum will help me with my studies. Maybe sometime i can contribute my experience aswell!

The attachment has four worksheets.

1. Intro is the basic log-in sheet. When you are using this, all the other worksheets should be hidden (ALT + O + H + H).

2. ShowData will appear if the log-in is correct. The code will only put the appropriate data in column B, for the one who logs in. The table can be filled in using lookup or Index/Match.

3. Base contains all the complete data. You can update this, and the code will automatically adjust, so that when it is pulled into the ShowData worksheet it will be current.

4. Admin This worksheet has all the administrative detail. Column A has the list of people who can login, and Column B has the corresponding password. D2 and E2 contain the user information that is typed it. D3 and E3 are the checks against Columns A and B. The code verifies that these match D2 and E2. H1:L1 lists the managers/supervisors. Data under each name indicates which set of data will populate the ShowData worksheet once login is complete.

Note there are several named ranges, some static, some dynamic. Check those out as well. The VBA uses Select Case for much of the "heavy" work in making this work. If you add another supervisor, say in M1 on Admin (and also in Column A with appropriate password in Column B), then you will need to add another Select Case in the code. You will also have to add a dynamic named range (just use one that is there, give it another name, and adjust the column references in the OFFSET formulas).

=======================

To test it, go to ShowData and hit the END button. This will clear the current data, then hide that worksheet and take you to Intro, the Login worksheet. Type in a name (based on Column A of Admin), then if that passes the first test, then it will ask for the appropriate password. This would be the corresponding password. If that works, then the Intro page is hidden, the ShowData worksheet will appear, and column B will have the appropriate data from the corresponding column in Admin (Columns H through L).

Once you have that figured out, Then hit the END button on ShowData. Also, hide the Base and Admin worksheets. This leaves only the Intro worksheet available. Now try the login.

=============

Hope this is a start in the direction of what you had in mind.

I've created a simple password control and attached the example. It is not as complete as shades example but shows how it can be added for any workbook, with a userform for the password entry.

You would keep the user passwords in the same workbook on a hidden sheet. though in the example it is not hidden for demo purposes.

Of course this is not high security but to only give simple file access control example. Would need some work to add function for your use.

Good Luck


.

Welcome to the Board!

Attached is a workbook that utilizes user names/passwords to allow access for individual users to only the sheets that you specify.

It's pretty straightforward and the code is well documented.

Hope it helps,

Smitty

(Heya Shades!)

Hey, Smitty.

I tried an experiment with both solutions. As you know, I work on both Office 2002 at work and Office 2004 (Mac) at home. I tried your solution, and like it's approach, but it doesn't work with XL2004 on the Mac. It gets hung up on the Dashboard line. On the other hand, my solution worked fine with XL2004 on the Mac.

The reason I mention this is that sometimes people have to do cross-platofrm development. I know from other work with this, that UserForms are very particular in implementation for both platforms. It can be done, but requires, tweaking, testing, tweaking more.

Good point Shades.

VBA for the PC and MAC are entirely diferent animals, despite what Microsoft says.

I don't have enough exposure to MACS to really be able to experiment enough.

Smitty

Nor was it expected (based on the OP). I just threw that in, so that if people were trying to work cross-platform, they would be aware of this bump in the road.