File Requiring Password entered multiple times

[PaulPlumridge]

Hello All,

I am having trouble with passwords hoping its a simple fix.

I have four sales teams of 5 people who each have their own commission sheet which are password protected to open.
The team leads sheet has links to cells in each of his team.

So when the team lead opens their file it is asking for his password and then the password for all other sheets so 6 different passwords.
Is there anyway to keep each file password protected, keep the links but only have to enter the one password for the team leads file

Thanks

 

[Col Delane]

I haven't thought this all the way through but may be worth exploring.

Rather than protect each sheet, you could protect the workbook (also on the Review/Changes menu, and which protects the "structure" and "windows" of the open workbook vs the file protection which restricts opening & editing)
Protecting the structure stops an unauthorised user from (among a few other things) adding/deleting and hiding/unhiding sheets.

So, I'm thinking you could:

1. upon opening/closing the workbook use hidden & secured VBA to:
   1. hide ALL but a cover sheet (Excel requires at least sheet in an open workbook to be visible at all times)
   2. apply Workbook Protection with a password that only the Team Lead knows (allowing that user to unprotect the workbook to view any sheet)
2. issue each sales user with a unique "proxy password" (not the same as the Workbook Protection password) that could be assessed by some more VBA code, and if valid:
   1. temporarily unprotect the workbook
   2. unhide the user's sheet, then
   3. reapply Workbook Protection

Note that due to the limitations of Excel's protection, this suggestion is not infallible . A savvy user can break passwords or interrupt the execution of VBA and gain access to the secured sheets/data.

 

[Michael M]

You could also look at a Select case Macro, which allows the team leader access to all pages and the salespeople only their own sheet

 

[Col Delane]

Hi again Michael,

Agreed, Select Case was the VBA construct I was thinking of to assess the user's password for validity and then specify their particular sheet as the one to unhide.

 

[PaulPlumridge]

Hi Guys,

Thanks for the suggestions, which if i am honest i don't understand fully.

The users of these files and ultimately the directors who oversee this are not excel savvy so any kind of advanced VBA solution is out of the question.

Also a macro isnt a solution as these are all seperate files with multiple sheets, not individual sheets on the one file.


The key thing is to stop anyone outside of the approved structure from opening the file, so again protecting sheets or workbooks isn't a solution.

Team of four - Each have a password to their own file
Team lead: Has password to his file and to the four in his team

Myself as accountant and Sales director have all passwords.

Is there any way to password lock each file to open but not have the team leads file ask for 5 passwords?

 

[ZVI]

This can be done in workbook of team lead via VBA code with embedded passwords for all linked files (team members workbooks). At loading of team lead workbook the code will update all linked cells without asking passwords.

 

[ZVI]

Here is the solution mentioned in my previous post.
The code for ThisWorkbook module of the team lead:

Option Explicit
' Put all the below code into ThisWorkbook module
' Structure of Sheets("Passwords"):
'  - column A is for the names of the team files, like Wb1.xlsx
'  - column B is for passwords of those files, like 123
 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Me.UpdateLinks = xlUpdateLinksNever
End Sub
 
Private Sub Workbook_Open()
' ZVI:2019-02-07 https://www.mrexcel.com/forum/excel-questions/1085583-file-requiring-password-entered-multiple-times.html
  Dim Lnk As Variant, WbName As String, PwdCell As Range, Pwd As String
  On Error Resume Next
  Application.ScreenUpdating = False
  For Each Lnk In Me.LinkSources(xlExcelLinks)
    WbName = Mid(Lnk, InStrRev(Lnk, "\") + 1)
    Set PwdCell = Sheets("Passwords").UsedRange.Columns(1).Find(WbName, LookIn:=xlValues, LookAt:=xlWhole, SearchFormat:=False)
    If PwdCell Is Nothing Then
      Workbooks.Open(Filename:=Lnk, UpdateLinks:=False, ReadOnly:=True).Close False
    Else
      Pwd = PwdCell.Offset(, 1).Value
      Err.Clear
      Workbooks.Open(Filename:=Lnk, UpdateLinks:=False, ReadOnly:=True, Password:=Pwd).Close False
      If Err Then Workbooks.Open(Filename:=Lnk, UpdateLinks:=False, ReadOnly:=True).Close False
    End If
  Next
  Me.Activate
  Application.ScreenUpdating = True
End Sub

Example of the sheet "Passwords" in the team lead workbook. That sheet can be hidden.

Book1
	A	B
1	Files	Password
2	Wb1.xlsb	Pwd1
3	Wb2.xlsb	Pwd2
4	Wb3.xlsb	Pwd3
5	Wb4.xlsb	Pwd4
Passwords

 

[RichardMGreen]

I haven't thought this all the way through but may be worth exploring.

Rather than protect each sheet, you could protect the workbook (also on the Review/Changes menu, and which protects the "structure" and "windows" of the open workbook vs the file protection which restricts opening & editing)
Protecting the structure stops an unauthorised user from (among a few other things) adding/deleting and hiding/unhiding sheets.

So, I'm thinking you could:

1. upon opening/closing the workbook use hidden & secured VBA to:
   1. hide ALL but a cover sheet (Excel requires at least sheet in an open workbook to be visible at all times)
   2. apply Workbook Protection with a password that only the Team Lead knows (allowing that user to unprotect the workbook to view any sheet)
2. issue each sales user with a unique "proxy password" (not the same as the Workbook Protection password) that could be assessed by some more VBA code, and if valid:
   1. temporarily unprotect the workbook
   2. unhide the user's sheet, then
   3. reapply Workbook Protection

Note that due to the limitations of Excel's protection, this suggestion is not infallible . A savvy user can break passwords or interrupt the execution of VBA and gain access to the secured sheets/data.

It's possible to disable the Ctrl-Break key-combo to stop users doing that, you could disable it at the start of the macro and then enable the key-combo at the end.
Not sure if that helps if the OP is going to use the code posted just above my post.