I would like to set up a macro to restrict an Excel file to a specific user from the list

Question

I have an excel file which I would like to restrict it for only a certain users to view so because there are multiple files I would like to setup a macro which will restrict the files to a certain users which I will provide on a list.

Answer 1

You DO NOT WANT TO DO THIS.

To prevent users from opening a file require them to open the file in order the macro to run (and if not the right user has opened it then close it immediately).
Besides it being annoying for the user there is not a safe way for it, if users starts the file without macros enabled then the macro just can't run and the file will open normally.

Instead look for blocking access to the file itself, depending on the technology you use. Most file sharing solution can manage access quite well.

Answer 2

"Protection" in Excel is no protection at all against a moderately experienced VBA user. It's so easily circumvented with readily available password hacks.

If you can be sure that all your users are naïve then there are strategies to give your workbook access to a certain group of users while excluding everyone else. As you haven't shown any code I'll give a few hints but leave it to you to patch it into a workable solution.

Password protect your workbook and VBA Project with different passwords. Different so users need 2 separate password hacks to get to your hidden sheets.

Hide all sheets except for an "Enable Macros" sheet before every save. By hide I mean set the sheets Visible property to xlVeryHidden. This setting masks the sheet name from Home > Format > Hide & Unhide > Sheets. Right-clicking a sheet tab will have the Unhide... option greyed out. Makes it hard for the casual user to even see the hidden sheets. If you apply sheet protection to the sheets their names may be exposed in the Backstage.

Build a function to convert Windows username to an 8 character string comprising the initial of firstname and first 7 digits of surname ignoring spaces in surname. I use:

Function UserShortName()
    User = Application.UserName
    If InStr(1, User, " ") Then
        UserShortName = LCase(Left(Left(User, 1) _
        & Replace(Mid(User, InStr(1, User, " ") + 1), " ", ""), 8))
    Else
        UserShortName = User
    End If
End Function

I don't use Apllication.UserUame because that can be changed fairly easily to impersonate someone else.

Build a table/range or an in-code array of UserShortNames of AllowedUsers.

Have a VisibilitySettings procedure that can be called from Workbook_AfterSave and Workbook_Open events. This sub should test the UserShortName against the AllowedUsers table/range/hard-coded names. VisibilitySettings will make selected sheets visible.

When a user opens the workbook they will see the "Enable Macros" if they don't have macros enabled. If they have macros enabled they will see whatever you allow in VisibilitySettings.

Answer 3

The below shown VBA (Macro) code will help you to create a Workbook, will automatically check the Window Logged in users, and allow or disallow access to the Workbook.

Private Sub Workbook_Open()

Application.AutomationSecurity = msoAutomationSecurityLow

Dim user As String
Dim users(4) As String

users(0) = "User1"
users(1) = "User2"
users(2) = "User3"
users(3) = "User4"


user = Application.UserName

Dim access As Boolean
Dim i As Integer

access = False

For i = 0 To 4
    If users(i) = user Then
        access = True
        Exit For
    End If
Next

If access = False Then
    MsgBox ("Opps, user """ & "user" & """ does not have access to open this workbook")
    ActiveWorkbook.Close
End If

End Sub

N.B.

• Save the Workbook as Excel-Macro Enabled Workbook with .Xlsm extension.
• If Macro is Disabled using File, Option, Trust Center, Marco Settings or by any method, then this Code line will Enables the Macro.

Application.AutomationSecurity = msoAutomationSecurityLow

• In the code, User Names are editable & all must be as similar as Windows User's Profile of that particular Computer.