environ("username") alternative

gordsky

New Member
Joined
Jun 2, 2016
Messages
10
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

Active Member
Joined
Jan 31, 2018
Messages
274
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.

Code:
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:
Code:
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
 
Last edited:

Forum statistics

Threads
1,078,451
Messages
5,340,369
Members
399,371
Latest member
wilbot

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top