Hide Worksheets dependant on domain name

[Jason87]

Good Morning,

I am am a very basic user of Excel but am trying to complete something a little more complex and am struggling quite a lot with this.

What I would like to do is have one Excel Workbook with Multiple worksheets, and then have only certain users access certain worksheets using domain login.

below is a sort of idea what I want to do.

Worksheet - Total Revenue
Worksheet - Office A
Worksheet - Office B
Worksheet - Office C


User - Joe.Bloggs logs on and can see

Worksheet - Total Revenue - Shown
Worksheet - Office A - Shown
Worksheet - Office B - Shown
Worksheet - Office C - Shown

User - Shaun Davis logs on and can see

Worksheet - Total Revenue - Hidden
Worksheet - Office A - Hidden
Worksheet - Office B - Shown
Worksheet - Office C - Hidden

I have never used VBA before so am a complete Noob. Is what I am trying to accomplish above achievable.

All help would be very much appreciated.

[SamT]

First, there are three Sheet.Visible conditions; Visible, hidden, and VeryHidden. Users can unhide Hidden sheets with the Excel Menu, but not VeryHidden.

This is a simple to understand algorithm:

Option Explicit

Private Sub Workbook_Open()
  ShowHideSheets
End Sub

Private Sub ShowHideSheets()
Dim Sht As Worksheet
Dim AlwaysShow As String
AlwaysShow = "Main, Sheet1, Don't Hide Me"
'List the names of all sheets that should never be hidden in AlwaysShow.
'there must always be at least one sheet visible in the Workbook.

  
  For Each Sht In Sheets
    If Not InStr(AlwaysShow, Sht.Name) Then
      Sht.Visible = xlVeryHidden
    End If
  Next
  
Select Case Application.UserName
  Case "Administrator": ShowForAll
  Case "Harry": ShowForHarry
  Case "Jane": ShowForJane
  Case "Bill": ShowForBill
End Select
End Sub

Private Sub ShowForAll()
  Sheets("Total Revenue").Visible = xlVisible
  Sheets("Office A").Visible = xlVisible
  Sheets("Office B").Visible = xlVisible
  Sheets("Office C").Visible = xlVisible

End Sub

Private Sub ShowForHarry()
  'Sheets("Total Revenue").Visible = xlVisible
  Sheets("Office A").Visible = xlVisible
  Sheets("Office B").Visible = xlVisible
  Sheets("Office C").Visible = xlVisible
End Sub

Private Sub ShowForJane()
  Sheets("Total Revenue").Visible = xlVisible
  'Sheets("Office A").Visible = xlVisible
  Sheets("Office B").Visible = xlVisible
  'Sheets("Office C").Visible = xlVisible
End Sub

Private Sub ShowForBill()
  Sheets("Total Revenue").Visible = xlVisible
  'Sheets("Office A").Visible = xlVisible
  'Sheets("Office B").Visible = xlVisible
  Sheets("Office C").Visible = xlVisible
End Sub

The Workbook_Open sub runs every time the book is opened. It merely runs the ShowHide Sub.

The ShowHide Sub first, hides all the worksheets, then, depending on the login UserName, calls one of the ShowForUser-Name Subs.

Sub SHowAll is the Template that you copy for each User's ShowFor sub.

All the ShowFor-UserName Subs are identical to the ShowForAl Sub, that way you can copy it for each user and change the "Name" to suit. Note that the "Sheets("Sheet-name").Visible" lines that are commented out will not be shown to the User

The KeyWord "Private" keeps those Subs out of the Macro list in the Excel Menus

[Jason87]

Thank you very much for your reply, I will give the above a go. Just so I put it in the correct place can you confirm I am placing this in the correct place.

Right click Sheet 1 > View code > Right click This Workbook > Insert Module > enter in the details.

[SamT]

Right click Sheet 1 > View code > Right click This Workbook > Insert Module > enter in the details.

Not quite

Right click any sheet > View code > DoubleClick ThisWorkbook > Paste the above Algorithm > enter in the details, which will make it real code.

[Jason87]

Great I will give that a go now

[Paul_Hossler]

These things a tendency to grow over time - new users, more worksheets, changed worksheets, removed users, etc.

I'd suggest a little 'data base' worksheet that can be more easily maintained

Capture.JPG


For 'Admin' you can replace that with your own UserID and when you open it you can see all the worksheets, including 'Users'

You can .Protect the Intro worksheet if you want, or make it really pretty and wow the boss

Option Explicit
Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    
    Set wsUsers = Worksheets("Users")
    Set rUsers = wsUsers.Cells(1, 1).CurrentRegion
    Set wsIntro = Worksheets("Intro")
    
    sUserID = Environ("UserName")
    sUserName = Application.UserName
    wsIntro.Shapes("IntroBox").TextFrame2.TextRange.Text = "Hello" & vbLf & sUserName
    
    ShowRightWorksheets
    
    wsIntro.Select
    
    Application.ScreenUpdating = True
    
End Sub

Option Explicit
Option Private Module
Public wsUsers As Worksheet, wsIntro As Worksheet
Public sUserID As String, sUserName As String
Public rUsers As Range

Sub ShowRightWorksheets()
    Dim iUser As Long, iWS As Long
    Dim ws As Worksheet
    'if user not in list, use USER=Unknown in col B
    iUser = 2
    On Error Resume Next
    iUser = Application.WorksheetFunction.Match(sUserID, rUsers.Rows(1), 0)
    On Error GoTo 0
    'find the WS row
    For Each ws In ActiveWorkbook.Worksheets
        iWS = 0
        On Error Resume Next
        iWS = Application.WorksheetFunction.Match(ws.Name, rUsers.Columns(1), 0)
        On Error GoTo 0
        
        'if the WS is in the list
        If iWS > 0 Then
            'if the row/col is empty then VeryHide the WS
            If Len(rUsers.Cells(iWS, iUser).Value) = 0 Then
                ws.Visible = xlSheetVeryHidden
            'otherwise show the WS
            Else
                ws.Visible = xlSheetVisible
            End If
        
        'if WS NOT in list, then VeryHide it
        Else
            ws.Visible = xlSheetVeryHidden
        End If
    Next
End Sub
'super secret hidden macro that you need to know the name of
'so that you can type it in with Alt-F8 - Run Macro
Sub SuperSecret()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next
End Sub

[Jason87]

First, there are three Sheet.Visible conditions; Visible, hidden, and VeryHidden. Users can unhide Hidden sheets with the Excel Menu, but not VeryHidden.

This is a simple to understand algorithm:

Option Explicit

Private Sub Workbook_Open()
  ShowHideSheets
End Sub

Private Sub ShowHideSheets()
Dim Sht As Worksheet
Dim AlwaysShow As String
AlwaysShow = "Main, Sheet1, Don't Hide Me"
'List the names of all sheets that should never be hidden in AlwaysShow.
'there must always be at least one sheet visible in the Workbook.

  
  For Each Sht In Sheets
    If Not InStr(AlwaysShow, Sht.Name) Then
      Sht.Visible = xlVeryHidden
    End If
  Next
  
Select Case Application.UserName
  Case "Administrator": ShowForAll
  Case "Harry": ShowForHarry
  Case "Jane": ShowForJane
  Case "Bill": ShowForBill
End Select
End Sub

Private Sub ShowForAll()
  Sheets("Total Revenue").Visible = xlVisible
  Sheets("Office A").Visible = xlVisible
  Sheets("Office B").Visible = xlVisible
  Sheets("Office C").Visible = xlVisible

End Sub

Private Sub ShowForHarry()
  'Sheets("Total Revenue").Visible = xlVisible
  Sheets("Office A").Visible = xlVisible
  Sheets("Office B").Visible = xlVisible
  Sheets("Office C").Visible = xlVisible
End Sub

Private Sub ShowForJane()
  Sheets("Total Revenue").Visible = xlVisible
  'Sheets("Office A").Visible = xlVisible
  Sheets("Office B").Visible = xlVisible
  'Sheets("Office C").Visible = xlVisible
End Sub

Private Sub ShowForBill()
  Sheets("Total Revenue").Visible = xlVisible
  'Sheets("Office A").Visible = xlVisible
  'Sheets("Office B").Visible = xlVisible
  Sheets("Office C").Visible = xlVisible
End Sub

The Workbook_Open sub runs every time the book is opened. It merely runs the ShowHide Sub.

The ShowHide Sub first, hides all the worksheets, then, depending on the login UserName, calls one of the ShowForUser-Name Subs.

Sub SHowAll is the Template that you copy for each User's ShowFor sub.

All the ShowFor-UserName Subs are identical to the ShowForAl Sub, that way you can copy it for each user and change the "Name" to suit. Note that the "Sheets("Sheet-name").Visible" lines that are commented out will not be shown to the User

The KeyWord "Private" keeps those Subs out of the Macro list in the Excel Menus

I have entered in the first bit of data and iam getting a Debug error as follows.

Private Sub Workbook_Open()
ShowHideSheets
End Sub

Private Sub ShowHideSheets()
Dim Sht As Worksheet
Dim AlwaysShow As String
AlwaysShow = "Total Revenue"
'List the names of all sheets that should never be hidden in AlwaysShow.
'there must always be at least one sheet visible in the Workbook.


For Each Sht In Sheets
If Not InStr(AlwaysShow, Sht.Name) Then
Sht.Visible = xlVeryHidden - Highlighted.

End If
Next

Select Case Application.UserName
Case "Administrator": ShowForAll
Case "Jason.Spring": ShowForJason
Case "Jane": ShowForJane
Case "Bill": ShowForBill
End Select
End Sub

Private Sub ShowForAll()
Sheets("Sheet1").Visible = xlVisible
Sheets("Office A").Visible = xlVisible
Sheets("Office B").Visible = xlVisible
Sheets("Office C").Visible = xlVisible

End Sub

Private Sub ShowForJason()
'Sheets("Total Revenue").Visible = xlVisible
Sheets("Sheet1").Visible = xlVisible
Sheets("Total Revenue").Visible = xlVisible

Debug error - Run-time error '1004';
Method 'Visible' of object '_Worksheet' failed

Have no idea what iam doing wrong. I added a sheet into my my workbook Sheet1 for that sheet to always be visible.

[mdmackillop]

If you have added a sheet called Sheet1 then add it to AlwaysShow as follows

AlwaysShow = "Total Revenue, Sheet1"

and change this line to include >0

If Not InStr(AlwaysShow, Sht.Name)>0 Then

[SamT]

I am coming to the conclusion that the VBA Compiler does not always interpret 0 and <> 0 as False and True.

I had no such problem on my computer.

[Paul_Hossler]

Personally, I think that this approach of hardcoding sheet names and user ID's in the macro will be a giant maintenance headache

A small WS database like in #6 will be IMHO a lot easier in the long run

[mdmackillop]

I am coming to the conclusion that the VBA Compiler does not always interpret 0 and <> 0 as False and True.
I had no such problem on my computer.

I agree. I saw nothing wrong but had to make the change for the code to run.

@ Jason
It might be useful to build in a password over-ride if you need to see admin views on a user's PC.

[Jason87]

So iam still struggling with the above as mentioned before complete noob at this. I have set up a User table as shown in one of the examples above. Sheets and Users added (Users just for testing being myself and a unknown).

However I still seem to be getting a error message as shown below.

The only thing that I have removed from the script that was posted above was with regards to a intro. Everything else is the same.


Error 1.jpgErro 2.PNGError 3.PNGScript.jpg

[Paul_Hossler]

Look at the attachment in #6

Some code (e.g. Workbook_Open) has to go into the ThisWorkbook module and some goes into a standard module

I put in an Intro sheet to make certain that there would always be a visible sheet

Capture.JPG

Capture1.JPG


If you're still stuck, post a SMALL representative sample workbook with sanitized data

[SamT]

For Each Sht In Sheets
If Not InStr(AlwaysShow, Sht.Name) Then
Sht.Visible = xlVeryHidden - Highlighted.

My bad, I got that olds-'imers disease; CRS

The Sheet .Visible settings are xlSheetVisible, xlSheetHidden, and xlSheetVeryHidden


I tried, but I could not get Pauls's example to work consistently enough on my Office XP computer to help you with your latest question. First, Match would fail because my UserName wasn't in the list. When I changed that, CurrentRegion failed because I changed the table. I just didn't feel like jumping thru the hoops to make a "Native" copy of his book on my computer.

If you need more help with my code offering, I'm here for you. I prefer it because it's all code and can be copied into any workbook with just the details of the code changed with no modifications to the workbook.

[Jason87]

Thank you so much for all your help. Once I placed the correct script segments into the correct locations (Easy once you know where) the script worked a treat and I can now limit access to certain sheets per user.

Just need to do a little more testing but looks like its all good.

[Jason87]

So I have one last question that you maybe able to help me with. The above table is now running great, however I have now run into a small problem. iam needing to upload my excel document into sharepoint which is fine. however Excel online doesn't support Macros, which is also not really a problem. The problem comes when said user edits the wookbook and save the excel doc. the next person to look at the excel spreadsheet or the SharePoint excel online screen gets a shown all the previous info.

How would I stop this happening so that upon closing the workbook it closes all the sheets back down apart from sheet 1 which has a bunch of instructions on.

[Paul_Hossler]

1. Add a BeforeClose event to ThisWorkbook

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    sUserID = "Unknown"
    ShowRightWorksheets
End Sub

Private Sub Workbook_Open()
    Set wsUsers = Worksheets("Users")
    Set rUsers = wsUsers.Cells(1, 1).CurrentRegion
    Set wsIntro = Worksheets("Sheet1")
    
    
    sUserID = Environ("UserName")
    sUserName = Application.UserName
    
    ShowRightWorksheets
    
    wsIntro.Select
    
End Sub

2. I removed the Intro idea, but there were a few compatibility changes to the other macro, so

Option Explicit
Option Private Module
Public wsUsers As Worksheet, wsIntro As Worksheet
Public sUserID As String, sUserName As String
Public rUsers As Range

Sub ShowRightWorksheets()
    Dim iUser As Long, iWS As Long
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    'if user not in list, use USER=Unknown in col B
    iUser = 2
    On Error Resume Next
    iUser = Application.WorksheetFunction.Match(sUserID, rUsers.Rows(1), 0)
    On Error GoTo 0
    'find the WS row
    For Each ws In ActiveWorkbook.Worksheets
        
        ws.Visible = xlSheetVisible
        
        iWS = 0
        On Error Resume Next
        iWS = Application.WorksheetFunction.Match(ws.Name, rUsers.Columns(1), 0)
        On Error GoTo 0
        
        'if the WS is in the list
        If iWS > 0 Then
            'if the row/col is empty then VeryHide the WS
            If Len(rUsers.Cells(iWS, iUser).Value) = 0 Then
                ws.Visible = xlSheetVeryHidden
            End If
        
        'if WS NOT in list, then VeryHide it
        Else
            ws.Visible = xlSheetVeryHidden
        End If
    Next
    Application.ScreenUpdating = True
End Sub

3. I made some changes to the User database worksheet to always have Sheet1 visible