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

[jlc29369]

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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]User Name[/TD]
[TD]Password[/TD]
[TD]Data Sheet[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]bsmith[/TD]
[TD]123[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]twilliams[/TD]
[TD]456[/TD]
[TD]x[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Steve_]

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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]User Name[/TD]
[TD]Password[/TD]
[TD]Data Sheet[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]bsmith[/TD]
[TD]123[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]twilliams[/TD]
[TD]456[/TD]
[TD]x[/TD]
[/TR]
</tbody>[/TABLE]

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...

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]

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]

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]

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_]

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]

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)

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.

 

[jlc29369]

Thank you very much for your help!