Protection des feuilles de calcul individuelles, par utilisateur (Microsoft Excel)
Excel vous permet de protéger des feuilles de calcul individuelles, comme vous l’avez appris dans d’autres numéros de ExcelTips. (Vous choisissez Outils | Protection | Protéger la feuille.) Vous pouvez utiliser cette approche pour protéger les feuilles de calcul individuelles indépendamment, en utilisant différents http: // nom d’utilisateur-mot de passe .com / 14 / ce-qui-fait-un-bon-mot de passe [mots de passe].
Cela signifie qu’un utilisateur peut apporter des modifications à une feuille de calcul en utilisant un mot de passe et un autre peut utiliser un mot de passe différent pour apporter des modifications à l’autre feuille de calcul.
Mais que faire si vous souhaitez limiter entièrement l’accès aux feuilles de calcul?
Que faire si vous ne voulez même pas qu’un utilisateur non autorisé voie l’autre feuille de calcul? Ce besoin est un peu plus délicat à satisfaire, mais cela peut être fait. L’approche de base serait la suivante:
-
Configurez un classeur contenant trois feuilles de calcul: une qui sera toujours ouverte, une pour l’utilisateur 1 et la troisième pour l’utilisateur 2.
-
Masquer les feuilles de calcul pour l’utilisateur 1 et l’utilisateur 2.
-
Créez un formulaire qui apparaît chaque fois que le classeur est ouvert, en demandant un nom d’utilisateur et un mot de passe.
-
Créez un code de macro qui déverrouille et affiche la feuille de calcul appropriée en fonction du nom d’utilisateur et du mot de passe.
-
Protégez l’intégralité du classeur (Outils | Protection | Protéger le classeur).
Les étapes 1, 2 et 5 sont assez faciles à faire et ont été couvertes dans d’autres numéros de ExcelTips. Cependant, le cœur de cette approche est les étapes 3 et 4. Vous pouvez créer un formulaire utilisateur en suivant ces étapes:
-
Appuyez sur Alt + F11 pour afficher l’éditeur VBA.
-
Dans l’éditeur VBA, choisissez Formulaire utilisateur dans le menu Insertion. Un nouveau formulaire utilisateur vierge s’affiche, ainsi que la boîte à outils du formulaire.
-
À l’aide des contrôles de la boîte à outils du formulaire, ajoutez un contrôle TextBox dans lequel l’utilisateur entrera son nom d’utilisateur.
-
Modifiez les propriétés du contrôle TextBox afin que son nom soit txtUser.
-
À l’aide des contrôles de la boîte à outils du formulaire, ajoutez un contrôle TextBox dans lequel l’utilisateur entrera son mot de passe.
-
Modifiez les propriétés du contrôle TextBox afin que son nom soit txtPass.
-
Juste sous les contrôles TextBox, ajoutez un contrôle CommandButton.
-
Modifiez les propriétés du contrôle CommandButton afin que son nom soit btnOK et sa légende soit OK.
Une fois votre formulaire utilisateur créé, vous êtes prêt à associer le code de macro aux contrôles que vous venez de placer. Assurez-vous que le formulaire utilisateur est sélectionné et appuyez sur F7 pour afficher la fenêtre Code pour le formulaire. La fenêtre peut contenir une ou deux lignes de code généré automatiquement. Remplacez-le par le code suivant:
Dim bOK2Use As Boolean Private Sub btnOK_Click() Dim bError As Boolean Dim sSName As String Dim p As DocumentProperty Dim bSetIt As Boolean bOK2Use = False bError = True If Len(txtUser.Text) > 0 And Len(txtPass.Text) > 0 Then bError = False Select Case txtUser.Text Case "user1" sSName = "u1sheet" If txtPass.Text <> "u1pass" Then bError = True Case "user2" sSName = "u2sheet" If txtPass.Text <> "u2pass" Then bError = True Case Else bError = True End Select End If If bError Then MsgBox "Invalid User Name or Password" Else 'Set document property bSetIt = False For Each p In ActiveWorkbook.CustomDocumentProperties If p.Name = "auth" Then p.Value = sSName bSetIt = True Exit For End If Next p If Not bSetIt Then ActiveWorkbook.CustomDocumentProperties.Add _ Name:="auth", LinkToContent:=False, _ Type:=msoPropertyTypeString, Value:=sSName End If Sheets(sSName).Visible = True Sheets(sSName).Unprotect (txtPass.Text) Sheets(sSName).Activate bOK2Use = True Unload UserForm1 End If End Sub Private Sub UserForm_Terminate() If Not bOK2Use Then ActiveWorkbook.Close (False) End If End Sub
Le code ci-dessus fait plusieurs choses. Notez qu’il existe deux procédures: une plus longue qui s’exécute lorsque l’utilisateur clique sur le bouton OK dans le formulaire et une autre qui s’exécute lorsque le formulaire est terminé. Lorsque l’utilisateur clique sur le bouton OK, la procédure vérifie que la combinaison du nom d’utilisateur et du mot de passe est correcte. Si ce n’est pas le cas, l’utilisateur est averti. Si tel est le cas, le nom de feuille autorisé est stocké dans une variable de document et la feuille appropriée est affichée et non protégée.
Si vous souhaitez modifier les noms d’utilisateur, les noms de feuille et les mots de passe acceptables, vous pouvez le faire en apportant les modifications souhaitées dans la structure Sélectionner la cas en haut de ce code de macro.
La deuxième macro de ce code (UserForm_Terminate) entre en jeu si l’utilisateur essaie simplement de fermer votre formulaire sans entrer un nom d’utilisateur et un mot de passe. Dans ce cas, si le processus d’autorisation n’était pas terminé auparavant, le classeur est simplement fermé.
En plus du code ci-dessus, vous devrez également ajouter les macros suivantes au classeur lui-même. Ceux-ci ouvrent le formulaire utilisateur lorsque le classeur est ouvert et protègent la feuille de calcul lorsque le classeur est fermé.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim w As Worksheet Dim bSaveIt As Boolean
bSaveIt = False For Each w In Worksheets If w.Visible Then Select Case w.Name Case "u1sheet"
w.Protect ("u1pass")
w.Visible = False bSaveIt = True Case "u2sheet"
w.Protect ("u2pass")
w.Visible = False bSaveIt = True End Select End If Next w If bSaveIt Then ActiveWorkbook.CustomDocumentProperties("auth").Delete ActiveWorkbook.Save End If End Sub
Private Sub Workbook_Open()
UserForm1.Show End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name <> "Main" Then If Sh.Name <> ActiveWorkbook.CustomDocumentProperties("auth").Value Then Sh.Visible = False MsgBox "You don't have authorization to view that sheet!"
End If End If End Sub
Lorsque l’utilisateur choisit de fermer le classeur – il en a terminé avec son travail – les feuilles de calcul applicables sont à nouveau protégées et masquées. (Si vous modifiez les noms de feuille utilisateur et les mots de passe, vous devrez également les modifier dans la structure Sélectionner le cas ici.) La macro supprime ensuite la propriété de document appropriée et enregistre le classeur.
Une autre macro intéressante ici est la procédure Workbook_SheetActivate.
Ceci est inclus dans le cas où un utilisateur essaie d’utiliser Format | Fiche | Afficher pour afficher la feuille de calcul d’un autre utilisateur. Dans ce cas, le nom de la feuille autorisée de l’utilisateur (stocké dans une variable de document lorsque l’utilisateur était initialement autorisé) est comparé à la feuille affichée. S’il ne correspond pas, l’utilisateur n’est pas autorisé à afficher la feuille de calcul. Notez également que cette procédure fait référence à une feuille de calcul appelée « Main ». Cette feuille de calcul est la troisième feuille de calcul mentionnée au début de cette astuce.
Cette feuille de calcul est également la première affichée lorsque le classeur est ouvert.
_Note: _
Si vous souhaitez savoir comment utiliser les macros décrites sur cette page (ou sur toute autre page des sites ExcelTips), j’ai préparé une page spéciale qui comprend des informations utiles.
lien: / excelribbon-ExcelTipsMacros [Cliquez ici pour ouvrir cette page spéciale dans un nouvel onglet de navigateur].
ExcelTips est votre source pour une formation Microsoft Excel rentable.
Cette astuce (1952) s’applique à Microsoft Excel 97, 2000, 2002 et 2003.