Check if user is allowed to open the excel file with using a user list.

[aequitas1903]

Hi everyone,

I am always creating some excel templates. In this templates, I create auto_open() macro to check if the user is in allowed user list. If yes, user can work on the template. Otherwise excel closes itself immediately.

I am doing this by entring the allowed users computer name on a sheet. and vlookup the Environ$("Username"). The templates are workbook,worksheet and vba protected. So if I need to add a user it takes too much time to do the necessary changes. And I have to do that in all templates.

The question is if there is a way to create a user list in vba code itself? So I can easily enter the vba password and add the new user in every template quickly. I won't need to unprotect sheet update lookup range and etc.

And is it possible to identify the users with names.

For Example
the list name will be Allowed_staff
and in that list; the users (Environ$("Username") ) are PC123 , PC456, PC789
PC123 = "Susan Red"
PC456 = "John Green"
PC789 = "Tom Blue"

I am using vlookup to check the user name and send a greeting msgbox

Please let me know if you think of a way to do this.

Kind regards

 

[Fishboy]

Hi everyone,

I am always creating some excel templates. In this templates, I create auto_open() macro to check if the user is in allowed user list. If yes, user can work on the template. Otherwise excel closes itself immediately.

I am doing this by entring the allowed users computer name on a sheet. and vlookup the Environ$("Username"). The templates are workbook,worksheet and vba protected. So if I need to add a user it takes too much time to do the necessary changes. And I have to do that in all templates.

The question is if there is a way to create a user list in vba code itself? So I can easily enter the vba password and add the new user in every template quickly. I won't need to unprotect sheet update lookup range and etc.

And is it possible to identify the users with names.

For Example
the list name will be Allowed_staff
and in that list; the users (Environ$("Username") ) are PC123 , PC456, PC789
PC123 = "Susan Red"
PC456 = "John Green"
PC789 = "Tom Blue"

I am using vlookup to check the user name and send a greeting msgbox

Please let me know if you think of a way to do this.

Kind regards

Hi aequitas1903,

How about something like this:

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

Change the usernames as required, you can have more than 3 in the array.

 

[aequitas1903]

Hi aequitas1903,

How about something like this:

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

Change the usernames as required, you can have more than 3 in the array.

Hi Fishboy,

Thanks for the reply and the hint on defining users in array. It works perfectly. But just one more question.

I did as you suggested and put PC123 in array. When I run the macro the message was like "Welcome PC123."
I would like to show the name of the user like "Welcome Susan Red"

Is there a way to define the usernames in array as mentioned above ?

Thanks in advance and thanks again for your reply.

 

[Fishboy]

Hi Fishboy,

Thanks for the reply and the hint on defining users in array. It works perfectly. But just one more question.

I did as you suggested and put PC123 in array. When I run the macro the message was like "Welcome PC123."
I would like to show the name of the user like "Welcome Susan Red"

Is there a way to define the usernames in array as mentioned above ?

Thanks in advance and thanks again for your reply.

Hmm, this is untested but I believe this should do what you are asking. I am not sure how efficient this will be if you have many different users however:

Private Sub Workbook_Open()
Dim UserNames As Variant
Dim UserCheck As String
Dim UserID As String


UserNames = Array("PC123", "PC456", "PC789")
UserCheck = Application.UserName


If Not IsNumeric(Application.Match(UserCheck, UserNames, 0)) Then
    MsgBox "You do not have permission to open this file"
    ThisWorkbook.Close False
Else
    If UserCheck = "PC123" Then
        UserID = "Susan Red"
    ElseIf UserCheck = "PC456" Then
        UserID = "John Green"
    ElseIf UserCheck = "PC789" Then
        UserID = "Tom Blue"
    End If
    MsgBox "Welcome " & UserID & "."
End If


End Sub

 

[Akuini]

Hi, Fishboy
Just an idea, I think you could use 2 array to make your code more simple.
Something like this:

Dim UserNames As Variant, UserNames1 As Variant
Dim UserCheck As String
Dim UserID As String

UserNames = Array("PC123", "PC456", "PC789")
UserNames1 = Array("Susan Red", "John Green", "Tom Blue")
UserCheck = Application.UserName

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

 

[Fishboy]

Hi, Fishboy
Just an idea, I think you could use 2 array to make your code more simple.
Something like this:

Dim UserNames As Variant, UserNames1 As Variant
Dim UserCheck As String
Dim UserID As String

UserNames = Array("PC123", "PC456", "PC789")
UserNames1 = Array("Susan Red", "John Green", "Tom Blue")
UserCheck = Application.UserName

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

Nice solution Akuini!

 

[aequitas1903]

Thank you so much Fishboy and Akuini.

It works perfectly. Thanks to you, now I can save sometime.

 

[Fishboy]

Thank you so much Fishboy and Akuini.

It works perfectly. Thanks to you, now I can save sometime.

Happy to help.