Hello. I have an excel file with information for 43 members of staff. 10 of the 3 members are managers. The excel file will have a main sheet, and then individual sheets for each of the 43 members. So, the file will have 44 worksheets/tabs. This file I would like to share with the members (i.e. it will be uploaded on a shared drive). However, I want that when the file is opened, the 10 managers are able to see all the 43 worksheets, but the remaining 33 members of staff are only able to see their own worksheet. Yes, I know excel is not the secured regarding preventing people seeing sensitive data, but this is not sensitive data as such (i.e. if a member of staff goes through the trouble of trying to see al the data on the file, it won’t be a big deal). I searched far and wide on the web and found a couple of VBA codes (by the way I am not a VBA expert. I just a copy and paste snippets of codes here and there, which means most times I do not actually know what some of the codes mean ) which are most close to what I want (codes pasted below and the issues). By the way, the users are on a network (work network).
The first code:
Two issues with the above code:
(1) The usernames are their email addresses which means any username longer than 31 characters cannot be used as the name of their individual sheets!
(2) I don’t know how it can be amended to allow certain users (the 10 managers I mentioned) to see all the sheets.
Second code:
(uNam is the login name in the network)
The above code I kind of liked because it would appear to be able to address the issue of the 31 characters limit. However, it does not appear to work. (i.e. it does even display my individual sheet when I tested it). So, not sure what’s gone wrong!
Also, I wonder how the above codes stop someone that is/or on the network but which username is not in the code! Your advice as to a better solution would be much appreciated. Thank you.
The first code:
VBA Code:
Private Sub Workbook_Open()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Sheets
If sht.Name <> "Main" Then
sht.Visible = xlSheetVeryHidden
End If
Next
Sheets(Environ("UserName")).Visible = True
End Sub
(1) The usernames are their email addresses which means any username longer than 31 characters cannot be used as the name of their individual sheets!
(2) I don’t know how it can be amended to allow certain users (the 10 managers I mentioned) to see all the sheets.
Second code:
(uNam is the login name in the network)
Code:
Private Sub Workbook_Open()
uNam = Environ("Username")
If uNam = "yyy" Then Sheets("Tabelle1").Visible = True
If uNam = "abc" Then Sheets("Tabelle2").Visible = True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Tabelle1").Visible = xlVeryHidden
Sheets("Tabelle2").Visible = xlVeryHidden
End Sub
The above code I kind of liked because it would appear to be able to address the issue of the 31 characters limit. However, it does not appear to work. (i.e. it does even display my individual sheet when I tested it). So, not sure what’s gone wrong!
Also, I wonder how the above codes stop someone that is/or on the network but which username is not in the code! Your advice as to a better solution would be much appreciated. Thank you.