Workbook user login

[Simmo1313]

Hi all,

I'm using excel 2016.

Was wondering if there was a way I can create a user login to access a workbook I have created.

My guess is it would have to reference a different workbook where the user names and passwords are kept so that they can be updated.

If its possible to do without VBA that would be great as I am still an excel novice but if VBA is the only way then I am will to give it a try as its important for the workbook I am developing

Thanks

 

[baitmaster]

Its sort of possible, but not secure - its not what Excel was designed for. Wherever you put your names / passwords, someone with the right knowledge would be able to get in and access that data

There's various things you can do, all involving VBA to lock sheets, hide them, track access e.g. by sending emails, but again there's ways around these things

I'd suggest you try something like sharepoint instead, but VBA does give you options. How secure do you need this and why? What are you actually trying to achieve?

 

[Simmo1313]

It really only needs to be secure enough to keep out the regular users.

It will be a company circulated document where within the company I probably have the most knowledge of excel so i'm confident thats enough security.

 

[baitmaster]

OK so...

You can password protect a workbook so only people with the password can access it. You only get one password for this feature

You can instead use VBA to deny users access by asking them to enter their details, and kicking them out again if they give the wrong info. This requires macros to be enabled or it won't ask the questions, and it only runs once the file is open. You can force users to have macros enabled by ensuring the system is locked down whenever it is saved, such that it can only be opened again by VBA. The issue is that someone skilled in VBA WILL be able to get it open quite easily, and see all the info in it. If people use the same passwords here as they use for more private stuff then you need to consider your data protection obligations - e.g. you need to tell them the system is not secure, or assign a password to them which they cannot change (I'd recommend this one, as they won't get to see the password sheet)

For this idea - which is untested, and for you to decide whether it is suitable for your needs - you'll need:
- Sheet1 is a splash screen, containing a big message that says something like "you must enable macros in order to proceed"
- Sheet2 is a sheet that needs to contain a range named "PWs", which is a two column range of cells, column 1 = staff name, column 2 = password. This sheet should be "very hidden" at all times (change the setting via VB Editor Properties window, not via Excel)

This code in a standard code module:

Option Explicit

Public Const PW As String = "myPassword"

Sub applyLockdown(state As Boolean)
Dim ws As Worksheet

Application.ScreenUpdating = False

Select Case state
    Case True ' show splash screen, very hide all others
        
        Sheet1.Visible = xlSheetVisible
        
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> Sheet1.Name And ws.Name <> Sheet2.Name Then ws.Visible = xlSheetVeryHidden
        Next ws
        
        ActiveWorkbook.Protect Password:=PW, Structure:=True, Windows:=False

    Case False ' very hide splash screen, password sheets, show all others
        
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> Sheet1.Name And ws.Name <> Sheet2.Name Then ws.Visible = xlSheetVisible
        Next ws
        
        Sheet1.Visible = xlSheetVeryHidden
        
        ActiveWorkbook.Unprotect Password:=PW

End Select
    
Application.ScreenUpdating = True
    
End Sub

This code in the ThisWorkbook code module:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
applyLockdown True
End Sub

Private Sub Workbook_Open()
' declarations
Dim strThisUser As String, strThisPassword As String
Dim strOpenMessage As String, intOpenButtons As Integer, strPwMessage As String, intPwButtons As Integer
Dim rngPWdata As Range: Set rngPWdata = Range("PWs")

' default message data
strOpenMessage = "this system is locked. Enter password to continue"
intOpenButtons = vbExclamation + vbOKCancel
strPwMessage = "enter password"
intPwButtons = vbExclamation + vbOKCancel

' ask user for ID details, retry if not found
repeatID: ' named location
strThisUser = MsgBox(strOpenMessage, intOpenButtons)

If strThisUser = "" Then ' action cancelled, close file without saving changes
    ThisWorkbook.Close savechanges:=False
    Exit Sub
End If

If WorksheetFunction.CountIf(rngPWdata.Columns(1), strThisUser) = 0 Then ' user not found, try again
    strOpenMessage = "ERROR: User not found"
    intOpenButtons = vbCritical + vbOKCancel
    GoTo repeatID
End If

' ask user for password, retry if not found
repeatPW:
strThisPassword = MsgBox(strPwMessage, intPwButtons)

If strThisPassword = "" Then ' action cancelled, close file without saving changes
    ThisWorkbook.Close savechanges:=False
    Exit Sub
End If

If WorksheetFunction.VLookup(strThisUser, rngPWdata, 2, False) = strThisPassword Then ' correct password, proceed
    applyLockdown False
    Exit Sub
Else ' incorrect password, try again
    strOpenMessage = "ERROR: incorrect password"
    intPwButtons = vbCritical + vbOKCancel
    GoTo repeatPW
End If

End Sub

Notes: Sheet1 and Sheet2 are the VBA names of these worksheets, not the names on the tabs in Excel. You'll see these in the VB Editor window, folder structure on the left. You should also consider password protecting your VB Code (VB Editor, Tools > VBA Project Properties) otherwise your password is visible, as is the code that controls this feature

Additional Warning: You are now playing with features that involve closing the file, and which decide whether or not to save changes. Be careful when testing your code!

 

[Simmo1313]

Thank you very much,

I will try this and see how I go.

This will be my first attempt at coding so i'm excited to give it a try.