Multiple Password to open Excel Document

[pete333]

Hi

I have a large commissions spreadsheet that generates 1 sheet per employee... i then save each sheet to relevant employees shared drive and have allocated a password to each sheet for added security. The employee then accesses their folder online, opens doc and enters password to view.

....

I am wanting to have an alternate password that would also open the document, like a user password above and a master password for me or other managers. Can I have a single excel file opened with 2 different passwords?

Thanks

Peter

 

[Rhodie72]

I think that's possible with VBA. It does mean that each user's machine will need to allow VBA code to run if it has your digital signature on it.

 

[Jeffrey Mahoney]

I don't you can have more than one password to open Excel. But you can create your own password system. You could even create a password for each worksheet.

Using VBA, you could have xlVeryHidden worksheets. A macro could ask the user what the password is, then make the sheets visible.

If you provided the same password to open all the workbooks and then create a password for each individual, you would have your double protection

Jeff

 

[Jeffrey Mahoney]

Here is some code to hide and unhide sheets. You'll either need to create a form to ask the user for a password, or create a special cell on a sheet you don't hide that the user enters the password. Maybe give them a button or setup a worksheet_Change trigger to test for something entered in that cell.


This would go in the "Thisworkbook" module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Call HideAllSheets
End Sub

You would only use this to hide this once. I put the password for this workbook on Sheet3

Sub HidePasswordSheet()
  Sheets("Sheet3").Visible = xlVeryHidden
End Sub

Use this to change the password manually. You can access this sheet and all the cells using VBA.

Sub UnHidePasswordSheet()
  Sheets("Sheet3").Visible = xlSheetVisible
End Sub

Call this when the user entered the password correctly on a form or on a cell somewhere

Sub UnhideAllSheets()
  Dim Sht As Worksheet
  For Each Sht In ThisWorkbook.Worksheets
    If Sht.Name <> "Sheet3" Then
      Sht.Visible = xlSheetVisible
    End If
  Next Sht
End Sub

This gets called from the Thisworkbook Workbook_BeforeSave Sub

Sub HideAllSheets()
  Dim Sht As Worksheet
  For Each Sht In ThisWorkbook.Worksheets
    Sht.Visible = xlSheetVeryHidden
  Next Sht
End Sub