Multi user login with diffrent rights

Gaith

New Member
Joined
Feb 19, 2019
Messages
3
hey!

I have a multi user workbook i would like to ask for some help. I have crated a login sheet where i can write user id and password for this workbook.
I have then crated a loop to make it look for the user if it match you get access. This is where i am stuck i cant get it to give different users different rights to see sheets.
For example want user1 to only see and use sheet 1 and user 2 to only see and use sheet 2. I am very grateful for any help


ps. My excel is in swedish so blad=Sheet

Here is my Code:

Private Sub CommandButton1_Click()
Dim Uname As String, Upwd As Variant 'sätter uname som text och Upwd som variant
Uname = UserForm1.TextBox1.Value 'användarnamn = Uname
Upwd = UserForm1.TextBox2.Value 'Lösenord = Upwd


If Uname <> "" And Upwd <> "" Then 'om det står ett värde i användarnamn och lösenord då ska
Blad6.Range("D2").Value = Uname ' Användernamnet kopieras till Login bladet
Blad6.Range("D3").Value = Upwd 'Lösenordet kopierat till Login bladet
Else
MsgBox "Användernamn och Lösenord Saknas!" 'om man inte skriver ett Anamn eller Lösen Då visas det här meddelandet
Exit Sub
End If

Dim rng As Range
Dim UnameRange As Range: Set UnameRange = Range("A2", "A10")
For Each rng In UnameRange
If Blad6.Range("D2").Value = rng.Value Then
rng.Offset(0, 1).Select
Range("F3").Value = Selection.Value
Range("F2").Value = rng.Value
End If
Next
If Range("D2").Value = Range("F2").Value And Range("D3").Value = Range("F3").Value Then
MsgBox "Du är nu inloggad"
Else
MsgBox "Ange rätt användar namn och lösenord!"
Range("F3").Value = ""
Range("F2").Value = ""
End If
TextBox1.Value = ""
TextBox2.Value = ""

End Sub
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Dipchikov

New Member
Joined
Feb 19, 2019
Messages
4
Hi,

Before time I made a similar code but I used vba form.
In code below you can find sheet and button access.

Code:
Private Sub cmdok_Click()


    Dim Username As String
    Dim password As String
    
             If txtpassword.Value = 123 And txtusername.Value = "Operator" Then     '
                       Sheets("Routing").Visible = False
                       Sheets("Service").Visible = True
                       Sheets("Data").Visible = False
                       Sheets("Dashboard").Visible = False
                       Sheets("Completed").Visible = True
                       Sheets("Label").Visible = False
                        ActiveSheet.Shapes("Delete_order").ControlFormat.Enabled = False
                       ActiveSheet.Shapes("Soft_by_date").ControlFormat.Enabled = True
                       ActiveSheet.Shapes("Soft_by_Priority").ControlFormat.Enabled = True
                       ActiveSheet.Shapes("Move_Down").ControlFormat.Enabled = False
                       ActiveSheet.Shapes("Move_Up").ControlFormat.Enabled = False
                        ActiveSheet.Shapes("Double_order").ControlFormat.Enabled = False
                        ActiveSheet.Shapes("Add_data").ControlFormat.Enabled = False
                        ActiveSheet.Shapes("CommandButton3").ControlFormat.Enabled = False
                        ActiveSheet.Shapes("CommandButton5").ControlFormat.Enabled = False
                        ActiveSheet.Shapes("CommandButton6").ControlFormat.Enabled = False
                        Sheets("Completed").Shapes("CommandButton7").ControlFormat.Enabled = True
                        Sheets("Completed").Shapes("CommandButton8").ControlFormat.Enabled = True
                        Sheets("Completed").Shapes("CommandButton9").ControlFormat.Enabled = True
                        Sheets("Completed").Shapes("CommandButton5").ControlFormat.Enabled = False
                        ActiveSheet.Shapes("CommandButton9").ControlFormat.Enabled = True
                        ActiveSheet.Shapes("CommandButton10").ControlFormat.Enabled = True
                        ActiveSheet.Shapes("CommandButton11").ControlFormat.Enabled = True
                        ActiveSheet.Shapes("CommandButton12").ControlFormat.Enabled = True
                        
                        
                        
                        Unload Me
              Else
              
                       If txtpassword.Value = "" And txtusername.Value = "Guest" Then     '
                       Sheets("Routing").Visible = False
                       Sheets("Service").Visible = False
                       Sheets("Data").Visible = False
                       Sheets("Dashboard").Visible = False
                       Sheets("Completed").Visible = True
                       Sheets("Label").Visible = False
                        ActiveSheet.Shapes("Delete_order").ControlFormat.Enabled = False
                       ActiveSheet.Shapes("Soft_by_date").ControlFormat.Enabled = False
                       ActiveSheet.Shapes("Soft_by_Priority").ControlFormat.Enabled = False
                       ActiveSheet.Shapes("Move_Down").ControlFormat.Enabled = False
                       ActiveSheet.Shapes("Move_Up").ControlFormat.Enabled = False
                        ActiveSheet.Shapes("Double_order").ControlFormat.Enabled = False
                        ActiveSheet.Shapes("Add_data").ControlFormat.Enabled = False
                        ActiveSheet.Shapes("CommandButton3").ControlFormat.Enabled = False
                        ActiveSheet.Shapes("CommandButton5").ControlFormat.Enabled = False
                        ActiveSheet.Shapes("CommandButton6").ControlFormat.Enabled = False
                        Sheets("Completed").Shapes("CommandButton7").ControlFormat.Enabled = False
                        Sheets("Completed").Shapes("CommandButton8").ControlFormat.Enabled = False
                        Sheets("Completed").Shapes("CommandButton9").ControlFormat.Enabled = False
                        Sheets("Completed").Shapes("CommandButton5").ControlFormat.Enabled = False
                        ActiveSheet.Shapes("CommandButton9").ControlFormat.Enabled = False
                        ActiveSheet.Shapes("CommandButton10").ControlFormat.Enabled = False
                        ActiveSheet.Shapes("CommandButton11").ControlFormat.Enabled = False
                        ActiveSheet.Shapes("CommandButton12").ControlFormat.Enabled = False
                        Unload Me
              Else
              
              
                       If txtpassword.Value = "ErEs_2000" And txtusername.Value = "Manager" Then     '
                       Sheets("Routing").Visible = True
                       Sheets("Service").Visible = True
                       Sheets("Data").Visible = True
                       Sheets("Dashboard").Visible = True
                       Sheets("Completed").Visible = True
                       Sheets("Label").Visible = True
                       ActiveSheet.Shapes("Delete_order").ControlFormat.Enabled = True
                       ActiveSheet.Shapes("Soft_by_date").ControlFormat.Enabled = True
                       ActiveSheet.Shapes("Soft_by_Priority").ControlFormat.Enabled = True
                       ActiveSheet.Shapes("Move_Down").ControlFormat.Enabled = True
                       ActiveSheet.Shapes("Move_Up").ControlFormat.Enabled = True
                        ActiveSheet.Shapes("Double_order").ControlFormat.Enabled = True
                        ActiveSheet.Shapes("Add_data").ControlFormat.Enabled = True
                        ActiveSheet.Shapes("CommandButton3").ControlFormat.Enabled = True
                        ActiveSheet.Shapes("CommandButton5").ControlFormat.Enabled = True
                        ActiveSheet.Shapes("CommandButton6").ControlFormat.Enabled = True
                        Sheets("Completed").Shapes("CommandButton7").ControlFormat.Enabled = True
                        Sheets("Completed").Shapes("CommandButton8").ControlFormat.Enabled = True
                        Sheets("Completed").Shapes("CommandButton9").ControlFormat.Enabled = True
                        Sheets("Completed").Shapes("CommandButton5").ControlFormat.Enabled = True
                        ActiveSheet.Shapes("CommandButton9").ControlFormat.Enabled = True
                        ActiveSheet.Shapes("CommandButton10").ControlFormat.Enabled = True
                        ActiveSheet.Shapes("CommandButton11").ControlFormat.Enabled = True
                        ActiveSheet.Shapes("CommandButton12").ControlFormat.Enabled = True
                    
                       Unload Me
                       Else
                       If txtpassword.Value = 2234 And txtusername.Value = "Admin" Then     '
                       Sheets("Routing").Visible = True
                       Sheets("Service").Visible = True
                       Sheets("Data").Visible = True
                       Sheets("Dashboard").Visible = True
                       Sheets("Completed").Visible = True
                       Sheets("Label").Visible = True
                       ActiveSheet.Shapes("Delete_order").ControlFormat.Enabled = True
                       ActiveSheet.Shapes("Soft_by_date").ControlFormat.Enabled = True
                       ActiveSheet.Shapes("Soft_by_Priority").ControlFormat.Enabled = True
                       ActiveSheet.Shapes("Move_Down").ControlFormat.Enabled = True
                       ActiveSheet.Shapes("Move_Up").ControlFormat.Enabled = True
                        ActiveSheet.Shapes("Double_order").ControlFormat.Enabled = True
                        ActiveSheet.Shapes("Add_data").ControlFormat.Enabled = True
                        ActiveSheet.Shapes("CommandButton3").ControlFormat.Enabled = True
                        ActiveSheet.Shapes("CommandButton5").ControlFormat.Enabled = True
                        ActiveSheet.Shapes("CommandButton6").ControlFormat.Enabled = True
                        Sheets("Completed").Shapes("CommandButton7").ControlFormat.Enabled = True
                        Sheets("Completed").Shapes("CommandButton8").ControlFormat.Enabled = True
                        Sheets("Completed").Shapes("CommandButton5").ControlFormat.Enabled = True
                        ActiveSheet.Shapes("CommandButton9").ControlFormat.Enabled = True
                        ActiveSheet.Shapes("CommandButton10").ControlFormat.Enabled = True
                        ActiveSheet.Shapes("CommandButton11").ControlFormat.Enabled = True
                        ActiveSheet.Shapes("CommandButton12").ControlFormat.Enabled = True
                       Unload Me
                       Else
            MsgBox "Invalid Username/Password"
         End If
         End If
         End If
        End If
End Sub

'-----------------
Private Sub UserForm_Initialize()






txtusername.List = Array("Operator", "Manager", "Guest", "Admin")




End Sub
 

Gaith

New Member
Joined
Feb 19, 2019
Messages
3
Thank you for the help, what dose
Code:
txtusernme.list=ary("operator","manager","guest","admin")
do?
 

Dipchikov

New Member
Joined
Feb 19, 2019
Messages
4
Hi,

In my user form for User I used ComboBox for easy selection users.


txtusernme.list=array("operator","manager","guest","admin")
 

Watch MrExcel Video

Forum statistics

Threads
1,109,000
Messages
5,526,172
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top