Username and password system to access information

[NateSC]

Our company has an Excel file/macro that I built for our HR department to document attendance and other employee data. It is just a few simple Excel tables and a front end that makes it easy for HR to add data. They recently asked if I could build a way for the individual employees could access the data to see their own information. To me, this involves a serious layer of username/password access control to make sure the user can only see their own data and not anyone else's.

I don't think Excel and macros are necessarily the best way to do this, but I would take any advice. I could easily export the data to a file each time HR makes changes and then that data could be accessible to a custom built or third party program if that is the best option.

Any opinions?

 

[Jeffrey Mahoney]

I base all my password workbooks on login names. When a person signs into their computer the name they provide is accessible using the code below. I usually create a table with all the login names where I can provide the sheets they can access or what tasks they are allowed to perform. You can hide worksheets in a way that nobody can see them unless you want them to. You can add passwords to sheets

Now, any data that you don't want others to see can, and will get hacked by the clever folk. So if it's really important, than don't put multiple user data in the same workbook.

I've been using a new method of writing to access databases to store data. The user's don't know where the data is coming from and the code is locked. When a user logs in, it grabs their data from the DB and puts it into the workbook. The workbook is read only, so no saving private data for others to see. This requires a lot of overhead.


LoginName = UCase(Environ("UserName"))

 

[NateSC]

This is a good option. Some of our floor employees won't have a unique machine and login so I may have to make it visible to the supervisors for each shift and let them handle the requests to see data. I can try this and see if it works.

 

[Logit]

This code goes in the user form :

Option Explicit

Dim HFD As Integer, HFR As Integer
Dim N As Long, F As Long, Pass As String
Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Sheets("SetUp").Visible = xlSheetVisible
        For N = 3 To HFR
            If ComboBox1.Value = Sheets("SetUp").Cells(15, N).Value Then
                Exit For
            End If
        Next N
        
    If TextBox1.Value = Sheets("SetUp").Cells(16, N).Value Then
        Sheets("SetUp").Visible = xlSheetVeryHidden
        MsgBox Range("SetUp!C10").Value, , Range("SetUp!C9").Value & " " & Sheets("SetUp").Cells(15, N).Value
        Unload UserForm1
        Sheets("SetUp").Visible = xlSheetVisible
        Pass = Sheets("SetUp").Range("K12").Value
        Sheets("SetUp").Visible = xlSheetVeryHidden
        
        For F = 17 To HFD
            If UCase(Sheets("SetUp").Cells(F, N).Value) = "X" Then
        
            Sheets(Sheets("SetUp").Cells(F, 2).Value).Visible = xlSheetVisible
            End If
            
            If UCase(Sheets("SetUp").Cells(F, N).Value) = "P" Then
                Sheets(Sheets("SetUp").Cells(F, 2).Value).Visible = xlSheetVisible
                Sheets(Sheets("SetUp").Cells(F, 2).Value).Protect Password:=Pass
            End If
        Next F
    Else
        MsgBox Range("SetUp!C6").Value, , Range("SetUp!C7").Value
        TextBox1.Value = ""
        Sheets("SetUp").Visible = xlSheetVeryHidden
    End If
    
    Application.ScreenUpdating = True
End Sub
Private Sub CommandButton2_Click()
    Unload UserForm1
End Sub

Private Sub Label3_Click()

End Sub

Private Sub UserForm_Initialize()

Dim WkSht As Worksheet
Application.ScreenUpdating = False
    For Each WkSht In Worksheets
        If Not WkSht.Name = "Intro" Then WkSht.Visible = xlSheetVeryHidden
    Next WkSht
    
    Sheets("SetUp").Visible = xlSheetVisible
    HFD = Sheets("SetUp").Range("B65536").End(xlUp).Row
    HFR = Sheets("SetUp").Range("IV15").End(xlToLeft).Column
    UserForm1.Caption = Range("SetUp!C3").Value
    Label3.Caption = Range("SetUp!C4").Value
    
    For N = 3 To HFR
        With ComboBox1
            .AddItem Sheets("SetUp").Cells(15, N).Value
        End With
    Next N
Sheets("SetUp").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub

Download sample workbook : Internxt Drive – Private & Secure Cloud Storage