environ("username") alternative

[gordsky]

Hi,

Hoping someone can point me in the right direction with this one.........thanks in advance.

So as part of the company structure individuals have different access to spreadsheets based on their username.
Some can only view certain sheets, some read only, some have full access etc etc.

At present this is controlled through code using If Environ("username") = X then do something or if Environ("username") = y then do something different.

The problem with this is that it is in around 30 different workbooks. As the staff within the company change it means having to go back and add or remove them from each of the individual workbooks.

What I would like to do is keep one central list in a separate workbook which I can update as necessary and have each of the other workbooks refer to that list before allowing the appropriate access level. That way it is only 1 list to update.

I have googled and tried various things but cant find a suitable method to achieve this. Can anyone help?

 

[MoshiM]

Hi,

Hoping someone can point me in the right direction with this one.........thanks in advance.

So as part of the company structure individuals have different access to spreadsheets based on their username.
Some can only view certain sheets, some read only, some have full access etc etc.

At present this is controlled through code using If Environ("username") = X then do something or if Environ("username") = y then do something different.

The problem with this is that it is in around 30 different workbooks. As the staff within the company change it means having to go back and add or remove them from each of the individual workbooks.

What I would like to do is keep one central list in a separate workbook which I can update as necessary and have each of the other workbooks refer to that list before allowing the appropriate access level. That way it is only 1 list to update.

I have googled and tried various things but cant find a suitable method to achieve this. Can anyone help?

Are your files saved to OneDrive?

I'm not to savvy with formulas so I don't know if there is one to do this nor have I ever done something with central workbook linking, howevere there are two methods that come to mind.

Option 1 :

Assuming everyone is using Windows then you can use a bit of Web Scraping on a text file loaded to dropbox to check for authorized users.

This example is for checking if there is a new version of an excel file available on DropBox. The concept is the same, except that you would have to use more/different delimiters for the levels of access each person has. Dropbox link needs to be shared.

Private Function Update_Date() As Boolean


Dim Path As String, Update As Double, dd As Byte, WinHttpReq As Object, FileN As String, Update_Range As Range, _
URL As String, html As Object, STR_AR() As String, x As Byte, File_Type As String


Set Update_Range = Variable_Sheet.ListObjects("Saved_Variables"). _
    DataBodyRange.Find("Update", LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1)
    
File_Type = Update_Range.Offset(1, 0).Value2


FileN = "Date_Check.txt"


x = Application.Match(File_Type, Array("L", "D", "T"), 0) - 1 '3 different file types are managed


   [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  Mac Then
       
   [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL]  'if on windows
    
        URL = Replace("https://www.dropbox.com/s/7lwhmhul1pqxbf9/Date_Check.txt?dl=0", _
            "www.dropbox.com", "dl.dropboxusercontent.com")'this text file has the version numbers of the most recently uploaded file
            
        Set html = CreateObject("htmlFile")
        
        Set WinHttpReq = CreateObject("MSXML2.XMLHTTP")
            
            With WinHttpReq
            
                .Open "GET", URL, False ' False means that it has to make the connection before moving on
                .send         
    
                html.Body.innerHTML = .responseText
            
            End With
            
            If Round(Update_Range.Value2, 10) < Round(Split(html.Body.FirstChild.Data, ",")(x), 10) Then Update_Date = True'checking version
            'Array elements in order [L,D,T]
    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  

Exit Function

No_Query:

    MsgBox "Check for workbook update failed" 
       
End Function

Option 2:

Alternatively if everyone has access to Power Query [Note: I've never used Power Query for MAC] then you can Query the Central Workbook for different tables assuming users have access to it through your network [like a shared OneDrive Folder?] and load each of them to a hidden sheet in all 30 of your workbooks. Then you can use a vba Function like:

Function Allow_Access(Table_Name As String) as boolean

dim Allowed as range

set Allowed=Hidden_Sheet.listobjects(Table_Name).databodyrange'hidden sheet is the sheet that holds the tables with different levels of access 

if not allowed.find(Environ("UserProfile"), LookIn:=xlValues, LookAt:=xlWhole) is nothing then Allow_Access=True 'if it is found within the table

end Function

The above function can be edited for a hierarchy of access if needed ie: if found in table 3 then level 3 access is given