VBA help password check then open form or sheet depending on option button selection

I could really use some help with a project that seems to be growing rapidly beyond my abilities. I have a userform that is serving as a portal to other items in the workbook. First problem is getting user passwords working. I will have 1 master password and 3 user passwords. Of course the master will be allowed into everything. The users need to be able to access only their unit's specific forms. I would like the macro to take the selections of the two option boxes on the userform and do the following:

Lets say I have a logsheet named Unit A, when the logsheet option is selected in one option group and Unit A in the other, if the password validates for that unit, it will open the unit specific logsheet. If the password is incorrect or not matching with the unit designation, it will not open the logsheet.

I have been looking around for the answer, but I haven't found a cookie cutter type solution I can get to work with my limited skill and ability in VBA programming. The latest thing I came across that seemed promising was the use of case statements. However, I am not sure how this would work with multiple selections and a password.

Attached is a sample form I have been working on.

Thank you for any help or direction you may provide.

Patrick

Hey Patrick,

To cater your needs, I have added an extra sheet "LoginDetails" which stores the details of the users. In which we have a "Table" (also known as Lists in Excel 2003) with three columns namely UserName, Password and UserType. UserName and Password is used for login verifications and UserType can define the access level for the users. Following are the workings in your file:

1. The sheet is basically very hidden by nature, which means you cannot find it even if you right click and try to Unhide a sheet. This needs to be handled only through VBA. Code to hide and unhide the sheet is as follows :

Please Login or Register  to view this content.

The second line of code is available in the macro named "ShowLoginDetails", so if you wish to view the sheet, you can find that in macros list and just run it. Macro is written inside ThisWorkbook.

2. We have then added a dynamic named range ("Users") to make sure you're provided with a list of only available users. This provides us the flexibility to reflect later added users. Under "Formula > Name Manager", use following formula to get that list:

Please Login or Register  to view this content.

Note: It only allows 98 users to be added. If you'd like to add more, just change $A$2:$A$<number> which you'd want to.

3. Now its all about the operation under Submit button. Its code goes as follows:

Please Login or Register  to view this content.

Attached is also the original file for your references.
Hope this helps and All the very best! You did great.

Thank you very much for the help with the code. It works wonderfully. Now to get it integrated with the rest of my project

You are awesome.
Cheers,
Patrick