VBA to prompt for user name and password for opening Excel file.
Results 1 to 8 of 8

Thread: VBA to prompt for user name and password for opening Excel file.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy VBA to prompt for user name and password for opening Excel file.

    So here is what I have...please help

    I have created three sheets in Excel.
    The first sheet contains the actual data
    The second sheet contains an audit log that is already set up through VBA code.
    The third sheet is a user list of all the authorized users that will be able to access the spreadsheet, set up like this example:

    A B C
    1 User Name Password Data Sheet
    2 bsmith 123 x
    3 twilliams 456 x

    I want to know how to create a prompt that will appear upon opening the spreadsheet that will only let authorized users access the file.
    Upon opening the file, I want the second and third sheets to use the "xlSheetVeryHidden" feature so they will be hidden from general users.

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to prompt for user name and password for opening Excel file.

    Quote Originally Posted by jlc29369 View Post
    So here is what I have...please help

    I have created three sheets in Excel.
    The first sheet contains the actual data
    The second sheet contains an audit log that is already set up through VBA code.
    The third sheet is a user list of all the authorized users that will be able to access the spreadsheet, set up like this example:

    A B C
    1 User Name Password Data Sheet
    2 bsmith 123 x
    3 twilliams 456 x

    I want to know how to create a prompt that will appear upon opening the spreadsheet that will only let authorized users access the file.
    Upon opening the file, I want the second and third sheets to use the "xlSheetVeryHidden" feature so they will be hidden from general users.

    Heads up, this is NOT very secure. but, either way, what you asked for was a pop up that requested credentials, so here you go...


    Code:
    Private Sub Workbook_Open()
        uName = InputBox("Please type your username.", "Authentication Required", Environ("USERNAME"))
        uPwd = InputBox("Please type your password.", "Authentication Required")
    End Sub

    Post that code in the "ThisWorkbook" module of your project

  3. #3
    Board Regular
    Join Date
    Oct 2015
    Location
    South Wales
    Posts
    549
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to prompt for user name and password for opening Excel file.

    An issue is that if the user disables macros prior to opening, they won't be asked for the password and will get access to the data.

    To add a bit more security, I'd suggest:
    - create a fourth sheet - either leave it blank, or put a message on it to tell the user to enable macros and reopen the spreadsheet if they are not asked for a password.
    - In your workbook_open macro, if the user enters the correct password, make the actual data sheet visible and very hide the blank sheet.
    - Add a workbook_beforesave macro to make the blank sheet visible and very hide the data sheet.
    - Add a workbook_aftersave macro to reverse the effect of the beforesave macro.
    - Password protect the VBA project.

    This way, wherever the workbook is saved, it will have all sheets (except the blank one) very hidden.

  4. #4
    New Member
    Join Date
    Jul 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to prompt for user name and password for opening Excel file.

    Thanks.
    Even though the password inboxes are there, there is no authentication for the users that are hidden within the third sheet. And the data is visible when it is opened...so is there any way that the data can be hidden until the login credentials are validated?

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to prompt for user name and password for opening Excel file.

    Quote Originally Posted by Trevor_S View Post
    An issue is that if the user disables macros prior to opening, they won't be asked for the password and will get access to the data.

    To add a bit more security, I'd suggest:
    - create a fourth sheet - either leave it blank, or put a message on it to tell the user to enable macros and reopen the spreadsheet if they are not asked for a password.
    - In your workbook_open macro, if the user enters the correct password, make the actual data sheet visible and very hide the blank sheet.
    - Add a workbook_beforesave macro to make the blank sheet visible and very hide the data sheet.
    - Add a workbook_aftersave macro to reverse the effect of the beforesave macro.
    - Password protect the VBA project.

    This way, wherever the workbook is saved, it will have all sheets (except the blank one) very hidden.
    Thank you for your input. I am going to hide Sheet 2 and Sheet 3 and password protect the VBA. I just need to figure out a way to authenticate the users and hide the data when the spreadsheet is opened.

  6. #6
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to prompt for user name and password for opening Excel file.

    Quote Originally Posted by jlc29369 View Post
    Thank you for your input. I am going to hide Sheet 2 and Sheet 3 and password protect the VBA. I just need to figure out a way to authenticate the users and hide the data when the spreadsheet is opened.

    Set the "password sheet" to very hidden. On open, hide the workbook, unhide the passwordsheet(tho i would consider just hiding that in my password protected code as variables), load the data into an array, then set the sheet back to very hidden at the end of the macro. of course then unhide your workbook.

  7. #7
    Board Regular
    Join Date
    Oct 2015
    Location
    South Wales
    Posts
    549
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to prompt for user name and password for opening Excel file.

    Assuming your current sheets are called Data, Audit Log and Password, and you create the blank sheet that I suggested and call it Blank ... the macros I think you need (all under Workbook) would be as follows (first bit as supplied by Steve)
    Code:
    Private Sub Workbook_Open()
        uName = InputBox("Please type your username.", "Authentication Required", Environ("USERNAME"))
        uPwd = InputBox("Please type your password.", "Authentication Required")
    On Error Goto ErrorRoutine
    If uPwd = Application.WorksheetFunction.Vlookup(uName, Sheets("Password").Range("A:B"), 2, False) Then
    'Password correct
    Sheets("Data").Visible = True
    Sheets("Blank").Visible = xlVeryHidden
    Sheets("Audit Log").Visible = xlVeryHidden
    Sheets("Password").Visible = xlVeryHidden
    Else
    'Password incorrect
    ErrorRoutine:
    msgReply = MsgBox("Password is incorrect.  Spreadsheet will be closed.", "Invalid Password", vbOkOnly)
    ActiveWorkbook.Close (False)
    End If
    End Sub
    
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel as Boolean)
    Sheets("Data").Visible = xlVeryHidden
    Sheets("Blank").Visible = True
    Sheets("Audit Log").Visible = xlVeryHidden
    Sheets("Password").Visible = xlVeryHidden
    End Sub
    
    Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    Sheets("Data").Visible = True
    Sheets("Blank").Visible = xlVeryHidden
    End Sub
    Amend the sheet names in the macro as appropriate. I haven't been able to test this (am using a phone, rather than PC), so try it on a copy of your spreadsheet first, rather than the original.
    Last edited by Trevor_S; Jul 18th, 2019 at 01:39 PM.

  8. #8
    New Member
    Join Date
    Jul 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to prompt for user name and password for opening Excel file.

    Thank you very much for your help!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •