PDA

View Full Version : Hide Worksheets dependant on domain name



Jason87
08-31-2017, 02:34 AM
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
08-31-2017, 06:09 AM
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
08-31-2017, 06:45 AM
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
08-31-2017, 06:50 AM
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
08-31-2017, 07:00 AM
Great I will give that a go now :)

Paul_Hossler
08-31-2017, 07:10 AM
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

20211


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
08-31-2017, 08:23 AM
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
08-31-2017, 10:00 AM
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
08-31-2017, 10:51 AM
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. :dunno

Paul_Hossler
08-31-2017, 10:57 AM
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
08-31-2017, 11:20 AM
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. :dunno
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
09-01-2017, 01:51 AM
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.


20221202222022320224

Paul_Hossler
09-01-2017, 06:01 AM
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

20226

20227


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

SamT
09-01-2017, 06:18 AM
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
09-01-2017, 06:24 AM
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
09-01-2017, 08:10 AM
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
09-01-2017, 11:08 AM
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