How to Use the Windows Username to Limit Access to Worksheets

In a company environment we might wish to control what a user can and can’t see within a workbook, depending on their authority level. Windows usernames and levels are stored in a database (preferably) or, as in this case, a hidden sheet within the workbook.

This article assumes the reader has the Developer ribbon displayed, and is familiar with the VBA Editor. If not, please Google Excel Developer Tab or Excel Code Window.

  1. Open a blank workbook. In testing our code, Sheets 1, 2 and 3 will be visible at administrator level. Sheets 2 and 3 will be visible at manager level and Sheet 3 will visible to anyone else.
  1. Create a new sheet within the workbook called UserLevels and populate it as shown. Put your own Windows username in cell A2.
    If you don’t know your Windows name, press the Win + r keys.  Type cmd  in the run input box that opens.  At Command line prompt type echo %username%.Create A New Sheet Within The Workbook Called UserLevel

The application uses the Auto_open procedure to find who is logged onto the computer before displaying any sheets.  Then it cycles through column A of the sheet above until it finds a match to the username and notes his or her level (a database would require no cycling).

Should the application not find a match, it will escape the loop and presume the level to be 3.

We’ll use the For…Next statement for looping. The workbook used in the exercise can be found here.

  1. In the code window, insert a new Module (“modMain”) with the following code:
Option Explicit
Dim strUser As String      ‘Declare variables to be used by the application
Dim sRow As Integer
Dim sLookupName As String
Dim nLevel As Integer
Dim i As Integer

Function UserNameWindows() As String      ‘called from code in Auto_open, below.
    UserNameWindows = Environ("UserName")
End Function

Sub Auto_Open()
    Sheets("Sheet1").Visible = xlVeryHidden      ‘The user can’t unhide xlVeryHidden sheets.

    Sheets("Sheet2").Visible = xlVeryHidden
    Sheets("UserLevels").Visible = xlVeryHidden
    strUser = UserNameWindows()  'call the function
    
    For i = 2 To 102     ‘Cycle through 100 users.
        sLookupName = Sheets("UserLevels").Cells(i, 1)    ‘Get name from column 1.
        nLevel = Sheets("UserLevels").Cells(i, 2)     ‘Get Level from column 2.

        If sLookupName = "" Then ‘user not found
            Exit For
        End If
        
        If sLookupName = strUser Then     ‘A match!
            If nLevel = 1 Then
                Sheets("Sheet1").Visible = True
                Sheets("Sheet2").Visible = True
            ElseIf nLevel = 2 Then
                Sheets("Sheet2").Visible = True
            End If
            Exit For     'Stop looking since match has been found.
        End If
    Next i
End Sub

Sub Auto_Close()
    Sheets("Sheet1").Visible = xlveryhidden
    Sheets("Sheet2").Visible = xlveryhidden
    Sheets("UserLevels").Visible = xlVeryhidden
    Activeworkbook.Save    ‘Force a final Save after hiding sheets
End Sub
  1. For test purposes, add four buttons to sheet3 so we can change our user-level on demand to show hidden sheets. Add Four Buttons To Sheet3
  2. Add temporary code to modMain.
Sub ChangeLevel1()
    Sheets("UserLevels").Range("B2") = 1
    Call Auto_Open
End Sub

Sub ChangeLevel2()
    Sheets("UserLevels").Range("B2") = 2
    Call Auto_Open
End Sub

Sub ChangeLevel3()
    Sheets("UserLevels").Range("B2") = 3
    Call Auto_Open
End Sub

Sub ShowUserLevels()
    Sheets("UserLevels").Visible = True
End Sub
  1. Assign each button to its relevant procedure (right_click the button, choose Assign Macro…).
  2. Click buttons in turn to view results.
  3. Protect your macro from being viewed or changed: Tools>Properties>Protection.

Repair Corrupt Excel Worksheets

As Excel grows more complex with each succeeding version, so we increasingly find file corruptions where the workbook fails to open properly.  If you have an effective Excel  repair tool in your armoury, you can solve such issues easily and quickly.

Author Introduction:

Felix Hooker is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair rar problem and sql recovery software products. For more information visit www.datanumen.com

One response to “How to Use the Windows Username to Limit Access to Worksheets”

  1. I’m in IT training since last 25yrs,, I’m developer also,, found some of your solutions extremely helpful.