Verifying user access to a macro from an external list.

[CodeRebel]

I have some macros that have to be ran by an internal team. The managers of that team are the macro testers. And everybody else on the team is just a user. However, everyone in the company has access to the form.

What I need is a code that can distinguish between the two like I have included below: (THIS WORKS)

Function IdentifyUser()
    UN = Application.UserName
    If UN = "McKinney, David A" Or UN = "Jingle, John C [US] (MS)" Or UN = "Jacobs, John [US] (SP)" Then '<-- Operators and/or Testers
        Condition = "Operational"
        OpOrT = MsgBox("Are you performing a Test on the Macro?", vbYesNo, "Test or Operational")
            Select Case OpOrT
                Case vbYes
                    Condition = "Test"
                Case vbNo
                    Condition = "Operational"
             End Select
    ElseIf UN = "Jasper, Jennifer [US] (MS)" Or UN = "Jingle, Jerome [US] (SP)" Then
        Condition = "Operational"
    Else
        Condition = "Unknown"  '<-- Non-Department Users
    End If
End Function

Whenever we get a new team member on the team or one quits we have to manually go through and insert their user name on their macros on their computer to give them access... Is there a way I can transform this code to check the users against an external list on a sharedrive, see what permissions the user has, and allow them to advance as permitted? The end goal being that it works the same, however I can manage the list of users externally?

 

[CodeRebel]

I forgot to mention, ideally I'd like to manage the users in an access database on the sharedrive, but would be perfectly fine managing them from another excel workbook on a sharedrive

 

[CodeRebel]

Bumping once for help please.

 

[dmt32]

Hi,

Not very practical to hardcode names in your function. Better & easier to maintain if your function reads from a table of valid users where you can also validate their user status

Give following a try & see if will do what you want

Create a table as below & enter all users usernames (I would suggest that you enter their network username) and select their access status from the validation list

23-03-2023.xls
	A	B
1	User Name	Status
2	McKinney, David A	Tester
3	Jingle, John C [US] (MS)	Tester
4	Jacobs, John [US] (SP)	Tester
5	Jasper, Jennifer [US] (MS)	User
6	Jingle, Jerome [US] (SP)	User
Users

Cells with Data Validation
Cell	Allow	Criteria
B2:B6	List	Tester,User

Place updated function in a STANDARD module

Function IdentifyUser(ByRef UserStatus As String) As Boolean
    Dim UsersName As String, m As Variant
    Dim tblUsers As ListObject
    
    UsersName = Application.UserName
    
    'or
    
    'if using network username
    'UsersName = Environ("UserName")

    Set tblUsers = ThisWorkbook.Worksheets("Users").ListObjects(1)
    
    m = Application.Match(UsersName, tblUsers.ListColumns(1).Range, 0)
     IdentifyUser = Not IsError(m)
    
    If IdentifyUser Then UserStatus = tblUsers.DataBodyRange(m - 1, 2)
    
End Function

And in your main code you would call it as follows

Sub CodeRebel()
    Dim Response    As VbMsgBoxResult
    Dim Condition   As String, Status As String
    
    If IdentifyUser(Status) Then
    
        If Status = "Tester" Then
            Response = MsgBox("Are you performing a Test On the Macro?", 36, "Test Or Operational")
            Condition = IIf(Response = vbYes, "Test", "Operational")
        Else
            'User
            Condition = "Operational"
        End If
        
    Else
    
        Condition = "Unknown"        '<-- Non-Department Users
        
    End If
End Sub

Hope Helpful

Dave

 

[CodeRebel]

Thank you for the help brother!

Your code wouldn't work directly for my application, but it did lead us down the right path.

This is how I ended up having to set it up for my use.

The table was an external workbook on the intranet server

Then I set a global variable because our "Role" will be used across several subs in a sequence

Dim Role As String

Then Function goes as:

Function IdentifyUser(ByRef UserStatus As String) As Boolean
    Dim App As New Excel.Application
    App.Visible = False
    Dim UWb As Workbook
    Dim tblUsers As ListObject
    Dim US As Worksheet

    UN = Application.UserName
    'UN = "That Guy" '<--- For Testing Purposes to throw error
    'or
    
    'if using network username
    'UsersName = Environ("UserName")
    
    FilePath = "\\server\link\"
    Set UWb = App.Workbooks.Open(FilePath & "Macro User List.xlsx")
    Set US = UWb.Worksheets("Users")
    Set tblUsers = US.ListObjects("Table1")
    
    FindPMPEUser = Application.Match(UN, tblUsers.ListColumns(1).DataBodyRange, 0)
    
    If IsError(FindPMPEUser) Then
        MsgBox "Unauthorized!"
        App.Workbooks.Close
    Else
        Role = tblUsers.Range(FindPMPEUser + 1, 2).Value
        App.Workbooks.Close
    End If
    App.Quit
End Function

Next, I called the function as follows in the other subs:

   Dim Response    As VbMsgBoxResult
    Dim Condition   As String, Status As String
    
    IdentifyUser (Status)
    
        If Role = "Tester" Then
            Response = MsgBox("Are you performing a Test On the Macro?", 36, "Test Or Operational")
            Condition = IIf(Response = vbYes, "Test", "Operational")
        ElseIf Role = "User" Then
            Condition = "Operational"
        ElseIf Role = "Unknown" Then
            Condition = "Unknown"
        Else
            Condition = "Unknown"
        End If

And this works for our application. I really appreciate the help and your time. Thank you so much!

 

[dmt32]

Hi
glad idea helped you & appreciate your feed back

Dave