Excel User Authentication

Incorporate a user authentication form in Excel to let users login individually with name and password. This approach adds a level of restriction to spreadsheets on top of existing Excel workbook and worksheet protection options. Let’s see how it works.

Excel counts with a number of possibilities to restrict access to spreadsheets. We can password protect a particular workbook. We can also hide sheets we don’t want users to see or protect the worksheet from editing. Furthermore, we can store the spreadsheet on a SharePoint where access can be restricted to certain members. 

Sometimes we may want to have that control at the file level, each user being able to request access and having a unique personal password. The admin or owner of the file is responsible to maintain that list of users.

Hide All Elements

Opening the Excel User Authentication triggers the Workbook_Open event procedure (located in Thisworkbook object module), which calls the HideDisplay subroutine and then launches the Login Userform.

The HideDisplay procedure hides all sheets except one (Authentication), as a workbook must contain at least one visible worksheet. The Authentication worksheet has just a black background and nothing in it. The code sets the user list sheet (Users) to very hidden, so that users cannot access its contents. However, that can be changed in the VBA editor or with VBA code, and therefore, the VBA project should be password protected. That’s done in the VBA editor under Tools -> VBA Project Properties -> Protection -> Lock project for viewing.

 

  For Each ws In Worksheets

          If ws.Name = "Users" Then

              ws.Visible = xlSheetVeryHidden

          ElseIf ws.Name = "Authentication" Then

              ws.Visible = xlSheetVisible

          Else

              ws.Visible = xlSheetHidden

          End If

  Next

Then it hides the formula bar, headings, scroll bars and displays the full screen view with the corresponding VBA properties of the Application and ActiveWindow objects (see below).

 

  ActiveWindow.DisplayVerticalScrollBar = False

  ActiveWindow.DisplayHorizontalScrollBar = False

  ActiveWindow.DisplayHeadings = False

  Application.DisplayFormulaBar = False

  Application.DisplayFullScreen = True

Login Form

The Login form prompts as a result of the workbook open event. The Userform named ‘LogInForm’ includes two text boxes to capture name and password, along with three command buttons.

The ‘Submit’ button triggers the code that checks if name and password are correct as per the information in the very hidden “Users” sheet. The ‘Request Access’ button adds the requestor’s name to column C of the “Users” sheet for the admin or owner to confirm and assign a password.

 

Both name and password to access the file are set to “admin” within the code itself and it should be changed as required in the SubmitForm_Click event procedure located in the Userform module code:

  If uName = "admin" And uPsw = "admin" Then

      Unload Me

      ShowDisplay

      Exit Sub

  End If

The “Close” button will close the workbook and the Excel application itself. Thus, the user will not be able to access any of the components of the file (as long as the VBA project has been protected).

Show All Elements

Successful login will trigger the ShowDisplay procedure, which makes all sheets in the workbook visible except the “Users” and “Authentication” worksheets. It also displays all the components discussed earlier by setting the corresponding Application and ActiveWindow object properties to True. Users can then work with the workbook, edit, add new sheets, etc, and save the workbook as usual when finished.

Click Here to download Excel User Authentication.