User Password Login


Board Regular
Dec 5, 2003
I am considering including a login screen for my application/spreadsheet to a) improve the presentation and b) improve the security.

Does anyone know any good resources for a beginner to look at? Also, how secure would such a feature be and would there be any way of enhancing security?

I am reluctant to use the Excel password protection (mainly due it making my project less professional) and would also like multiple users (with different access levels) to use the application.

Any advice or assistance would be greatly appreciated should anyone have undertaken a similar venture!!

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
XL security is poor.

The best option if you're using XL and you're running this on a network is to use the NT Login as a security trap.

Have a sheet with NT User logins who should be able to log in - say this sheet is called LOGINS - check the NT Login of the user when the file is opened and compare it to your list, if they're not on the list they're not coming in ...

1. Sheet called LOGINS that contains login information (NT Users, Sheets they can see etc...)
2. Column A on this sheet contains the NT Login of the user (eg cdhoughton)
3. Columns B onwards contain the sheets they're allowed to see.

Private Sub Workbook_Open()

User = Environ("Username")

On Error GoTo Hades:
TF = Application.WorksheetFunction.Match(User, Sheets("LOGIN").Range("A:A"), 0)

'Unhide Appropriate Sheets
c = 2
Do Until Cells(TF, c) = ""
    Sheets(Cells(TF, c)).Visible = True
    c = c + 1

Exit Sub

'If Error (ie no match for NT login then close file, do not save changes)
ActiveWorkbook.Close SaveChanges = True

End Sub

You then need a close event that hides every worksheet (one must remain visible at ALL times however - say Sheet1 will be visible at all time)

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Sheet1" Then
        ws.Visible = xlSheetVeryHidden
    End If
Next ws

End Sub

If you're unsure about events see a post I did to cdible about half an hour ago for pointers.

Make sure your protect your VBA also else someone (capable) can still work around this. For high level - adaptable security levels you really need to use a purpose built database (SQL, MySQL etc....) - XL isn't really designed for it.

Hope that helps.

Also suggest you run a search on security on this forum - there are loads of posts with solutions / workarounds available.
Upvote 0
My spreadsheet is likely to be distributed to a number of clients, and so I was focusing more on a login procedure that could define a number of bespoke users, dependent on the user's wishes - this could be simply 1 for a sole trader, or say 10 for a larger client.

The spreadsheet will hold accounting data, and so is security is of a medium priority. I doubt that the users will have sufficient knowledge of VBA to "hack" the program, so I think I'll have a look for some security posts on the site per your instructions.

Cheers for the input, you've certainly helped shape what I'm trying to achieve!
Upvote 0
I'm having trouble with this macro. How do I prime the sheet/run private subs etc.
Upvote 0
the above method is good however it can be got round easily with the below

Sub slip()
Dim ws As Worksheet
Sheets.Add.Name = "door" ' creates a new sheet called door
For Each ws In ActiveWorkbook.Worksheets 'declares all sheets in the book
If ws.Name <> "door" Then 'if the sheet name is not door
ws.Visible = true 'makes it visible
End If
Next ws
End Sub

i have used the environ function but set each sheet they had access to individually which is a lbit of work but when only dealing with 10 users not so bad.

Sub slim()
Dim USERNAME As String
If USERNAME = "9546" Then
ActiveWorkbook.Close False
End If
End Sub
Upvote 0
Of course it can be "got round easily" if you have access to the VB Editor within that workbook. If you have that access, you don't even need code to un-hide workbooks, you just un-hide them in the VBE properties for each worksheet. Hence why DonkeyOte stated "Make sure you protect your VBA..."

You would make the user access worksheet "xlSheetVeryHidden" so that you could only un-hide it through the VBE, not via Workbook/Worksheet protection.
Upvote 0
One theory is that Excel is not a secure platform.
Time wasted on trying to make it secure might be spent on making good spreadshseets.

That's one theory.

Access control for stubborn, un-trained, clumsy, but honest users is one thing.
Protecting against bad guys is another.
Upvote 0
AShift 1Shift 2Shift 3
Team 1450049505445
Team 2540059406534
Team 3630069307623
Team 4720079208712
Team 5810089109801
BShift 1Shift 2Shift 3
Team 1780085809438
Team 28400924010164
Team 398001078011858
Team 4320035203872
Team 595001045011495
CShift 1Shift 2Shift 3
Team 1780085809438
Team 295001045011495


I have the data like this with the help of vloopup how could i arrive the incentive for the below:
Team 2Shift 2a
Team 3Shift 3a

Upvote 0

Forum statistics

Latest member
Rich Cohen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back