Long, Difficult, Very Specific Problem on VBA Code for User Form Log In for Multiple Users

[Bazman1981]

I have a very difficult and specific request that I would like a lot of help with please.
I have a very large macro-enabled workbook with around 260 sheets. Originally, I used some VBA code so that no matter what sheet the user was on when they saved and closed the document, the next time it was opened it would open on the sheet “Start_Sheet” which is essentially a welcome page.

However, what I would now like to do is make it so that a number of different Users (up to 20) can log in individually with their own unique username and unique password to access a number of different sheets based on their access rights. I have already created the User Form (“UserForm1”) which has the User Name and Password entry fields and then as two buttons “Log In” and “Cancel”.
I have also created a sheet called “Owner” in which I have filled in the following:
Row 1 is Headings. So Cell A1 is “User Name” A2 is “Password” and A3 is labelled “Sheets” but I haven’t bothered to label the other cells in this row.
Row 2 is the first row of log in data. So Cell A2 is the first user name which is “Admin”, Cell B2 is the password for the user “Admin” which is “1234” and then Cell C2 is the first sheet that the admin user can view, which in this case is “Owner”. In Cell D2 we then have the second sheet (call it “Start_Screen”) E2 is the next sheet….and so on. This range is as I said, around 260 cells because Admin can view every single sheet in the book.
The next username is in Cell A3 and let’s call this person “John Smith” and a password in Cell B3 of “5678”. John Smith can access every sheet also except for a few (like the Owner sheet and some hidden data sheets which are just for the admin) so again Cells C3 to something like ZZ3 will be the names of the sheets he can open.
Then Cell A4 is user “Jane Smith” and her passcode is 1966 in Cell B4. She can only access 6 of the sheets so her 6 sheets are listed in cells C4:I4.
The list of Users will finish in Cell A21 so that there are 21 users in total. Then in Cell A25 the name of the “Current User” i.e. the person who logs in should appear.

So with all of this in mind, I then want the sheet to function as follows:

1. When someone first opens the Excel Workbook, I want it to default to open on the sheet named “Log_In” which will just be a blank sheet. Every single other sheet in the workbook will be completely invisible and should be VeryHidden to avoid people being able to unhide sheets.
2. At the same time, the UserForm1 log in box will appear and the user will have the option to enter their username and password. If the user accidentally opened the sheet but does not want to log in (or is not a registered user) then I want them to be able to click the “Cancel” button and that will just close the workbook. I also want the little X in the corner to either close the workbook as well or provide an error message saying that the document cannot be closed and the user must click cancel to close the workbook. (in case clicking the x closes the user form and then provides no way to log in)
3. If the User IS a registered user then they type in their name and password and as long as they are a matching combination with the list of users on Sheet “Owner” then they will gain access to the corresponding sheets which are listed next to their name as described above. Any sheets they don’t have access to will remain Very Hidden. If someone types in an incorrect user name or password combination then I want it to produce an error message which says “Incorrect User Name and Password combination. Please try again or contact the administrator for help”
4. I also want at this time for the name of the user who has logged in to appear in Cell A25 on the sheet “Owner” so that on the “Start_Sheet” their name is inserted into the “welcome” text. I already have the welcome text set up to read the name from Cell A25 in “Owner” but I just don’t know how to make A25 change to the name of whomever has logged in.
5. Likewise, this correct log in combination should transport the user to the “Start_Sheet” so they know they have logged in successfully.
6. Crucially, I would like the VBA code to read from the list of user names and passwords in the “Owner Sheet” as a range or table because I want to be able to change these later by just changing the names and passcodes (or sheet access) on the “Owner” sheet without having to go into the VBA code and typing over each name and password.

Hopefully all of this makes sense. I have read a number of posts on subjects similar to this but I have been unable to find any VBA code which does exactly what I want.

I would be very very grateful if someone is able to take on this challenge and advise me of the best code to use to do this

Thank-you

Bazman1981

 

[dmt32]

Hi,
If your users have access to your workbook over your corporate network then a better way maybe to manage your requirement would be via a table with user’s network names & sheets they have access to.
Sheet visibility for each user would be automatic (no userform passwords needed) as valid users would be checked against their network Username & sheets they have access to in the table when they open the workbook.

have a look here:Password Protect Viewing for Multiple Worksheets

and follow guidance in #post3 & see if solution helps you

Dave

 

[Bazman1981]

Thank-you, I will give that a try in the interim and see if I can get that to work. It sounds like the functionality of the access would work, but I would ideally like to have the log in box to be honest because it then starts to take the form of a system rather than just a spreadsheet. One of the features of it taking you to the welcome page and inserting the "welcome XXX" is an appeal.

 

[dmt32]

Hi,
How you choose to develop your application is a matter for you - upside of suggested approach is that it requires no input by users and is as foolproof as it can be in that it works by checking users network user name to display correct sheets. Also, users do not need to remember a password & requires no admin maintenance.

If though you prefer a a login solution then have a look here:Excel Userform Login - Online PC Learning
and see if this helps you

Dave