How to allow specific users to open excel file while other users need to enter password?

[yanlok1345]

Hi everyone,

Here's the Excel VBA code allow specific users to access the Excel file. Other users can open it in read-only mode.

However, other users can still click once to enter edit mode to edit.

I want the Excel VBA to allow specific users to open excel file, while other users need to enter password and cannot see what are the contents before they entered correct password.

Is is possible to achieve that?

Many thanks for your help!

Code: Select all

Private Sub Workbook_Open()
    
    Dim Users As String
    Dim Password As String
    
    Users = Environ("USERNAME")
    Password = InputBox("Enter password to access this workbook", "Password Required")
    
    Select Case Users
        Case "user1": MsgBox "ok"
        Case "user2": MsgBox "ok"
        Case "user3": MsgBox "ok"
        Case Else
            If Password = "your_password" Then
                ThisWorkbook.ChangeFileAccess xlReadWrite
            Else
                ThisWorkbook.ChangeFileAccess xlReadOnly
            End If
    End Select
    
End Sub

[HansV]

The Workbook_Open event procedure runs when the workbook is already open. A clever user could disable macros and so bypass your code.

I'd simply set a password needed to open the workbook. If the user doesn't know the password, they cannot open it.

[yanlok1345]

The Workbook_Open event procedure runs when the workbook is already open. A clever user could disable macros and so bypass your code.

I'd simply set a password needed to open the workbook. If the user doesn't know the password, they cannot open it.

Yes. You are right.

Can Excel VBA identify the username? If non-specified users are detected, the VBA will close the workbook. Only the specified username can open it.

Just like the following one:

Code: Select all

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

[HansV]

Application.UserName is the name that users specify in File > Options:

S2485.png

They can easily change it...
Also: if the user disables macros, the workbook will open without running the Workbook_Open event procedure...