COMMENT CRÉER UNE AUTHENTIFICATION PAR MOT DE PASSE POUR INTERDIRE L’ACCÈS À CERTAINES FEUILLES EXCEL

Souvent lorsque l’on crée des fichiers de travail sur Excel, nous aimerions faire en sorte que certaines feuilles de calculs puissent n’être accessibles qu’à certains utilisateurs bien précis et inaccessibles pour les autres, et sans pour autant avoir à masquer ces feuilles de travail. Dans ce tutoriel, nous allons donc voir comment mettre en place un système d’authentification par mot de passe qui va ensuite nous permettre de donner des droits d’accès pour les utilisateurs. Pour cela, nous utiliserons VBA, qui permet de démultiplier les fonctionnalités offertes par EXCEL.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Création de la table de gestion des droits

Pour illustrer ce tutoriel, nous allons utiliser un simple classeur composé de quatre feuilles de calculs :

Nous retrouvons ici une première feuille qui est la feuille d’accueil principale, laquelle sera accessible à tous les utilisateurs.

Puis deux feuilles de travail qui ne nous serviront ici que de simples feuilles de tests.

Et enfin, dans la dernière feuille, qui va particulièrement nous intéressa ici nous paramètrerons les différents aspects de la gestion des accès aux feuilles de calcul.

Rendons-nous dans cette feuille.

Celle-ci est composée de trois éléments principaux.

Tout d’abord, nous retrouvons une simple cellule dans laquelle nous venons saisir le nom de l’utilisateur connecté.

Il s’agit pour le moment d’une simple cellule que nous venons alimenter à la main, mais évidemment, nous verrons par la suite comment remplir celle-ci de manière automatique.

Dans le second de ces trois éléments nous retrouvons la table de gestion des droits.

Dans celle-ci, nous retrouvons simplement des colonnes qui reprennent les différents utilisateurs autorisés à utiliser le fichier EXCEL (Alexandre, Émily, Lou et Nina).

Dans la première ligne, nous retrouvons les mots de passe liés à chacun des utilisateurs.

Et enfin, sur chacune des lignes suivantes, nous venons préciser si celui-ci doit avoir un accès à la feuille.

Lorsque nous voulons lui laisser accès à la feuille, nous saisissons VRAI.

Dans le cas contraire, nous laissons la cellule vide (mais nous pourrions également saisir FAUX).

Nous constatons par exemple dans cette table que seul ALEXANDRE pour accéder à l’ensemble des pages du classeur

Et pour finir dans la troisième partie de cette feuille de calcul, nous retrouvons une dernière table dans laquelle vont être insérer les paramètres propres à l’utilisateur en cours d’utilisation du fichier.

Les éléments de la seconde colonne sont récupérés par une simple formule Excel INDEX-EQUIV (toutes les informations sur l’utilisation de cette formule sont disponibles dans ce tutoriel) :

 =INDEX(D5:J5;EQUIV(_utilisateur;$D$4:$J$4;)) 

De cette manière, lorsque nous aurons actualisé le nom de l’utilisateur connecté, il suffira de consulter les informations disponibles dans cette petite table pour obtenir les droits d’accès de celui-ci.

Maintenant que nous avons vu comment fonctionne cette page de gestion des droits, voyons comment mettre en place les restrictions, en utilisant le langage de programmation VBA.

Les points du langage que nous aborderons ici sont relativement simples à comprendre, mais vous pourrez évidemment mieux les appréhender si vous avez au préalable suivi la formation gratuite disponible sur Excelformation.fr.

2. Créer le formulaire d’authentification

Tout d’abord pour pouvoir se connecter au classeur, nous allons devoir proposer une interface de login à l’utilisateur.

Pour cela, rendons-nous dans l’éditeur de code VBA (depuis le menu Développeur, ou par la combinaison de touches [Alt]+[f11] :

Cela étant fait, nous créons un simple formulaire (menu Insertion / Formulaire)

Ensuite, nous nous rendons dans les propriétés de celui-ci afin de lui donner un nom (Name : connect) et un titre (Caption : Indentification)

Dans celui-ci, nous insérons les éléments suivants :

• Deux zones de textes que nous appelons « TextBox_utilisateur » et « TextBox_motPasse »

• Les deux intitulés correspondants : « Utilisateur » et « Mot de passe » :

• Et enfin deux boutons « CommandButton_valider » et « CommandButton_fermer »

Lorsque le formulaire est correctement dessiné, nous pouvons commencer à développer ces différentes parties.

Tout d’abord, la partie la plus simple à mettre en place est de rendre opérant le bouton Fermer.

Pour cela, nous double-cliquons sur celui-ci afin de créer l’évènement à lancer lorsque nous appuyons sur celui-ci.

Étant donné que nous souhaitons fermer le formulaire, nous saisissons simplement la ligne suivante dans l’évènement :

Private Sub CommandButton_fermer_Click()
    Unload Me
End Sub

Ensuite, nous passons au second bouton, c’est-à-dire l’action à effectuer lorsque l’utilisateur valide la connexion.

Pour commencer, nous allons regarder si le champ TextBox_utilisateur est correctement rempli, lorsque ce n’est pas le cas, nous quittons simplement la procédure pour inviter l’utilisateur à ressaisir un nom et un mot de passe.

Private Sub CommandButton_valider_Click()
    If TextBox_utilisateur = "" Then Exit Sub
End Sub

Ensuite, lorsque nous avons passé cette étape, nous allons insérer le nom saisi dans la cellule qui correspond sur la feuille de calcul.

Pour l’appeler facilement, nous allons donner un nom à celle-ci (par exemple « _utilisateur ») directement depuis la zone de nom :

Pour alimenter celle-ci, nous utilisons nous utilisons simplement ce nom entre crochets :

    [_utilisateur] = TextBox_utilisateur

Maintenant nous allons passer à l’analyse du mot de passe.

Et comme vous pouvez vous en douter, nous allons agir simplement sur la cellule qui correspond en lui donnant un nom :

La première chose à faire maintenant, c’est de regarder si l’utilisateur saisi par l’utilisateur (et que nous avons inséré dans la cellule _utilisateur) existe réellement.

Pour cela, il suffit d’observer ce qui se passe lorsque cela n’est pas le cas.

Comme vous pouvez le constater, étant donné que l’alimentation des données du tableau repose sur l’utilisation de la formule INDEX-EQUIV, lorsque le nom n’est pas reconnu dans la base, Excel nous retourne alors une série d’erreurs.

Il suffit alors d’intercepter ces erreurs pour identifier un utilisateur inconnu, en utilisant la fonction VBA IsError().

Celle-ci permet effectivement de renvoyez un résultat de type Booléen (c’est-à-dire ayant pour valeur VRAI ou FAUX, ou True et False en anglais) après avoir analysé l’argument passé entre parenthèses :

    If IsError([_passe]) Then
    End If

Lorsque l’utilisateur est inconnu, alors nous nous effectuons les opérations suivantes :

• Nous revenons sur la feuille principale (dans le cas où le formulaire serait lancé depuis une autre feuille de calcul),
• Puis nous modifions la valeur de la cellule _utilisateur pour que celle-ci identifie un utilisateur Invité
• Et enfin nous affichons un message pour informer que la saisie est erronée

    If IsError([_passe]) Then
        PRINCIPALE.Activate
        [_utilisateur] = "Invité"
        MsgBox "Utilisateur inconnu !"
    End If

Vous noterez au passage que nous utilisons une petite astuce pour gagner un de temps dans la saisie de la feuille de l’activation de la feuille principale en appelant celle-ci directement dans son nom d’identification dans le projet

Ainsi les deux options suivantes sont identiques :

        PRINCIPALE.Activate 
        Sheets("excelformation.fr").Activate

Et maintenant il ne reste plus qu’à vérifier la concordance (ou plutôt la non-concordance) du mot de passe saisi avec le mot de passe attendu (stocké dans la cellule _passe)

    If IsError([_passe]) Then
        PRINCIPALE.Activate
        [_utilisateur] = "Invité"
        MsgBox "Utilisateur inconnu !"
        
    ElseIf [_passe] <> TextBox_motPasse Then
    End If

Lorsque le mot de passe saisi est incorrect, nous réalisons les mêmes opérations, à la différence que le message affiché informe l’utilisateur sur la saisi du mauvais mot de passe.

Voici le code complet à lancer lorsque l’utilisateur clique sur le bouton valider

Private Sub CommandButton_valider_Click()
    If TextBox_utilisateur = "" Then Exit Sub
    
    [_utilisateur] = TextBox_utilisateur
    
    If IsError([_passe]) Then
        PRINCIPALE.Activate
        [_utilisateur] = "Invité"
        MsgBox "Utilisateur inconnu !"
        
    ElseIf [_passe] <> TextBox_motPasse Then
        PRINCIPALE.Activate
        [_utilisateur] = "Invité"
        MsgBox "Mot de passe incorrect !"
    End If
    
    Unload Me
    
End Sub

Comme vous pouvez le constater, nous finissons en fermant le formulaire avec l’instruction Unload Me.

3. Lancer le formulaire

Ensuite, il va nous rester à lancer le formulaire de connexion.

Pour cela, nous créons un nouveau bouton sur la feuille de calcul (Menu Insertion / Forme / Rectangle)

Nous dessinons alors le rectangle à la bonne taille et nous effectuons un clic-droit sur celui-ci afin de choisir d’affecter une macro

Nous donnons un nom à notre macro (ici par exemple la macro va porter le nom de connexion), puis nous appuyons sur le bouton Nouvelle.

De cette manière, Excel crée pour nous une nouvelle macro dans un nouveau module dédié, il ne nous reste plus qu’à lancer le formulaire depuis cette procédure grâce à la propriété Show

Sub connexion()
    connect.Show
End Sub

Nous faisons de même pour créer un bouton de deconnexion afin de rétablir l’utilisateur Invité (Création d’un bouton / Création d’une nouvelle procédure : deconnexion)

Sub deconnexion()
    [_utilisateur] = "Invité"
End Sub

4. Gérer les droits

À présent que nous sommes en mesure de nous connecter sur le classeur, nous allons pouvoir analyser les droits éventuels de chaque utilisateur sur les différentes feuilles du classeur.

Pour cela, nous allons créer un évènement qui va être instancié à chaque fois que nous allons changer de feuille de calcul :

• Depuis l’explorateur de Projet, nous double-cliquons sur ThisWorkbook, afin d’entrer sur la feuille de code du classeur,
• Puis nous sélectionnons les évènements de Workbook depuis le premier menu déroulant situé en haut de la feuille de code

Et enfin, nous choisissons l’évènement SheetActivate, cela a pour conséquence de créer la procédure : Workbook_SheetActivate(ByVal Sh As Object)

Pour obtenir les droits qui correspond pour l’utilisateur en cours ET pour la feuille sélectionnée, nous allons passer en revue chacune de lignes contenues dans le tableau de synthèse, jusqu’à tomber sur la cellule ayant pour valeur le nom de la feuille en question :

Pour cela, nous donnons un nom à la plage de cellules : _rubriquesAcces

Et nous les passons en revue grâce à une boucle For Each (après avoir instancié une variable du type Range) :

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim c As Range   For Each c In [_rubriquesAcces]   Next
End Sub

La boucle For each permet d’énumérer tous les éléments contenus dans une collection.

Ici la plage de cellules _rubriquesAcces est en effet une collection contenant toutes les cellules qui en font partie).

Vous retrouverez toutes les informations concernant For Each en suivant le chapitre dédié de la formation VBA d’excelformation.fr.

Puis nous comparons la valeur de la cellule avec le nom de la feuille sélectionnée (obtenu à partir de la propriété Name de l’objet Sh envoyé par l’évènement SheetActivate)

        If c = Sh.Name Then
        End If

Et enfin, lorsque nous avons identifié la bonne cellule nous regardons si l’accès à la feuille est interdit en vérifiant la valeur de la cellule située juste à droite (nous effectuons ce décalage avec la fonction VBA Offset)

            If Not c.Offset(0, 1) = True Then
                PRINCIPALE.Activate
                MsgBox "Acces interdit !"
            End If

La méthode Range.Offset(ligne, colonne) permet de récupérer la cellule qui se trouve décalée du nombre de lignes et de colonnes données en arguments de la cellule Range.

Par exemple [B10].Offset(1, 2) permet d'agir sur la cellule [D11] (une cellule en dessous et deux cellules à droite de la cellule [B10])Lorsque la valeur de la cellule est différente de VRAI, alors nous redirigeons l’utilisateur sur la feuille PRINCIPALE et nous l’informons que l’accès lui est interdit.

Avant de pouvoir tester le système, il va nous rester à paramétrer l’affichage du formulaire au lancement de la feuille de calcul (en utilisant l’évenement WorkBook_Open)

Nous en profitons également pour définir que l’utilisateur par défaut doit être l’utilisateur Invité.

Private Sub Workbook_Open()
    [_utilisateur] = "Invité"
    connexion
End Sub

Et enfin, nous pouvons tester et nous rendre compte que l’accès aux feuilles non autorisée est rendu impossible !

Évidemment, il est important de garder à l’esprit que ce système n’est pas infaillible, mais il propose déjà une bonne solution pour gérer les différents accès aux fichiers.

Nous pouvons même envisager coupler ce que nous venons de mettre en place avec le système de log que nous avons déjà découvert il y a quelques jours !