Menu

VBA Excel - Password and Users

USFRBR
Ask a question


In this article you will learn how to configure your Excel workbook so that each user only have access to the sheets he's allowed to.

Introduction


The goal is to allow users to access a workbook using a password. Depeding on the permissions and access right the user possesses, he will be able to view or edit specific sheet(s) of the workbook. On the other hand, a admin password will give access to all the sheets.

Getting started


We will insert a UserForm that will be displayed when the file is opened. In this interface, we need two labels , two TextBox for entering the name and password and a CommandButton to validate the password.


VBA code for the button must:
  • Check that the two TextBox are not empty
  • Check that the password correspond to the username
  • Display (only) the sheets for which the user has access and hide the others.


We shall add another code:
  • Upon loading the UserForm:
    • Clear the 2 TextBox
    • Change the "caption" properties of the UserForm, the two labels and the CommandButton.
    • Display *** that instead of characters entered in the password textbox.

The UserForm


To create this UserForm, we need access to the VBA editor. To familiarize yourself with it, read this FAQ: Getting started with the Microsoft Visual Basic for Applications editor
To access it:
  • Open the workbook
  • From any sheet of the workbook, press: ALT + F11
  • In the VBA editor, create your UserForm: Menu > Insert > UserForm.

We get this:

NB: If the toolbox does not appear by default: Menu: View, click "Toolbox".

The controls of the UserForm


In the toolbox, identify the controls that will be used, namely:
  • Label
  • Text Box
  • CommandButton

In the Userform, draw 2 labels (Label 1 and 2 - green), two text boxes and a command button (CommandButton - red ).

You don't need to change the names of the control (Label, Label2, etc. UserForm.), but you can set the colors and size of the controls.

The Code


Routines:
  • To avoid cluttering our Userform codes, we'll put in a standard module, the codes to verify the password and display the sheets.
  • To do this: Menu > Insert > Module.
  • Copy and paste these two procedures:



Option Explicit

'J'ai fait le choix d'une fonction car il ne s'agit que de savoir
'si le mot de passe correspond à l'utilisateur.
'par conséquent, il nous faut une procédure qui compare les 2
'et qui renvoie VRAI ou FAUX (d'où la fonction déclarée As Boolean).
    'ICI : (Utilisateur As String, MdP As String)
    'sont des paramètres envoyés lors du clic sur le bouton
    
Function VerifMDP(Utilisateur As String, MdP As String) As Boolean
VerifMDP = False 'par défaut, renvoie FAUX

'Selon l'utilisateur saisi
Select Case Utilisateur
    'ici il vous faut adapter les noms de chaque utilisateur
    'Vous pouvez donc changer, dans chaque Case, les noms NOM1, NOM2 etc.
    'par contre, saisissez les uniquement en MAJUSCULES
    'Vous pouvez en ajouter ou en enlever selon...
    Case "NOM1"
        'si le mot de passe saisi, pour l'utilisateur NOM1 est "PASS1" alors VRAI
        If MdP = "PASS1" Then VerifMDP = True
        
    Case "NOM2"
        'si le mot de passe saisi, pour l'utilisateur NOM2 est "PASS2" alors VRAI
        If MdP = "PASS2" Then VerifMDP = True
    
    Case "NOM3"
        'si le mot de passe saisi, pour l'utilisateur NOM3 est "PASS3" alors VRAI
        If MdP = "PASS3" Then VerifMDP = True
    
    Case "ADMIN"
        'si le mot de passe saisi, pour l'utilisateur ADMIN est "PASS4" alors VRAI
        If MdP = "PASS4" Then VerifMDP = True
    
    Case Else
        'si le nom d'utilisateur saisi n'existe pas alors :
        'message à l'utilisateur :
        MsgBox "Le nom d'utilisateur saisi n'existe pas. Merci de vérifier."
                
End Select
End Function

Sub AfficheFeuilles(Utilisateur As String)
Dim Ws As Worksheet, Feuilles(), Pos As Integer
'ADAPTEZ tous les case comme dans la fonction
Select Case Utilisateur
    Case "NOM1"
        'placez dans l'array, toutes les feuilles auxquelles doit accéder NOM1
        Feuilles = Array("Feuil5", "Feuil7", "Feuil8")
        On Error Resume Next 'Application.Match étant source d'erreur
        'boucle sur toutes les feuilles du classeur
        For Each Ws In ThisWorkbook.Worksheets
            'On regarde si le nom de la feuille fait partie de l'Array remplit ci-dessus
            Pos = Application.Match(Ws.Name, Feuilles, 0)
            'Si oui alors
            If Pos <> 0 Then
                'on affiche la feuille
                Ws.Visible = True
                'on réinitialise la position
                Pos = 0
            Else 'sinon
                'on masque fortement la feuille
                Ws.Visible = xlSheetVeryHidden
                'on réinitialise la position
                Pos = 0
            End If
        Next Ws
    Case "NOM2"
        'placez dans l'array, toutes les feuilles auxquelles doit accéder NOM2
        Feuilles = Array("Feuil2", "Feuil3", "Feuil4")
        On Error Resume Next 'Application.Match étant source d'erreur
        'boucle sur toutes les feuilles du classeur
        For Each Ws In ThisWorkbook.Worksheets
            'On regarde si le nom de la feuille fait partie de l'Array remplit ci-dessus
            Pos = Application.Match(Ws.Name, Feuilles, 0)
            'Si oui alors
            If Pos <> 0 Then
                'on affiche la feuille
                Ws.Visible = True
                'on réinitialise la position
                Pos = 0
            Else 'sinon
                'on masque fortement la feuille
                Ws.Visible = xlSheetVeryHidden
                'on réinitialise la position
                Pos = 0
            End If
        Next Ws

    Case "NOM3"
        'placez dans l'array, toutes les feuilles auxquelles doit accéder NOM3
        Feuilles = Array("Feuil6", "Feuil9", "Feuil10")
        On Error Resume Next 'Application.Match étant source d'erreur
        'boucle sur toutes les feuilles du classeur
        For Each Ws In ThisWorkbook.Worksheets
            'On regarde si le nom de la feuille fait partie de l'Array remplit ci-dessus
            Pos = Application.Match(Ws.Name, Feuilles, 0)
            'Si oui alors
            If Pos <> 0 Then
                'on affiche la feuille
                Ws.Visible = True
                'on réinitialise la position
                Pos = 0
            Else 'sinon
                'on masque fortement la feuille
                Ws.Visible = xlSheetVeryHidden
                'on réinitialise la position
                Pos = 0
            End If
        Next Ws

    Case "ADMIN" 'ici on affiche toutes les feuilles sans exception
        For Each Ws In ThisWorkbook.Worksheets
            Ws.Visible = True
        Next Ws

    Case Else
        'comme il n'y a pas d'autre cas, je place ici un message d'erreur fatale inutile
        MsgBox "Votre utilisateur renvoie une erreur fatale", vbCritical
End Select
End Sub

Close the module window and lets get back to our UserForm.
The command button:
In your UserForm double-click the Commandbutton and enter the following lines: 
Private Sub CommandButton1_Click()

End Sub


Any code placed between these two lines will run everytime you left-click on your button.
Delete these two lines of code and replace them by: 

Option Explicit

'Code se déclenchant au clic sur bouton
Private Sub CommandButton1_Click()
'Si TextBox1 est vide
If TextBox1 = "" Then
    'Message à l'utilisateur
    MsgBox "Saisie du nom d'utilisateur obligatoire.", vbInformation
    'sortie de la procédure
    Exit Sub
End If
'Même chose avec TextBox2
If TextBox2 = "" Then
    MsgBox "Saisie du mot de passe obligatoire.", vbInformation
    Exit Sub
End If
'Lance la fonction VerifMDP en utilisant :
    '- TextBox1 (transformé en majuscule) comme paramètre "utilisateur"
    '- TextBox2 (transformé en majuscule) comme paramètre "MdP"
'UCase(TextBox1) = contenu du textbox1 en majuscules
'Si la fonction renvoie FAUX :
If VerifMDP(UCase(TextBox1), UCase(TextBox2)) = False Then
    'c'est que le mot de passe ou l'utilisateur est faux donc
    'Message à l'utilisateur
    MsgBox "Erreur Mot de passe et/ou utilisateur. Merci de saisir à nouveau.", vbInformation
    'on vide les 2 textbox
    TextBox1 = ""
    TextBox2 = ""
    'on sort de la procédure
    Exit Sub
End If
'A partir d'ici, le code ne se déroule que lorsque mdp et nom sont corrects.
'On peut donc afficher les feuilles correspondants à l'utilisateur saisi
AfficheFeuilles UCase(TextBox1)
'masque l'UserForm
UserForm1.Hide
End Sub


When the UserForm is initialized:

Under the code that you have previously pasted, copy and paste this: 

'code se déclenchant à l'ouverture de Userform1
Private Sub UserForm_Initialize()
'vidage des textbox :
TextBox1 = ""
TextBox2 = ""
'réglage des propriétés Caption
'de l'Userform :
Me.Caption = "Saisie du Mot de Passe"
'des labels :
Label1.Caption = "Utilisateur"
Label2.Caption = "Mot de Passe"
'du bouton
CommandButton1.Caption = "VALIDER"
'Remplace les caractères saisis dans le textbox2 par des astérisques
Me.TextBox2.PasswordChar = "*"
End Sub


When opening the workbook:

Upon opening your workbook, you want to the following to happen:
- Display "Sheet1" (all other sheet are hidden)
- Display the Userform
In the VBA editor, double-click on ThisWorkbook.
Copy and paste this code: 
Option Explicit

Private Sub Workbook_Open()
Dim Ws As Worksheet

'Masque toutes les feuilles sauf la feuille "Feuil1"
'!!!! ADAPTEZ le nom de la feuille qui doit rester affichée
For Each Ws In ThisWorkbook.Worksheets
    If Ws.Name <> "Feuil1" Then Ws.Visible = xlSheetVeryHidden
Next Ws
'Charge l'Userform en mémoire
Load UserForm1
'Affiche l'Userform
UserForm1.Show
End Sub


You can now:
  • Close the VBA Editor
  • Save your workbook
  • Close and reopen your workbook to test the new feature...

Download the Demo sheet


Download link:http://cjoint.com/13ju/CGylrRfPVX5.htm
  • Username: ADMIN
  • Password: ADMIN

Related

Most popular

VBA - Getting started with the Microsoft Visual Basic for Applications editor
The logic functions