Windows validation

Topic

New Reply

Hi
Is it possible to use windows validation for users to grant access to individual spreadsheets in a common workbook

Reply | Quote

Replies

This is the closest I found on Windows Authentication. Tried it but it needs some tweaking

http://vbnet.mvps.org/index.html?code/network/acceptsecuritycontext.htm

There is an interesting hack at http://datapigtechnologies.com/blog/index.php/hack-windows-authentication-for-your-applications/ using permissions to a share point folder but I don’t see why this could not be applied with permissions to a shared network folder as well

HTH,
Maud

Reply | Quote

Phil,

I don’t know what you mean by Windows Validation but you can do it with a little VBA as long as each user has their own logon account.
You just need to check the Environment Variable: UserName then unprotect the appropriate sheet and make sure you protect it on close or reprotect all sheets on open. BTW: You could also accomplish this by giving each sheet a different password. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Phil,

Here is some sample code:

In a module:

Code:

Option Explicit

Sub Auto_Open()

   Dim zUserID  As String
   
   zUserID = Environ("UserName")
   
   '*** Add a Case statement for each UserName and the appropriate Sheet
   '*** Unprotect command with Password - I'd recomment All Upper Case PWs
   Select Case zUserID
         Case "Bruce"
             Sheets("Sheet2").Unprotect "TEST"
         Case Else
            MsgBox "The User Named: " & zUserID & vbCrLf & _
                   "Is not Authorized to use this Workbook.", _
                   vbOKOnly + vbCritical, _
                   "UnAuthorized Access Attempt"
            Application.Quit
   End Select  'Case zUserId
End Sub

In the Workbook Object:

Code:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

   Dim zUserID  As String
   
   zUserID = Environ("UserName")
   
   '*** Add a Case statement for each UserName and the appropriate Sheet
   '*** Protect command
   '*** Note: The password must be set manually once and the workbook saved!
   Select Case zUserID
         Case "Bruce"
             Sheets("Sheet2").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
   End Select  'Case zUserId

End Sub

Notes:

[*]In the attached sheet you’ll need to get into the VBA and change the UserName “Bruce” to your UserName before you can test it. Change it in BOTH places! In you production code you should a password on the VBA module and select the Hide Code option to keep the curious from finding your PWs.
[*]I placed the reprotect code in a Workbook_BeforeSave event since if they don’t save it nothing will have changed.
[*]Sheet1 is protected and the code does not unprotect it. Sheet2 will be unprotected when you change the UserNames in the code.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RG
Thanks for that it is pretty much what I was looking for.

Just one point I can’t unprotect Sheet1 with “TEST”!

Reply | Quote

Retiree hi
I am having trouble with the code.
I have in the Auto_Open module this :
Sub Auto_Open()

Dim zUserID As String

zUserID = Environ(“UserName”)

‘*** Add a Case statement for each UserName and the appropriate Sheet
‘*** Unprotect command with Password – I’d recomment All Upper Case PWs
‘Set Focus = Sheets(“Instructions”)

Select Case zUserID
Case “pgc”
Sheets(“Phil Carter”).Unprotect “hradmin”
Sheets(“Instructions”).Visible = xlSheetVisible
Sheets(“Phil Carter”).Unprotect “hradmin”
Case “dmw”
Sheets(“Dian Wilkinson”).Visible = xlSheetVisible
Sheets(“Instructions”).Visible = xlSheetVisible
Sheets(“Dian Wilkinson”).Unprotect “hradmin”

and in the Workbook this:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim zUserID As String

zUserID = Environ(“UserName”)

‘*** Add a Case statement for each UserName and the appropriate Sheet
‘*** Protect command
‘*** Note: The password must be set manually once and the workbook saved!

Select Case zUserID
Case “pgc”
Sheets(“Phil Carter”).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Case “dmw”
Sheets(“Dian Wilkinson”).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

I also have this code in the workbook to hide all sheets except “Instructions”:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Me.Worksheets
If ws.Name “Instructions” Then ws.Visible = xlSheetHidden
Next ws
Me.Save
End Sub

Can’t seem to get the zUserID recognised

Reply | Quote

Hi Phil

Re:
Select Case zUserID
Case “pgc”
Sheets(“Phil Carter”).Unprotect “hradmin”
Sheets(“Instructions”).Visible = xlSheetVisible
Sheets(“Phil Carter”).Unprotect “hradmin”
Case “dmw”
Sheets(“Dian Wilkinson”).Visible = xlSheetVisible
Sheets(“Instructions”).Visible = xlSheetVisible
Sheets(“Dian Wilkinson”).Unprotect “hradmin”
…

Perhaps it should be
Select Case zUserID
Case “pgc”
Sheets(“Phil Carter”).Visible = xlSheetVisible
Sheets(“Instructions”).Visible = xlSheetVisible
Sheets(“Phil Carter”).Unprotect “hradmin”
Case “dmw”
Sheets(“Dian Wilkinson”).Visible = xlSheetVisible
Sheets(“Instructions”).Visible = xlSheetVisible
Sheets(“Dian Wilkinson”).Unprotect “hradmin”

zeddy

Reply | Quote

Hi Phil

It seems you have a couple of files where you have fifty sheets or so, one for each person.
And you are trying to control access for each person.

Maybe you should rethink this, and give each person their own workbook.
The advantages include
1. the person doesn’t need to load such a large file containing data from 49 others
2. the person could work with their own data without interfering with other users read-write access
3. you don’t need to use shared workbooks with all the complications that arise
4. it is easier to restrict access to a datafile for that person only (plus admin users of course)
5. it is easy to generate a new datafile for a new user rather than adding more sheets to existing files etc

Naturally, you would probably need a method to combine ALL the users data for analysis etc., but this is relatively easy.
You would need to use a simple file-naming convention for the datafiles e.g. incorporating the login UserID.
And you would need a Merge Tool.

see my replies in post
http://windowssecrets.com/forums/showthread//155757-Macro-for-fetching-data-from-dynamic-range-of-a-spreadsheet-to-another-spreadsheet
..for an example of combining datafiles for up to 100 users.

If you are using Excel version prior to 2007, let me know.

zeddy

Reply | Quote

RG’s code will grant access to anyone logged in under a specific account. However, if you will be asking for the Window’s Authenticated password (to match the user logged in) to access the sheet, then you will need to dig deeper than RG’s code.

Reply | Quote

Phil,

That was by design to show the code worked. The password to Sheet1 is TEST1, original huh? Glad it is what you needed. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Exactly what I need
Now the work starts, sheet 1 = general information, sheet = template for records. 2 files 50 sheets each all with different names!! Therefore 2 files with 50 case statements each!
Should keep me busy for ???????????????

Reply | Quote

Phil,

Hopefully the two workbooks use the same UserNames so you can copy that Case stmt!

Better yet you can set it up so that all the macros are in one workbook and that workbook opens the ones with the named sheets then you only have one code base to maintain. This is how I set up many of my projects. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi
Unfortunately they bare 2 separate workbooks with different user names.
I am interested in how you maintain or create and maintain a master workbook

Reply | Quote

Phil,

The basic setup is a Workbook with all the code (VBA) that runs a series of data workbooks which are all setup identically. I usually use this setup where there is data which is stored by year, e.g. restarts every year w/no need to look at the data across years. The user opens the master workbook which immediately opens a dialog to allow them to select the desired data workbook.
34454-Selector
I’ve actually developed a standard code workbook that has all the standard functions like selection dialog box as shown above, application specific menu, sort code, etc. It just takes a few customizations (usually setting variables) to adapt it to a new data setup. Of course you do have to rewrite the data specific code/menus but all the repetitive stuff is just reused and there is only one version of the code to maintain. When I make a change to one of the standard modules it’s just a matter of copying it into each project after it has been fully tested. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Phil,

I always force the userids to upper case then compare against upper case just to eliminate problems.
[noparse]zUserID = UCase(Environ(“UserName”))[/noparse]

I think you mis-typed in the first line of Case “pgc” shouldn’t that be like the 1st line of Case “dmw”? :cheers:

If I may ask why are you

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Phil,

I always force the userids to upper case then compare against upper case just to eliminate problems.
[noparse]zUserID = UCase(Environ(“UserName”))[/noparse]

I think you mis-typed in the first line of Case “pgc” shouldn’t that be like the 1st line of Case “dmw”? :cheers:

If I may ask why are you

You are right. Not sure why I changed it!

I still can’t get it to work, which is frustrating as I was hoping to roll it out today!!!

Reply | Quote

Phil,

Could you possibly post a test workbook? :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Sure see attached.
No privacy issues only contains staff names and my training records

Reply | Quote

Phil,

Works fine on my machine! With two minor changes.

1. Seems that Naming the Module containing the Auto_Open() code Auto_Open confused Excel. I changed the module name to Main.
34508-ProjectProperties
2. Please note the change to the first Case. You need to check against Upper Case values since you’re converting the UID to uppercase.

Code:

   Select Case zUserID
         Case "BRUCE"
             Sheets("Phil Carter").Visible = xlSheetVisible
             Sheets("Instructions").Visible = xlSheetVisible
             Sheets("Phil Carter").Unprotect "hradmin"
         Case "dmw"
             Sheets("Dian Wilkinson").Visible = xlSheetVisible
             Sheets("Instructions").Visible = xlSheetVisible
             Sheets("Dian Wilkinson").Unprotect "hradmin"
         Case "iea"
             Sheets("Irina Alexeichik").Visible = xlSheetVisible
             Sheets("Instructions").Visible = xlSheetVisible
             Sheets("Irina Alexeichik").Unprotect "hradmin"

34509-PhilCarterSheet

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Retiree
Thanks very much that fixed my access but now I have another problem that is network related. I need to get our IT guys give All Staff access to a restricted area on our network.

Forgot about that when I started.

Anyway, thanks very much for your help with this. I am not sure I would have ever realised that Excel was getting confused!

Reply | Quote

Phil,

I got to think about this from a manageability perspective and decided this mess needed to be simplified so here’s a better way IMHO.

Now this is just a test section but you’ll get the drift the nice part is you only have to maintain the array of users and the rest is automatic.

Code:

Option Explicit

Type tdCredentials
    zUID As String
    zUserName As String
End Type

Public zSelectedUser As String

Sub Auto_Open()

   Dim bAuthorized As Boolean
   Dim iCntr       As Integer
   Dim zUserID     As String
   Dim zUsers(30)  As tdCredentials
   
   zUsers(0).zUID = "BRUCE": zUsers(0).zUserName = "Phil Carter"
   zUsers(1).zUID = "DMW": zUsers(1).zUserName = "Dian Wilkinson"
   zUsers(2).zUID = "IEA": zUsers(2).zUserName = "Irina Alexeichik"
   zUsers(4).zUID = "RAA": zUsers(3).zUserName = "Rosemary Apperley"
   
   zUserID = UCase(Environ("UserName"))
   bAuthorized = False
   
   For iCntr = 0 To UBound(zUsers) - 1
      If zUserID = zUsers(iCntr).zUID Then
        bAuthorized = True
        zSelectedUser = zUsers(iCntr).zUserName
        Sheets(zUsers(iCntr).zUserName).Visible = xlSheetVisible
        Sheets("Instructions").Visible = xlSheetVisible
        Sheets(zUsers(iCntr).zUserName).Unprotect "hradmin"
      End If
   Next iCntr
   
End Sub

Code:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

   Dim zUserID  As String

   zUserID = UCase(Environ("UserName"))
   Sheets(zSelectedUser).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I’ll have a look at this shortly.
Did you see zeddy’s solution whereby a master file is used to extract data from “slave”(staff!) files as required. Working my way through this now

Reply | Quote

Phil,

I used to do something similar for a training database written entirely in Excel. Each Group taught several courses and then all groups had to be combined for the numbers at the Division Level. So I set up each group with its own course workbook (the structure was identical across Groups). Then when the Division Chief needed his numbers a Master sheet would be opened where the Auto Open code would go out and grab the data sheet from each group workbook and consolidate it into a single sheet. Pivot sheets & Graphs were used for the summaries along with some SumIf & DSum formulas. Worked quite well. It all depends on your needs and user work requirements. In this case only one person in each group did all the input so the way I set it up allowed all groups to update any time they wanted since they each had their own workbook. As always good preliminary analysis of the workflow and requirements followed by good design makes things easier in the long run. So you have two good solutions now it’s up to you to “Make the Call!” :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi Phil

I have adapted a version of my file specifically for you.
I just need to do a couple of further checks, and describe what’s been done.
I’ll post it here tomorrow – I’m on UK time here.

zeddy

Reply | Quote

Hi Phil

OK, here’s my version. Unzip the file to a folder of your choice.
The file is zipped because you can’t post .xlsb files here.
When you unzip this file, you’ll notice a significant file size reduction compared to your uploaded file.

My file works like this:

When the file is opened with macros enabled, the User’s login is checked (via an Excel formula) to see if it is in a hidden Team List of Users.

If the login ID is found in the list, the User’s name is ‘fetched’ from the Team List, and the corresponding data sheet for that User is unhidden for access by the User.

If the login ID is not found in this list, a message is displayed, and the file will be closed.
(To allow RG and others to check out this file, I have commented out that ‘close file’ vba line in the startup routine, which would actually close the file. You would need to uncomment that line yourself. For anyone checking this example, add your own login ID to the list!)
.
If the login ID is found, a check is made to see if there is a worksheet for the User. If there isn’t a sheet for the User, one will be created automatically from the hidden template sheet.

If the User’s login ID is one of a set of Admin User’s (defined in the startup routine), then all sheets are unhidden.

For an Admin User, a [StaffList] sheet is available , allowing new Users to be added (up to 100 in this sample).

This [StaffList] sheet also summarizes the number of Training Hours for all Users for each Event Type. (You need to copy the formulas down – I left this for you to do deliberately). The formulas use Custom Functions (e.g. =fetchConferenceDays(xx) ) rather than using Excel’s INDIRECT function (since the latter will give an error when dealing with sheetnames containing an apostrophe e.g. like Glen O’Leary)

For an Admin User, the sheet [Lists] uses dynamic range names and automatic sorting, for dropdown lists used on the Users own sheets. I have removed all of the sheet code from each User’s sheet as I believe these are not appropriate or required.

If you have any questions, please ask. Let me know what you think. Personally, I still believe the better way is to have separate datafiles for the Users.

zeddy

Reply | Quote

Richard hi
I have had time now to test
Why are references for Conference, Online and Seminar like this =fetchConferenceDays(F16) and yet the remaining are like this =fetchEventTypeDays($F16,K$15)?

I have entered some data in Dian Wlikinsons sheet, which toals OK but doesn’t update the Staff List

34608-dmwRecord
34609-dmwStaffList

Reply | Quote

Zeddy thanks for that
Unfortunately I am in meetings all day today and out of the office until Wednesday so probably won’t be able to do anything with this until Thursday.
Will be in touch

Reply | Quote

Hi Phil

The ‘different references’ are to show you different methods for gettting the Total duration days, based on the eventType, for a specified Staffname (i.e. sheet).

For example, as in cell [J17] of my file [rz1-StaffTraingingRecords.xlsb] we can use this:
=SUMIF(INDIRECT(“‘”&$F17&”‘!$C:$C”),J$15,INDIRECT(“‘”&$F17&”‘!$F:$F”))

..where F17 is the corresponding staffname (which is also the sheet name for that person), $C:$C refers to the Event Type column and $F:$F refers to the Duration (days) on the data sheets. But you can’t use this INDIRECT type of formula if your names (and therefore corresponding sheet names) have apostrophe’s e.g. as in Glen O’Leary.

So, using a custom UDF (User definedFunction) we can have
=fetchConferenceDays(staffname)
=fetchOnlineDays(staffname)
=fetchSeminarDays(staffname)
These custom functions define the event type directly in the vba code for the corresponding vba function ie.
zType = “Conference” ‘training event type
zType = “Online”
zType = “Seminar”

So, a more efficient method is to define a custom function where you specify the staffname (i.e sheetname) as well as the Event Type, as in
=fetchEventTypeDays(staffname,event type)

So we can use
=fetchEventTypeDays($F16,L$15)
(note use of the $ to ‘lock’ the column F (staffname column), and the $ to ‘lock’ the row 15 (which is the header row for the event type i.e has the description for the event type)

So you can use =fetchEventTypeDays(staffname,event type) for all event types rather than define them separately.

Re: I have entered some data in Dian Wlikinsons sheet, which toals OK but doesn’t update the Staff List

>After the workbook is saved by Dian, and opened by an Admin User, the Staff List sheet will show the results.
If Dian (or any non-Admin User) opens the workbook, they will not see the [StaffList] sheet. They can adds entries, then save and close the workbook. When an Admin User then opens the workbook, the [StaffList] sheet will be made visible, and it will show the correct values.

But, to allow you to see the results ‘on the fly’ after any User’s sheet is manually updated, I have amended the file to include
Application.CalculateFull
..whenever the sheet [StaffList] is activated, i.e when you switch back to the [StaffList] sheet. see attached .xlsm version of the file.

The reason you didn’t see Dian’s values updated is because the UDF’s are, by default, non-Volatile functions, so will only update the value shown in its cell if it is re-Entered! (They will be updated when the workbook is opened).

A Volatile function is re-calculated every time the sheet is re-calculated and is dynamic in responce to changes on the sheet. If the sheet doesn’t recalc, it doesn’t matter if you have Application.Volatile or not, the UDF will not execute.

Using Volatile sparingly gives you two benefits. Your worksheet will recalculate faster because it’s only recalculating what it needs to. And UDF’s are notoriously slow compared to built-in functions.

I guess that will open up a stack of responses from the other Forum members!

zeddy

Reply | Quote

Richard
Once again thanks very much.

I have found that when I open the workbook I only get the “Instructions” sheet and my sheet showing. Not sure why as you have set me as Admin and an instruction for Admin access to show all sheets. I have checked all the code and cannot find find out why the “unhide command for Admin users” doesn’t work.

I notice you have changed the file extension to *.xlsm. Are you recommending changing from the binary workbook extension *.xlsb?

Reply | Quote

Hi Phil

..I can’t upload .xlsb files to this site, so had to use .xlsm instead.
I recommend using binary .xlsb, so you can use Save As etc. etc.
As data is added, .xlsb will be a smaller file in the long run.

I will investigate the Admin issue and get back to you.
That will be tomorrow.

zeddy

Reply | Quote

Hi Phil

See updated file, for fixed Admin issue.

the Startup routine had:
zUserID = UCase(Environ(“UserName”)) ‘fetch login ID

..but later on, for Admin Users, it checked like this..
If zUserID = “pgc” Then ‘<< this should have been UPPERCASE
unhideAllSheets
End If

So the fix was:
If zUserID = "PGC" Then '<< v1.03; check UPPERCASE login ID
unhideAllSheets
End If

Should work for you now.

zeddy

Reply | Quote

Richard hi
I am having problems with this workbook. I distributed it to 3 staff and asked them to open and let me know what they saw. They all had all worksheets visible.

I have opened your rz3….xlsm file above and got this VBA error:
34750-UserIDFail

Can you let me know what is going on. As far as I can determine the code looks fine!

Reply | Quote

Richard hi
Did you manage to investigate the Admin issue at all?

Reply | Quote

Hi Phil

check the post#31

zeddy

Reply | Quote

Thanks Richard
Once changed works fine

Once again thanks for all your help. Far exceeded what I was expecting

Reply | Quote

Hi Phil

The error message is saying you don’t have a named cell called loginIDcell in that workbook you sent out.
Check in the workbook I supplied as to where it should be.

zeddy

Reply | Quote

Hi Richard
This was when I opened your file. Your name was in the loginIDcell;
34751-zeddyLogin

When I open my file, my name is in the loginIDcell so that seems to work OK. I have run some tests and the true/false is working for sheetnames.

As I said in previous the main problem is all staff being able to view all worksheets

Reply | Quote

Hi Phil

Two things to try:
1. Before sending the file to your Users, run the macro hideAllSheets, then save the file, then send that to your Users.
This is so that, if they don’t enable macros, then the file will be opened in the default state with only [Instructions] sheet showing.
2. Make sure your Users are not opening the file directly from within Outlook, i.e. if you are sending the file via email, make sure they save the file to a folder location before opening the file. Opening an Excel file directly from an attachment can cause a vba error on any random line of code!

zeddy

Reply | Quote

Richard hi
1 Saved and opened from saved location. Works fine
2 Hadn’t considered that but will remember for the future

Thanks agin

Reply | Quote

Richard hi again
I have come across an unexpected problem. The Area Managers need to see the sheets of their groups.
I have tried with this code;
If zUserId = WLR Then
Sheets(“Marc Ferguson”).Visible = True
Sheets(“Greg Williams”).Visible = True
Sheets(“Geoff Hallam”).Visible = True
Sheets(“Dian Wilkinson”).Visible = True
Sheets(“Phil Carter”).Visible = True
ElseIf zUserId = “BRA” Then
Sheets(“Anne Hofstra”).Visible = True
Sheets(“Keith Towl”).Visible = True
Sheets(“Gavin Tasker”).Visible = True
Sheets(“Jane Busby”).Visible = True
Sheets(“Shelli Turner”).Visible = True
Sheets(“Adrienne Woollard”).Visible = True
Sheets(“Jen Ferguson”).Visible = True

but all I get is the sheet at zUserID which is correct but ignores the rest!

Reply | Quote

Hi Phil

..perhaps it’s a typo in your code..
Try this:

Code:

'**********************************************
'CHECK FOR AREA MANAGER'S..
'**********************************************
On Error Resume Next

If zUserId = "WLR" Then
 Sheets("Marc Ferguson").Visible = True
 Sheets("Greg Williams").Visible = True
 Sheets("Geoff Hallam").Visible = True
 Sheets("Dian Wilkinson").Visible = True
 Sheets("Phil Carter").Visible = True
End If
'----------------------------------------------
If zUserId = "BRA" Then
 Sheets("Anne Hofstra").Visible = True
 Sheets("Keith Towl").Visible = True
 Sheets("Gavin Tasker").Visible = True
 Sheets("Jane Busby").Visible = True
 Sheets("Shelli Turner").Visible = True
 Sheets("Adrienne Woollard").Visible = True
 Sheets("Jen Ferguson").Visible = True
End If
'----------------------------------------------

On Error GoTo 0

Let me know if this works for you.

Perhaps another way would be to colour-code the tabs for the different groups.
Then you could use something like:
If zUserId = “WLR” Then
unhideBrownSheets
End if

If zUserId = “BRA” Then
unhidePurpleSheets
End if

..see the routine Sub hideAllREDSheets() for example code etc etc.

zeddy

Reply | Quote

Richard hi
Couldn’t get either to work.
The first, again, just opened the users sheet and the second gave an error= “Undefined object”

I remember the Sub hideAllRedSheets() but couldn’t find it!

Reply | Quote

Hi Phil

We can fix this, but would need to see the exact code you have.
If you could make a copy of the workbook, delete most of the data and/or sheets to preserve the innocent, then post it back here, we could identify the problem, fix it, and tell you how to put the fix into your ‘live’ copy.
It might be as simple as one of the sheets you are referring to doesn’t exist, e.g. Sheets(“Adrienne Woollard”)

zeddy
zeddy

Reply | Quote

Hi Phil

..just to let you know, the code for Sub hideAllREDSheets() was in the vba code module named [modHide]
..you can see the module names in the vbe window (Alt [F11] ), Project Explorer (Ctrl [R] )
But here is an example of how to use tab colours in vba code:

Code:

For Each zSht In ThisWorkbook.Worksheets      'loop through all worksheets
'****************************************************
'RUN FOLLOWING ROUTINE IF SHEET TAB COLOUR IS GREEN..
'****************************************************
If zSht.Tab.Color = RGB(0, 100, 0) Then           'RGB code for green tab

'do this

End If
'****************************************************
'RUN FOLLOWING ROUTINE IF SHEET TAB COLOUR IS BLUE..
'****************************************************
If zSht.Tab.Color = RGB(0, 0, 100) Then            'RGB code for blue tab

'enter name of subroutine for Blue tab sheets here..

End If
'****************************************************
Next zSheet                                     'process next worksheet

zeddy

Reply | Quote

Richard hi
Zipped cleansed file attached

Reply | Quote

Phil,

You’re missing a lot of quotes in the UserIDs:

Code:

On Error Resume Next

    If zUserId = WLR Then
        Sheets("Marc Ferguson").Visible = True
        Sheets("Greg Williams").Visible = True
        Sheets("Geoff Hallam").Visible = True
        Sheets("Dian Wilkinson").Visible = True
        Sheets("Phil Carter").Visible = True
    End If
    If zUserId = "BRA" Then
        Sheets("Anne Hofstra").Visible = True
        Sheets("Keith Towl").Visible = True
        Sheets("Gavin Tasker").Visible = True
        Sheets("Jane Busby").Visible = True
        Sheets("Shelli Turner").Visible = True
        Sheets("Adrienne Woollard").Visible = True
        Sheets("Jen Ferguson").Visible = True
    End If
    If zUserId = DMW Then
        Sheets("Kay Brown").Visible = True
        Sheets("Claire Moon").Visible = True
        Sheets("Nisha Yakub").Visible = True
    End If
    If zUserId = GDO Then
        Sheets("Tejinder Singh").Visible = True
        Sheets("Paras Kumar").Visible = True
    End If
    If zUserId = "AJH" Then
        Sheets("Don Arnold").Visible = True
        Sheets("Trevor Moran").Visible = True
        Sheets("Robert Glass").Visible = True
        Sheets("Jed Gibson").Visible = True
        Sheets("Ian Laban").Visible = True
        Sheets("Michael Vercoe").Visible = True
        Sheets("Olento Peauafi").Visible = True
    End If
    If zUserId = KRT Then
        Sheets("Paul Nichols").Visible = True
    End If
    If zUserId = GJT Then
        Sheets("Claire Jongenelen").Visible = True
    End If
    If zUserId = JKF Then
        Sheets("Rogini Royeppen").Visible = True
        Sheets("Erin Forrest").Visible = True
    End If
    If zUserId = JLB Then
        Sheets("Sharon Thomas").Visible = True
    End If
    If zUserId = SJT Then
        Sheets("Erin Retter").Visible = True
        Sheets("Shiva Sami").Visible = True
        Sheets("Gopy Sundararajah").Visible = True
    End If
    If zUserId = ADW Then
        Sheets("David Sidwell").Visible = True
        Sheets("Ruth Porter").Visible = True
        Sheets("Carolyn Osborne").Visible = True
End If

All of the 3 character USERID’s need to be quoted. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Phil,

It took me a while to hack in so I could add myself to the StaffList but I got there then tested this code for the Startup()

Code:

'***********************************************'*******************************************
' Staff Training Tool                           v1.00 AUG 2013                 TOP OF MODULE
'
' FILE : [StaffTrainingRecords.xlsb]            last updated: 31-JUL-2013       by: RZ
'***********************************************'*******************************************


Sub startUp()                                   'v1.00

Application.Calculation = xlCalculationAutomatic 'make sure calcs are ON

zUserId = UCase(Environ("UserName"))            'fetch login ID
[loginIDcell] = zUserId                         'place loginID in named cell

'**********************************************
'CHECK IF LOGIN ID IS IN STAFF LIST..
'**********************************************
zSheet = [staffSheetname]                       'fetch computed value from named cell
If zSheet = "" Then                             'this User's loginID is NOT in Staff List
'display message..
saywhat = "The LoginID: " & zUserId & " is not listed as " & vbCr   'message text
saywhat = saywhat & "an Authorized user of this workbook."          'add text
saywhat = saywhat & vbCr & vbCr                                     'add 2 lines
saywhat = saywhat & "Please check with your Administrator."         'add text
saywhat = saywhat & vbCr & vbCr                                     'add 2 lines
btns = vbOKOnly + vbCritical                    'message box buttons
boxtitle = "UnAuthorized Access Attempt"        'message box heading
answer = MsgBox(saywhat, btns, boxtitle)        'display message box

ThisWorkbook.Close savechanges:=False           'close THIS workbook

Exit Sub                                        'nothing else to do
End If                                          'end if test for missing loginID in List
'**********************************************
'UNHIDE USER'S SHEET..
'**********************************************
On Error GoTo createSheet                       'set error trap for next line
Sheets(zSheet).Visible = True                   'unhide User's sheet
On Error GoTo 0                                 'reset error trap
'**********************************************
'UNLOCK USER'S SHEET..
'**********************************************

Sheets(zSheet).Unprotect "hradmin"

'**********************************************
'CHECK FOR ADMIN USER..
'**********************************************
'you can add as many Admin users here as wanted
'Or "BRA" Or "AJH" Or "KRT" Or "GJT" Or "JLB" Or "SJT" Or "ADW" Or "JKF" Or "WLR" Or "MDF" Or "GDO" Or "DMW"
'**********************************************
'CHECK FOR AREA MANAGER'S..
'**********************************************
On Error Resume Next
  Select Case zUserId
    Case "WLR"
        Sheets("Marc Ferguson").Visible = True
        Sheets("Greg Williams").Visible = True
        Sheets("Geoff Hallam").Visible = True
        Sheets("Dian Wilkinson").Visible = True
        Sheets("Phil Carter").Visible = True
    
    Case "BRA"
        Sheets("Anne Hofstra").Visible = True
        Sheets("Keith Towl").Visible = True
        Sheets("Gavin Tasker").Visible = True
        Sheets("Jane Busby").Visible = True
        Sheets("Shelli Turner").Visible = True
        Sheets("Adrienne Woollard").Visible = True
        Sheets("Jen Ferguson").Visible = True
    
    Case "DMW"
        Sheets("Kay Brown").Visible = True
        Sheets("Claire Moon").Visible = True
        Sheets("Nisha Yakub").Visible = True
    
    Case "GDO"
        Sheets("Tejinder Singh").Visible = True
        Sheets("Paras Kumar").Visible = True
    
    Case "AJH"
        Sheets("Don Arnold").Visible = True
        Sheets("Trevor Moran").Visible = True
        Sheets("Robert Glass").Visible = True
        Sheets("Jed Gibson").Visible = True
        Sheets("Ian Laban").Visible = True
        Sheets("Michael Vercoe").Visible = True
        Sheets("Olento Peauafi").Visible = True
    
    Case "KRT"
        Sheets("Paul Nichols").Visible = True
    
    Case "GJT"
        Sheets("Claire Jongenelen").Visible = True
    
    Case "JKF"
        Sheets("Rogini Royeppen").Visible = True
        Sheets("Erin Forrest").Visible = True
    
    Case "JLB"
        Sheets("Sharon Thomas").Visible = True
    
    Case "SJT"
        Sheets("Erin Retter").Visible = True
        Sheets("Shiva Sami").Visible = True
        Sheets("Gopy Sundararajah").Visible = True
    
    Case "ADW"
        Sheets("David Sidwell").Visible = True
        Sheets("Ruth Porter").Visible = True
        Sheets("Carolyn Osborne").Visible = True

    Case "RICHARD", "HRADMIN", "PGC", "BRUCE"                    '<<REMOVE THIS OR CHANGE AS APPROPRIATE
        unhideAllSheets
        
   End Select
'**********************************************


Exit Sub                                        'all done; nothing else to do
'------------------------------------------------------------
'vba continues here if the User's login ID is found in the
'Staff List, but no sheet exists for this authorized User..
createSheet:

Sheets("Blank Staff").Visible = True            'unhide sheet before copying it
Sheets("Blank Staff").Copy after:=Sheets("Instructions")    'copy template sheet
ActiveSheet.Name = zSheet                                   'and rename it with User name
Sheets("Blank Staff").Visible = xlVeryHidden    'hide sheet after copying
Resume Next                                     'continue processing above

End Sub    'StartUp()
'***********************************************'*******************************************

It works just fine and is much more efficient than repeated IF tests.

I used this modification for a test:

Code:

    
    Case "ADW", "BRUCE"
        Sheets("David Sidwell").Visible = True
        Sheets("Ruth Porter").Visible = True
        Sheets("Carolyn Osborne").Visible = True

Results:
34833-PhilTest
HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Richard hi
Doh!! Didn’t even see the lack of quotes, “Woods” and “trees”!

I changed the code to use the Case statements and works fine.

Thanks very much once again

Reply | Quote

Hi RG

Top marks for identifying and resolving the issue.
(I was away – you got there before me)

Phil:
As RG says, you must ‘wrap’ each of the zUserId’s being checked, in double quotes, like this “WLR”
If you don’t, vba assumes you are checking against a variable, e.g. a variable named WLR, which is empty.

And as RG says, with so many Area Managers, it is probably better to use the vba Case command rather than so many If .. End If checks (although it is perfectly acceptable to do that).

I have attached an updated copy of your submitted file.
(It is a zip file containing the .xlsb file)

zeddy

Reply | Quote

Hi Phil

What is it with my system!
Is it because I’m in a different time zone or what!
By the time I reply, you’ve already done it!

zeddy

Reply | Quote

Richard hi
Your working late! Oh I guess it is Sunday. I start work at 6:30 am
I thanked RG for the advice he gave but I must thank you also.

Reply | Quote

As RG says, you must ‘wrap’ each of the zUserId’s being checked, in double quotes, like this “WLR”
If you don’t, vba assumes you are checking against a variable, e.g. a variable named WLR, which is empty.
zeddy

Zeddy,

That’s why I always like to DIM all my variables and use an Option Explicit command at the top. The VBE would then have caught those unquoted strings as undeclared variables…I’m just sayin’ :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

You are right. Option Explicit would have caught that.
I don’t mind admitting when I’m wrong.
I’m proud of my humility.
..but I can’t help thinking that Dimming your variables is like making an artist choose all the colours first before he starts a painting. Or choosing all your words before writing a story. Or selecting a cast before you’ve read the film script. Or..
OK, you win this time.

zeddy

Reply | Quote