Création d’une plage dynamique sécurisée, Excel VBA

Création d’une plage dynamique sécurisée, Excel VBA
Voici une explication détaillée de la création d’une plage dynamique sécurisée avec VBA dans Excel. Nous allons couvrir deux aspects principaux : créer une plage dynamique et ajouter des mesures de sécurité pour la protéger.
Objectif :

• Créer une plage dynamique dans Excel à l’aide de VBA.
• Ajouter des fonctionnalités de sécurité à cette plage avec VBA.

Concepts clés :
1. Plage dynamique : Une plage dynamique s’adapte automatiquement lorsque des données sont ajoutées ou supprimées. Elle peut être définie à l’aide d’une formule ou directement par VBA.
2. Sécurité : Excel permet de verrouiller des plages pour éviter qu’elles soient modifiées. Cependant, cela nécessite de protéger la feuille pour activer cette fonctionnalité.
Solution étape par étape
Divisons cela en deux parties : la création de la plage dynamique et l’ajout de la sécurité.
1. Créer une plage dynamique en VBA
Une plage dynamique dans Excel est généralement définie par la dernière ligne ou colonne contenant des données. VBA peut calculer cette plage de manière dynamique et l’ajuster en conséquence.
Voici un code VBA pour créer une plage dynamique qui s’ajuste en fonction des données présentes dans la feuille de calcul :

Sub CreerPlageDynamique()
    Dim ws As Worksheet
    Dim plageDynamique As Range
    Dim derniereLigne As Long
    Dim derniereColonne As Long
    ' Référence à la feuille de calcul
    Set ws = ThisWorkbook.Sheets("Feuille1")
    ' Trouver la dernière ligne et colonne contenant des données
    derniereLigne = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    derniereColonne = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    ' Définir la plage dynamique de A1 à la dernière ligne et colonne avec des données
    Set plageDynamique = ws.Range(ws.Cells(1, 1), ws.Cells(derniereLigne, derniereColonne))
    ' Optionnel : Nommer la plage pour une référence plus facile
    ws.Names.Add Name:="PlageDynamique", RefersTo:=plageDynamique
    ' Confirmer que la plage a été créée
    MsgBox "Plage dynamique créée de A1 à " & plageDynamique.Address
End Sub

Explication du code :

• derniereLigne : Trouve la dernière ligne dans la colonne « A » qui contient des données. La méthode .End(xlUp) permet de naviguer vers le haut depuis la dernière cellule de la colonne jusqu’à ce qu’elle trouve des données.
• derniereColonne : Trouve la dernière colonne dans la ligne 1 qui contient des données à l’aide de .End(xlToLeft).
• plageDynamique : Définit la plage de A1 à la cellule située à l’intersection de la dernière ligne et colonne.
• ws.Names.Add : Cette ligne permet de nommer la plage dynamique pour la rendre plus facile à utiliser dans des formules et autres procédures VBA.

2. Ajouter des mesures de sécurité à la plage
Excel permet de verrouiller des plages et de les protéger contre les modifications, mais pour ce faire, il faut d’abord protéger la feuille. Voici comment protéger une plage dynamique à l’aide de VBA :

Sub ProtegerPlageDynamique()
    Dim ws As Worksheet
    Dim plageDynamique As Range
    Dim derniereLigne As Long
    Dim derniereColonne As Long
    ' Référence à la feuille de calcul
    Set ws = ThisWorkbook.Sheets("Feuille1")
    ' Trouver la dernière ligne et colonne contenant des données
    derniereLigne = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    derniereColonne = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    ' Définir la plage dynamique
    Set plageDynamique = ws.Range(ws.Cells(1, 1), ws.Cells(derniereLigne, derniereColonne))
    ' Déverrouiller toutes les cellules d'abord
    ws.Cells.Locked = False
    ' Verrouiller la plage dynamique
    plageDynamique.Locked = True
    ' Protéger la feuille (avec mot de passe pour plus de sécurité)
    ws.Protect Password:="votreMotDePasse", UserInterfaceOnly:=True
    ' Confirmer que la protection a été appliquée
    MsgBox "Plage dynamique protégée !"
End Sub

Explication du code :

• ws.Cells.Locked = False : Déverrouille toutes les cellules de la feuille par défaut.
• plageDynamique.Locked = True : Verrouille uniquement la plage dynamique que nous avons définie.
• ws.Protect Password:="votreMotDePasse" : Protège la feuille avec un mot de passe. Cela signifie que les utilisateurs devront entrer ce mot de passe pour effectuer des modifications.
• UserInterfaceOnly:=True : Cette option permet à votre code VBA de modifier la feuille pendant que l’interface utilisateur est protégée.

3. Gestion des fonctionnalités de sécurité

• Protection par mot de passe : Cela verrouille la feuille et empêche les modifications non autorisées. Assurez-vous de garder votre mot de passe en sécurité, car il n’est pas facile à récupérer si oublié.
• Déverrouiller certaines cellules : Vous pouvez également déverrouiller certaines cellules pour permettre aux utilisateurs d’y saisir des données tout en protégeant le reste de la feuille.

4. Options de sécurité supplémentaires
Vous pouvez ajouter d’autres couches de sécurité en fonction de l’utilisateur qui accède à la feuille de calcul. Par exemple, vous pouvez appliquer différentes protections selon le nom d’utilisateur.

Sub ProtegerPlageSelonUtilisateur()
    Dim ws As Worksheet
    Dim plageDynamique As Range
    Dim utilisateur As String
    Dim derniereLigne As Long
    Dim derniereColonne As Long
    ' Récupérer le nom d'utilisateur de la personne accédant au fichier
    utilisateur = Environ("UserName")
    ' Référence à la feuille de calcul
    Set ws = ThisWorkbook.Sheets("Feuille1")
    ' Trouver la dernière ligne et colonne contenant des données
    derniereLigne = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    derniereColonne = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    ' Définir la plage dynamique
    Set plageDynamique = ws.Range(ws.Cells(1, 1), ws.Cells(derniereLigne, derniereColonne))
    ' Appliquer la protection en fonction de l'utilisateur (par exemple, seul "Admin" peut modifier)
    If utilisateur = "Admin" Then
        ws.Protect Password:="motDePasseAdmin", UserInterfaceOnly:=True
    Else
        ws.Protect Password:="motDePasseUtilisateur", UserInterfaceOnly:=True
    End If
    ' Optionnel : Verrouiller la plage dynamique pour tous les utilisateurs
    plageDynamique.Locked = True
    MsgBox "Sécurité appliquée pour " & utilisateur
End Sub

Explication :

• Environ("UserName") : Récupère le nom d’utilisateur actuel de Windows.
• Selon l’utilisateur, vous appliquez différents mots de passe ou protections. Ainsi, différents utilisateurs auront des niveaux d’accès différents.

Résumé :
Dans cette approche détaillée, nous avons :
1. Créé une plage dynamique qui s’ajuste en fonction des données présentes dans la feuille de calcul.
2. Protégé cette plage dynamique en la verrouillant et en ajoutant une protection par mot de passe à la feuille.
3. Ajouté des options de sécurité basées sur le nom d’utilisateur pour donner des accès différents.
Avec cette configuration, vous disposez désormais d’une plage dynamique flexible et sécurisée dans Excel, en utilisant VBA.