User Password Login

[cdhoughton]

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!!

 

[DonkeyOte]

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 ...

Assumptions:
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
Loop

Exit Sub

'If Error (ie no match for NT login then close file, do not save changes)
Hades:
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.

 

[cdhoughton]

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!

 

[Bay]

I'm having trouble with this macro. How do I prime the sheet/run private subs etc.

 

[gekko66]

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
USERNAME = Environ("USERNAME")
If USERNAME = "9546" Then
Sheets("Sheet2").visible=true
Else
ActiveWorkbook.Close False
End If
End Sub

 

[mvptomlinson]

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.

 

[mikerickson]

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.

 

[Giri raju]

Hi

A	Shift 1	Shift 2	Shift 3
Team 1	4500	4950	5445
Team 2	5400	5940	6534
Team 3	6300	6930	7623
Team 4	7200	7920	8712
Team 5	8100	8910	9801
B	Shift 1	Shift 2	Shift 3
Team 1	7800	8580	9438
Team 2	8400	9240	10164
Team 3	9800	10780	11858
Team 4	3200	3520	3872
Team 5	9500	10450	11495
C	Shift 1	Shift 2	Shift 3
Team 1	7800	8580	9438
Team 2	9500	10450	11495

<tbody>
</tbody>

I have the data like this with the help of vloopup how could i arrive the incentive for the below:

Team 2	Shift 2	a
Team 3	Shift 3	a

<tbody>
</tbody>