Share a work book base on user name and password

Re: Share a work book base on user name and password

You need to have a hidden sheet (called Sheet1 in the macro) with the usernames on and in the column next to it have the sheet name that you want them to view. You also need a sheet called Welcome as this will be the only one displayed if macros are turned off or they don't have sheet access.

This goes in the Thisworkbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sh As Worksheet
Sheets("Welcome").Visible = True
For Each sh In Sheets
If sh.Name <> "Welcome" Then
sh.Visible = xlVeryHidden
End If
Next sh
ThisWorkbook.Save
End Sub


Private Sub Workbook_Open()
    Dim Ans As Range
    Application.ScreenUpdating = False
    
    On Error GoTo Nxt
    Set Ans = Sheets("Sheet1").Columns(1).Find(What:=Environ("username"), After:=Sheets("Sheet1").Range("A1"), LookIn:=xlFormulas, _
    lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    If Ans <> "" Then
        Sheets(Ans.Offset(0, 1).Value).Visible = True
Sheets("Welcome").visible=False
        Application.ScreenUpdating = True
        Exit Sub
    End If
Nxt:     MsgBox "No such user Found"
    ThisWorkbook.Close False
End Sub

Re: Share a work book base on user name and password

When I tried using your file, it all worked just fine, with the exception of username mark. In that case, the list of sheets to unlock had a space after the comma, which the split function in UnlockSheets was not prepared to deal with. If I took out the extraneous space in cell C5, then all the users worked just fine. The way the code is written, it looks like you could also get an invalid sheetname result if you input "Name" and "Password" as the name and password entries, as it would then be attempting to open a non-existent sheet named "Sheets".

I would also add that if the information you are keeping hidden from some of the users is highly confidential, this is not a particularly secure way of protecting it from them. Even if they don't themselves have the Excel/VBA skills to crack it, they might know someone who does and who would do it for them. I could crack this in a matter of minutes, and have done so in the past with other files I receive where I want to know what's been hidden.

Re: Share a work book base on user name and password

As Barb already said, using Excel to hide confidential information on worksheets is never going to be secure.

This technique is fine as a way to avoid confusing users by hiding information that is not relevant to them. You would need to take an entirely different approach if the information is confidential.

Re: Share a work book base on user name and password

It is possible to set up BeforeSave and BeforeClose event code to disable save and automatically save the workbook when it is closed. Not sure how that relates to your orginal question though.

Re: Share a work book base on user name and password

A lot depends on what level of security you are trying to achieve. The issue with Excel passwords and VBA as security is that the workbook can be loaded with macros disabled and then any security that is being imposed by your VBA is nullified. Excel passwords can also be cracked within minutes using VBA.

Re: Share a work book base on user name and password

Not in a secure way. Any code has to be enabled by the user. If they have high security settings and disable macros when they load the workbook then any code is disabled. You cannot base high security data on Excel.