multi user login

[jerichey]

hello,

I have an excel book i created to track inventory. The book has a sheet with a list of users (A), their initials (B) and a password(C). I have placed multiple command buttons on certain sheets that each run a different macro. What i would like is a way for users to be able to log in and have their access of different command buttons restricted based on their user level. Is there a way to do this?

 

[Computerman]

Jerichey,
I would use the ‘visible’property of your command buttons. When the sheet first loads have the commandbuttons ‘visible’ property set to false. Then when the user logs in and theirlogin is validated the last steps in the validation process would be to set the‘visible’ properties of the command buttons they should have access to True.
Computerman

 

[Logit]

.
Here is a simple project for you to edit to suit :

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 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 workbook : https://www.amazon.com/clouddrive/share/w3lpRJRePAYueBqlgT3gpBwvJJ6OxzEiKAxkh4NK0LH

If you password protect the VBE to hide the code, it will keep honest folks where they are supposed to be. Excel security is very easy to circumvent.

 

[jerichey]

Logit,

sorry for the late reply. From the code provided it looks as though this would just hide certain sheets. I don't want to do this as I will have employees that need to view sheets but not have access to buttons. the sheets themselves are password protected. I need to password protect the buttons, but will need to use only a single login form to do so that references the user list sheet. I have changed column B from initials to a user access level 1 through 4.

 

[Logit]

.
This is one method :

Paste this into the Sheet level module :

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    If Range("C2").Value = "pw" Then
        CommandButton1.Enabled = True
    ElseIf Range("C2").Value = "" Then
        CommandButton1.Enabled = False
    End If
    
    If Range("C6").Value = "ss" Then
        CommandButton1.Enabled = True
        CommandButton3.Enabled = True
    ElseIf Range("C6").Value = "" Then
        CommandButton1.Enabled = False
        CommandButton3.Enabled = False
    End If
    
End Sub

Download example workbook : https://www.amazon.com/clouddrive/share/qmPFCx67SuRqnoj9g3J6B9GlQpR1zPXmhOiVVXhKp73