Open workbook depend on User list

[alonelove]

Hi All:

I found this code to open workbook if only name Array the same Username of PC:

Private Sub Workbook_Open()
Dim UserNames As Variant


UserNames = Array("Username1", "Username2", "Username3")


If Not IsNumeric(Application.Match(Application.UserName, UserNames, 0)) Then
    MsgBox "You do not have permission to open this file"
    ThisWorkbook.Close False
Else
    MsgBox "Welcome " & Application.UserName & "."
End If


End Sub

How to change Array("Username...") by list name A1:A10 in Sheet("Username")

Thanks./.

 

[sykes]

Try this:

Private Sub Workbook_Open()
Dim UserNames As Variant


With Sheets("Username")

UserNames = Array(.Range("A1").Value, .Range("A2").Value, .Range("A3").Value)

End With


If Not IsNumeric(Application.Match(Application.UserName, UserNames, 0)) Then
    MsgBox "You do not have permission to open this file"
    ThisWorkbook.Close False
Else
    MsgBox "Welcome " & Application.UserName & "."
End If


End Sub

......just extend the array all the way up to A10.

It could be better, but it'll do the job....

 

[alonelove]

Thanks Your idea but I want User name depend on data range in sheet. Because maybe I can add or remove user who can open excel file easier.

Do you have any idea?

 

[sykes]

............that's what I've done for you!

On opening the workbook, the code will now populate the array with the usernames in your sheet called "Username" (which is what you asked for), from A1 to A3 - you just need to continue the code all the way up to A10, but I assumed you could do that.
A long as the user attempting to open the workbook, has their username in your sheet, from A1 to A10, then the code will allow the sheet to be opened.

 

[Fluff]

@alonelove
You are using Application.username, which return the Excel username, but you say you want the PC username.
Try

Private Sub Workbook_Open()
Dim UserNames As Variant


With Sheets("Username")

UserNames = .Range("A1:A10").Value

End With


If Not IsNumeric(Application.Match(Environ("UserName"), UserNames, 0)) Then
    MsgBox "You do not have permission to open this file"
    ThisWorkbook.Close False
Else
    MsgBox "Welcome " & Environ("UserName") & "."
End If


End Sub

 

[alonelove]

@alonelove
You are using Application.username, which return the Excel username, but you say you want the PC username.
Try

Private Sub Workbook_Open()
Dim UserNames As Variant


With Sheets("Username")

UserNames = .Range("A1:A10").Value

End With


If Not IsNumeric(Application.Match(Environ("UserName"), UserNames, 0)) Then
    MsgBox "You do not have permission to open this file"
    ThisWorkbook.Close False
Else
    MsgBox "Welcome " & Environ("UserName") & "."
End If


End Sub

Thanks Fluff, this code working.

Can you help me add-ing more function that I want people is not in username range can open file in read_only mode.

Thanks in advanced./.

 

[Fluff]

Try

Private Sub Workbook_Open()
Dim UserNames As Variant


With Sheets("Username")

UserNames = .Range("A1:A10").Value

End With


If Not IsNumeric(Application.Match(Environ("UserName"), UserNames, 0)) Then
    MsgBox "You do not have permission to modify this file"
    ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
Else
    MsgBox "Welcome " & Environ("UserName") & "."
End If


End Sub

 

[alonelove]

Try

Private Sub Workbook_Open()
Dim UserNames As Variant


With Sheets("Username")

UserNames = .Range("A1:A10").Value

End With


If Not IsNumeric(Application.Match(Environ("UserName"), UserNames, 0)) Then
    MsgBox "You do not have permission to modify this file"
    ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
Else
    MsgBox "Welcome " & Environ("UserName") & "."
End If


End Sub

Perfect code, many thanks Fluff.

I've just found this code

Sub ArryTest()
Dim Myarray
Dim i As Long, j As Long
If Environ("username") <> "VoG" Then Exit Sub
Myarray = Range("A1:B10")
For i = 1 To 10
    For j = 1 To 2
        Cells(i, j).Offset(, 2).Value = Myarray(i, j)
    Next j
Next i
End Sub

How can I use new code? Is there any diffrences with yours?

Sorry i'm beginer with exxcel macro.

 

[Fluff]

That code is completely different.
It only checks for one username