Restrict view to only user with specific department.

[bijayche]

Hello. I have been trying to find this solution in excel but not sure if this is available. Is it possible to restrict users to view only one tab in a workbook. My workbook has 30 sheets and each has department specific data. I want to restrict users to be able to view only their department data. Can this be done? Now I am sending individual email to atleast 30 people is there a way I can send just one email and restrict user access to the tabs with different data info?

 

[Skybluekid]

One thought would be to have vba code to activate when the workbook opens up, it would ask for a password. When it is entered it will only show the relevant sheet.

 

[Candyman8019]

This snippet of code will return the department of the current user. You can use that to display applicable worksheets.

Private Sub ADDepartment()
UserName = Environ("UserName")

Set rootDSE = GetObject("LDAP://RootDSE")
Base = "<LDAP://" & rootDSE.Get("defaultNamingContext") & ">"
'filter on user objects with the given account name
fltr = "(&(objectClass=user)(objectCategory=Person)" & _
        "(sAMAccountName=" & UserName & "))"
'add other attributes according to your requirements
attr = "distinguishedName,sAMAccountName,department"
scope = "subtree"

Set conn = CreateObject("ADODB.Connection")
conn.Provider = "ADsDSOObject"
conn.Open "Active Directory Provider"

Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = Base & ";" & fltr & ";" & attr & ";" & scope

Set rs = cmd.Execute
Do Until rs.EOF
 ' MsgBox rs.Fields("distinguishedName").Value
  MsgBox rs.Fields("department").Value
  rs.MoveNext
Loop
rs.Close

conn.Close
End Sub

 

[Candyman8019]

Taking this a step closer to your original question...try adding this script to 'ThisWorkbook'. This will hide all but sheet 1. You will need to modify your departments and identify what sheets you want visible for each. I like to Hide all sheets on workbook open and workbook close, in case the workbook crashed and left rogue sheets visible.

Private Sub workbook_open()
Dim Dept As String
Dim Sht As Worksheet

'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 <> "Sheet1" Then Sht.Visible = xlSheetVeryHidden
Next

'determine department of current user
UserName = Environ("UserName")

Set rootDSE = GetObject("LDAP://RootDSE")
Base = "<LDAP://" & rootDSE.Get("defaultNamingContext") & ">"
'filter on user objects with the given account name
fltr = "(&(objectClass=user)(objectCategory=Person)" & _
        "(sAMAccountName=" & UserName & "))"
'add other attributes according to your requirements
attr = "distinguishedName,sAMAccountName,department"
scope = "subtree"

Set conn = CreateObject("ADODB.Connection")
conn.Provider = "ADsDSOObject"
conn.Open "Active Directory Provider"

Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = Base & ";" & fltr & ";" & attr & ";" & scope

Set rs = cmd.Execute
Do Until rs.EOF
 Dept = rs.Fields("department").Value
  rs.MoveNext
Loop
rs.Close
conn.Close

'Display sheet applicable to department
Select Case Dept
    Case Is = "DataCenter Management"
        Sheet2.Visible = xlSheetVisible
    Case Is = "Deskside Support"
        Sheet3.Visible = xlSheetVisible
End Select

End Sub
Private Sub worksheet_beforeclose()
'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 <> "Sheet1" Then Sht.Visible = xlSheetVeryHidden
Next
End Sub

 

[Skybluekid]

Try the below. You need to place this on 'ThisWorkbook'

Private Sub Workbook_Open()
Dim Pass As String
Dim ws As Worksheet

Sheet1.Visible = xlSheetVeryHidden
Sheet2.Visible = xlSheetVeryHidden
Sheet3.Visible = xlSheetVeryHidden

Pass = InputBox("Please enter your password")
    
    Select Case Pass
        Case Is = "Happy"
            Sheet1.Visible = xlSheetVisible
        Case Is = "Sad"
            Sheet2.Visible = xlSheetVisible
        Case Is = "Up"
            Sheet3.Visible = xlSheetVisible
    End Select
        
End Sub

 

[bijayche]

One thought would be to have vba code to activate when the workbook opens up, it would ask for a password. When it is entered it will only show the relevant sheet.

Thankyou so much for responding. If i do this do I have to send the password to 30 users separately ?

 

[bijayche]

Taking this a step closer to your original question...try adding this script to 'ThisWorkbook'. This will hide all but sheet 1. You will need to modify your departments and identify what sheets you want visible for each. I like to Hide all sheets on workbook open and workbook close, in case the workbook crashed and left rogue sheets visible.

Private Sub workbook_open()
Dim Dept As String
Dim Sht As Worksheet

'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 <> "Sheet1" Then Sht.Visible = xlSheetVeryHidden
Next

'determine department of current user
UserName = Environ("UserName")

Set rootDSE = GetObject("LDAP://RootDSE")
Base = "<LDAP://" & rootDSE.Get("defaultNamingContext") & ">"
'filter on user objects with the given account name
fltr = "(&(objectClass=user)(objectCategory=Person)" & _
        "(sAMAccountName=" & UserName & "))"
'add other attributes according to your requirements
attr = "distinguishedName,sAMAccountName,department"
scope = "subtree"

Set conn = CreateObject("ADODB.Connection")
conn.Provider = "ADsDSOObject"
conn.Open "Active Directory Provider"

Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = Base & ";" & fltr & ";" & attr & ";" & scope

Set rs = cmd.Execute
Do Until rs.EOF
 Dept = rs.Fields("department").Value
  rs.MoveNext
Loop
rs.Close
conn.Close

'Display sheet applicable to department
Select Case Dept
    Case Is = "DataCenter Management"
        Sheet2.Visible = xlSheetVisible
    Case Is = "Deskside Support"
        Sheet3.Visible = xlSheetVisible
End Select

End Sub
Private Sub worksheet_beforeclose()
'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 <> "Sheet1" Then Sht.Visible = xlSheetVeryHidden
Next
End Sub

Thank you so much. I am quite a rookie in Excel, and I do not know how to use VBA code. I have a few doubts. Are the lines in green your description? And should I use the email ID of the user as the department to allow them to view the sheet?

 

[Skybluekid]

Possible my lack of understanding. You would only need to send the password to each user once. So the user who access' Sheet 1 would have their password and they could access Sheet 1 only.

 

[Candyman8019]

to use this code, past it as-is into 'ThisWorkbook' as shown in the screenshot below:

There is no need for the user to enter anything. The code will find out what department they're in by how your organization has defined them in ActiveDirectory. All you need to do is addadditional cases circled above, for each department.

 

[Candyman8019]

This may be easier for you. I've moved all of the relevant details to a "config" tab so you shouldn't need to go into the developer tab to touch the VBA.

Here's a link to the doc: department test2.xlsm

On the Config tab, you make the list of 'departments' and which sheets they need access to. If they need more than one sheet, make more than one line for that department.

Since the config sheet will be hidden by default you'll need a way to access that. On the config sheet is an Admin password field. The default password is 'iwantin'. You can set this to whatever you want. NOTE: the font is white so nobody can look over your shoulder and see it.

When you enter the Admin password in the Home sheet, it will unhide ALL sheets, including the config tab.