Allowing individual sheets to be viewed by the authorised individual

danjuma

Active Member
Joined
Sep 21, 2008
Messages
251
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:
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
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)

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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Forum statistics

Threads
1,223,897
Messages
6,175,269
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top