Restricting specific user sheets

[Linki]

Hi all,

I've been trying all sorts of random solutions I could find online (as I am unable to do VBA myself).

I'm making a sick leave & vacation sheet for my department and my fellow colleagues should only be able to see their own sheet (about 15 sheets, one for each in the team).
There will then be 1 master sheet used for filing paychecks, that compiles the information from all the other sheets - only myself should be able to see this one.

Searching this forum, I am looking for a similar solution as to what was discussed in THIS THREAD (i.e., identifying which person is logged in, and have only that persons respective sheet visible and all others very hidden).

We all use O365 with our work e-mails, hopefully that could be of use in the VBA code as user identifier? I liked the solution that was mentioned with having a "config" sheet, which has a column with usernames (account email?) and columns with which sheet they should be able to access - that would make it easy for someone like me to access and adjust in case of new hires etc.

I want to avoid passworded sheets/files, as it would make it complicated to compile information from multiple passworded locations into a master sheet - as far as I've understood it.

Hope anyone is able to hold my hand and guide my way through this, I appreciate any help I can get, thank you in advance!

 

[Candyman8019]

As requested...

Private Sub workbook_open()
Dim email As String
Dim sht As Worksheet
Dim LastRow As Long

'hide all sheets...at least one sheet must remain visible.
'Use VeryHidden to ensure users cannot simply unhide other sheets without going into VBA.
For Each sht In Worksheets
    If sht.Name <> "Home" Then sht.Visible = xlSheetVeryHidden
Next

'determine email of current user
Dim objOutlook As New Outlook.Application
email = Trim(objOutlook.getnamespace("MAPI").currentuser.AddressEntry)

'Email = CreateObject("WScript.Shell").RegRead("HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Common\Identity\ADUserName")

LastRow = Sheet4.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

'Display sheet applicable to email
For x = 2 To LastRow
    If email = Sheet4.Range("A" & x).Value Then
        Worksheets(Sheet4.Range("B" & x).Value).Visible = True
    End If
Next x

End Sub

 

[Candyman8019]

Yup, that'll do it.

You can prevent them from changing the tab name by adding the following to each sheet:

private sub worksheet_selectionchange(byval target as range)
    if activesheet.name <> "{the name of the sheet here}" then
       activesheet.name = "{the name of the sheet}"
   end if
end sub

 

[ranman256]

if you name all sheets to their user ID, then this will hide them all but the 1 user.

put this code into Workbook_Open event
in spreadsheet, enter VBE (alt-F11)
in left pane (projects) dbl-click the ThisWorkbook object
in right pane (code side), paste the code.

when workbook opens the event will fire.

Private Sub Workbook_Open()
Dim vUser
Dim ws As Worksheet

vUser = getUserID()

For Each ws In Sheets
   ws.Visible = ws.Name = vUser
Next
Set ws = Nothing
End Sub

Public Function getUserID() As String
getUserID = Environ("Username")
End Function

 

[Herakles]

if you name all sheets to their user ID, then this will hide them all but the 1 user.

put this code into Workbook_Open event
in spreadsheet, enter VBE (alt-F11)
in left pane (projects) dbl-click the ThisWorkbook object
in right pane (code side), paste the code.

when workbook opens the event will fire.

Private Sub Workbook_Open()
Dim vUser
Dim ws As Worksheet

vUser = getUserID()

For Each ws In Sheets
   ws.Visible = ws.Name = vUser
Next
Set ws = Nothing
End Sub

Public Function getUserID() As String
getUserID = Environ("Username")
End Function

I would make them Very Hidden and hide the VBA Project as well.

 

[Candyman8019]

Hi Linki,

Give this a try...I have modified the previous example that you had mentioned, to use email address rather than department to display correct sheets. NOTE: The email addresses you specify on the config tab are validated against the logged in user's active directory account.

email tabs-iwantin.xlsm

1drv.ms

 

[Linki]

Hi Linki,

Give this a try...I have modified the previous example that you had mentioned, to use email address rather than department to display correct sheets. NOTE: The email addresses you specify on the config tab are validated against the logged in user's active directory account.

email tabs-iwantin.xlsm

1drv.ms

Hi!
When I enable the macro my excel freezes, every time - I have no idea why. Would it matter that the file is located on our company's onedrive?

 

[Linki]

if you name all sheets to their user ID, then this will hide them all but the 1 user.

put this code into Workbook_Open event
in spreadsheet, enter VBE (alt-F11)
in left pane (projects) dbl-click the ThisWorkbook object
in right pane (code side), paste the code.

when workbook opens the event will fire.

Private Sub Workbook_Open()
Dim vUser
Dim ws As Worksheet

vUser = getUserID()

For Each ws In Sheets
   ws.Visible = ws.Name = vUser
Next
Set ws = Nothing
End Sub

Public Function getUserID() As String
getUserID = Environ("Username")
End Function

Hi Ranman!
Thank you!
I attempted this one too. I had made 3 sheets.
1. My username
2. A colleagues
3. One named Test

It hid 1 + 2 randomly. I was also able to go in and unhide the sheets afterwards without objection which would be a bit too easy access. It doesnt have to be super secure, but a bit more than that would be great - if possible somehow

 

[Candyman8019]

OneDrive shouldn't matter. It may be getting hung up accessing your active directory. hold down the ESC key to stop the script from running. You should be able to step through it from there. Alternatively, I could add a switch to enable/disable the code to make it easier.
You may need to validate what your active directory fields are called. In my organization email is called 'mail' in active directory.

 

[Linki]

OneDrive shouldn't matter. It may be getting hung up accessing your active directory. hold down the ESC key to stop the script from running. You should be able to step through it from there. Alternatively, I could add a switch to enable/disable the code to make it easier.

Is it possible to make it grab it from the O365 account within its own environment? Or maybe thats what its already doing, apologies for being daft!
Just thinking if my colleagues would have to open this to submit their absences, if they would also know how to get "through" the script.