VBA Excel - Password and Users
April 2018
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
- Getting started
- The UserForm
- The controls of the UserForm
- The Code
- Download the Demo sheet
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