royUK Super Moderator

  • Member since Jan 26th 2003
  • Last Activity:
Likes Received
Profile Hits
  • Hi Roy,

    Sorry - slight error in my previous request on the display.

    User Names are listed in Cells A2, A3, A4 etc down to A26

    Passwords are listed in Cells B2 - B26

    and the bit I stated incorrectly were the sheet names, they are listed in cells C2 to IW2 for the Administrator, then C3 to CX3 for Dave (i.e. the sheets are listed horizontally)

    Apologies for making that more confusing

  • Hi Roy,

    I was looking at your Password Form 7 which when I use your sample works perfectly, but I am struggling to apply it to my own existing Excel workbook and I was wondering if you would be willing to help me.

    I have an enormous workbook that has 257 sheets. I have created a User Form (UserForm1) which I want to pop up on a blank sheet called "START_SHEET" - The user will then type in their user name and password and assuming these are correct they will be granted access to a specific number of sheets based on their rights.

    So I have created a sheet called "Administrator". I want this sheet to be hidden always from everyone other than the Admin (i.e. me).

    On that sheet I want to create a table which lists all of the registered users in Column A (So Cell A2 would be "Admin" then cell A3 would be "Dave" for example) but I would ideally like to set this a lookup so that I can add names if I need to without having to change all of the code later.

    Then Column B would be their password which only I can see as admin obviously. So cell B2 would be 1234 (i.e. the admin password) and then Cell B3 would be 4567 (Dave's unique password) and again, I'd ideally like the VBA code to read from a range rather than me have to go and enter each password individually into the code.

    I would like to repeat this for 25 users, so the range I am referring to would be A2 - A26 for the names and B2 to B26 for the corresponding passwords.

    Then the really difficult bit, "Admin" obviously needs to be able to access all 257 sheets so I then planned to list in cells C2 to C260 each of the sheet names to match my workbook.

    "Dave" however is only allowed to access say 100 of them and then user 3 might be allowed 110 etc so again in my mind the easiest thing to do was to list all of the sheets Dave can access in cells C3 to C103 and so on so that the VBA code read from the range per person rather than listing every sheet.

    All of the examples I have seen (which I have still struggled to implement to be honest) seem to name each sheet and each person and each password which is just not practical for me given the size of the workbook.

    I would like to also have the X button disabled and warning message and the incorrect password message if someone enters an unrecognised user name and/or incorrect password. If someone fails 3 times then I would like it just to put up an error box advising it will now close and once they click Ok it closes.

    I have seen numerous tutorials for the basics of this but I just can't get one that can do everything I need.

    Finally, irrespective of who it is that logs on, I want every person who logs on successfully to be transported to a sheet called "Welcome_Sheet"

    Ideally I would then like a piece of code that would know based on the user who logged in and would insert their name in cell A1 so I can copy from that and insert it into a welcome message tailored to the person in question. (So if "Dave" logged in, Cell A1 of the worksheet "Welcome_Sheet" would say "Dave")

    I appreciate this is long winded but I would be incredibly grateful if you are able to help me with a more tailored answer than a link to a tutorial because I am not an expert in VBA, hence the struggle to apply the tutorials to this workbook.

    Kind Regards

  • Hey brother happy Christmas