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

jlc29369

New Member
Joined
Jul 17, 2019
Messages
7
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:

ABC
1User NamePasswordData Sheet
2bsmith123x
3twilliams456x

<tbody>
</tbody>

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.
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Steve_

Board Regular
Joined
Apr 28, 2010
Messages
167
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:

ABC
1User NamePasswordData Sheet
2bsmith123x
3twilliams456x

<tbody>
</tbody>

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
 

Trevor_S

Well-known Member
Joined
Oct 17, 2015
Messages
610
Office Version
2016
Platform
Windows
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.
 

jlc29369

New Member
Joined
Jul 17, 2019
Messages
7
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?
 

jlc29369

New Member
Joined
Jul 17, 2019
Messages
7
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.
 

Steve_

Board Regular
Joined
Apr 28, 2010
Messages
167
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.
 

Trevor_S

Well-known Member
Joined
Oct 17, 2015
Messages
610
Office Version
2016
Platform
Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,101,815
Messages
5,483,075
Members
407,377
Latest member
JennaWashburn

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top