Determining a user’s security group in VBA

Excel, Security, Software, VBA , , , 0 Comments

We’ve recently completed a project where we needed our add-in to behave differently depending on the security group of the user.  For example, if the user was an estimator, the add-in was to perform behaviour X; but if the user was clerical staff, the add-in should perform behaviour Y.

The client had a server that was running Windows Server 2003.  Users on the server were assigned to security groups, and gained their access rights from those.  The solution we came up with was to attempt to open a file on the server.  The file permissions were set so that it could only be accessed by users in group A.  When the code ran, it would determine whether the file could be opened by the current user.  If the file was accessible, the user was in group A.  If the file wasn’t accessible, the user was in group B! Though you could easily check more than one file if you have more than two groups.

Function IsFileLocked(sFile As String) As Boolean
    On Error Resume Next
     ''' Open the file
    Open sFile For Input As #1
     ''' Close the file
    Close #1
     ''' If error occurs the document if open!
    If Err.Number <> 0 Then
         ''' Return true and clear error
        IsFileLocked = True
        Err.Clear
    End If
End Function