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.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top