Discussion :

[akkyleri]

Bonjour à tous,

Je suis en train de créer un fichier de calcul Excel où l'utilisateur peut saisir ses données dans des cellules non verrouillées, puis des formules et du code VBA font le travail.

Le classeur et toutes les feuilles sont protégés mais on m'a demandé de laisser le mot de passe vide pour qu'ils ne risquent pas de le perdre (ils doivent pouvoir faire maintenir le fichier si un jour je m'en vais). J'ai tout de même protégé le code par un mot de passe non vide, mais l'ai indiqué dans les annexes de la documentation.

Or, l'une des futures utilisatrices a tendance à supprimer les protections malgré mes avertissements (si elle déplace des cellules, insère des lignes ou renomme les feuilles à son goût, mon code ne saura plus où il habite ! ).

J'ai mis l'avertissement dans la doc, mais,... Je cherche comment faire apparaître un message dès que quelqu'un est sur le point de supprimer une protection, et peut-être d'en garder la trace quelque part s'il poursuit.

L'idéal aurait été de pouvoir utiliser un événement de type "Suppression de la protection de la feuille", mais celui-ci semble ne pas exister, du coup je sèche.

Si quelqu'un a une idée ou même juste une piste, je le remercie d'avance.

[Menhir]

Or, l'une des futures utilisatrices a tendance à supprimer les protections malgré mes avertissements (si elle déplace des cellules, insère des lignes ou renomme les feuilles à son goût, mon code ne saura plus où il habite ! ).

Laisse-la faire.
Une fois qu'elle aura vérolé sa version et qu'elle aura du patienter plus que de raison pour que tu viennes lui remettre ça d'équerre (voir même qu'elle aura perdu des données dans l'opération de remise en état), ça calmera ses ardeurs.

Perso, ma politique est de faire une protection qui empêche les maladresse mais ne protège pas contre les mauvaises manipulations volontaires.
Si un utilisateur fait ce que je lui avais dit de ne pas faire et qu'il se retrouve dans la merde, il assume. C'est de la pédagogie "à la dure" mais c'est assez efficace.
J'en ai deux ou trois qui aimaient bien faire comme tu décris. Quand ils ont perdu plusieurs heures de travail en attente de ma disponibilité et à ressaisir les données perdues, ils sont devenus beaucoup plus respectueux des consignes.

C'est une méthode à la fois plus simple pour le concepteur et pour les utilisateurs.

[RyuAutodidacte]

Bonjour,

Menhir, j'approuve totalement

[akkyleri]

Bonjour, Menhir et Ryu,

Oui, sur le fond vous avez raison.

Toutefois, dans le cas présent, il y a plusieurs fichiers qui ne doivent servir que tous les deux ans pour différentes élections et s'il y a un problème, il faudra forcément le résoudre en urgence le soir des scrutins.

D'autre part, dans deux ou quatre ans, mes avertissements seront loin et ce ne seront peut-être même plus les mêmes personnes qui utiliseront ces fichiers, c'est pourquoi j'aurais voulu rendre ces avertissements automatiques lorsqu'on tente de supprimer une protection de bonne foi car rien ne semble l'empêcher si on ne lit pas la doc.

Pour être honnête, ma crainte est aussi un peu qu'on mette sur le dos de l'application un mauvais fonctionnement alors qu'il résulterait d'actions d'un utilisateur qui aurait supprimé une protection.

En tout cas merci de vos conseils.

[Menhir]

D'autre part, dans deux ou quatre ans, mes avertissements seront loin et ce ne seront peut-être même plus les mêmes personnes qui utiliseront ces fichiers

Dans ce cas, crée un onglet supplémentaire indiquant la procédure à suivre pour faire fonctionner correctement l'application et les avertissements spécifiant ce qu'il ne faut pas faire.
Au pire, tu mets dans l'onglet de ton application une zone de texte non imprimable qui mets en garde sur les risque de modifications.

Déjà, tu peux conseiller à tes utilisateur de conserver une sauvegarde "pure" de ton application et de ne JAMAIS y modifier quoi que ce soit, même dans le cadre d'une utilisation normale, de se contenter d'utiliser des copies de cette source de référence.

c'est pourquoi j'aurais voulu rendre ces avertissements automatiques lorsqu'on tente de supprimer une protection de bonne foi car rien ne semble l'empêcher si on ne lit pas la doc.

Techniquement, il y a toujours des possibilités pour faire ce genre de chose (par exemple une macro qui se déclenche toutes les 10 s pour vérifier que la feuille est toujours verrouillée) mais ça va forcément être une usine à gaz comme dit Pierre Fauconnier ( ).
Ca sera lourd, compliqué, contraignant et peu fiable. VBA n'est vraiment pas conçu pour faire de la protection, il y a trop de façon de le contourner (dont certaines très simples).

Pour être honnête, ma crainte est aussi un peu qu'on mette sur le dos de l'application un mauvais fonctionnement alors qu'il résulterait d'actions d'un utilisateur qui aurait supprimé une protection.

Si tu mets en évidence des avertissements clairs, tu dégages ta responsabilité en cas d'utilisation qui ne s'y conforme pas.

[Pierre Fauconnier]

+1 pour Menhir.

Si je peux ajouter mon grain de sel (considération technique et non attaque personnelle )

Je ne sais pas de quelles élections tu parles, mais Excel n'est absolument pas l'outil à utiliser dans une configuration dans laquelle le bon fonctionnement de l'outil est critique. C'est une ineptie totale de vouloir utiliser Excel en environnement critique.

Si vraiment on ne peut se passer d'Excel, on fait des tets, des tests et encore des tests dans les jours qui précèdent la date du scrutin, puis on met le fichier avec un mot de passe non connu de tou(te)s à disposition pour le jour fatidique, et on prévoit de devoir être sur le pont au cas où...

Cela dit, à part usine à gaz instable et rendant instable le reste de la solution, je ne connais pas de technique permettant de détecter facilement la levée d'une protection de feuille.

[akkyleri]

Bonjour Pierre,

Merci pour ton point de vue, c'est déjà une information qui m'aide de savoir que ce que je voudrais faire est en réalité très compliqué à réaliser. Cela va m'éviter de perdre trop de temps là dessus.

Pour le choix de la solution Excel, en fait cela fait partie de la demande et de toute façon, je n'aurais pas les compétences pour faire autrement. Des fichiers analogues ont déjà bien fonctionné pour d'autres élections il y a un an, après effectivement beaucoup de tests et ma disponibilité (non utilisée finalement) le soir des scrutins.

Pour finir, je pense faire afficher un message contenant avertissements et recommandation à chaque ouverture du fichier, avant ou après le formulaire d'accueil. Ils le verront chaque fois qu'ils ouvriront le fichier pour ajouter une liste ou un bureau de vote, ou saisir les nombres de voix le jour j.

Merci à vous trois, et une bonne fin de journée.

[Philippe Tulliez]

Bonjour,
Je ne peux que rejoindre ce qu'a écrit Menhir et Pierre que je salue au passage.

Il n'y a effectivement aucune procédure événementielle qui détermine si l'on protège ou déprotège une feuille. Cependant on peut toujours trouver une astuce.
En supposant que si un utilisateur enlève la protection d'une feuille c'est dans le but d'écrire dans une cellule, on peut toujours utiliser cet événement en vérifiant la propriété ProtectContents de la feuille.
Exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Me.ProtectContents Then
  MsgBox "Vous avez déprotégé la feuille", vbCritical
 End If
End Sub

[Pierre Fauconnier]

Hello,

Si le fichier est bien conçu, je ne vois pas de raison pour laquelle une utilisatrice devrait en modifier régulièrement la structure. Peut-être serait-il judicieux d'associer cette personne à la conception de l'outil.

Ça la valorise et ça la responsabilise.

[Invité]

Bonsoir,
Plutôt que de fournir une application xlsm fournis un fichier modèle xltm.

Comme tu nous à dépeint cette utilisatrice comme un buze...

[Pierre Fauconnier]

Bonsoir,
Plutôt que de fournir une application xlsm fournis un fichier modèle xltm.

Ca n'empêche en rien les catastrophes.

Renommer ou supprimer une colonne dans un fichier fera planter "l'application" xlsm, et je ne vois pas bien le rapport avec le xltm qui n'empêche pas la perte de données.

[Invité]

Bonsoir Pierre,

Il n'y à rien contre la bêtise mais peut-être une certaine ignorance des utilisateurs qui sait.

[Pierre Fauconnier]

Salut Robert,

Oui, mais il est apparemment plus question de modifier la structure du classeur de données plutôt que de triturer le code. C'est pourquoi j'ai dit que le xlam ne protégeait en rien contre les manipulations malheureuses. Le xltm permet éventuellement de repartir sur une base saise, au prix d'une perte et/ou d'une recopie de données, et ce n'est pas pour moi la fonction première du xltm.

Si on ne s'en tient pas au sujet de la discussion, on est parti pour un troll sur la sécurité des fichiers Excel qui, comme le dit Menhir ( Hello ), est de toute manière tellement fragile qu'elle en est presqu'inexistante

[akkyleri]

Bonsoir Philippe et dysorthographie,

Philippe,

Merci beaucoup pour l'idée du contrôle a posteriori de la protection de la feuille, dès qu'on change le contenu d'une cellule. Je pense l'utiliser, en plus du message à l'ouverture du fichier.

Pierre,

Oui, je pense discuter mieux avec elle des raisons qui peuvent lui faire toucher aux protections. Elle est très contente de l'outil déjà réalisé et utilisé l'an dernier, mais je pense qu'elle ne se rend pas bien compte que certaines actions inoffensives dans ses fichiers personnels peuvent poser problème dans un fichier avec du code, sans compter les toujours possibles maladresses. A moi de mieux faire passer le message et d'améliorer ce qui peut l'être !

Je pense déjà travailler sur la largeur des colonnes : soit protéger les feuilles en laissant la liberté à l'utilisateur d'augmenter ou rétrécir les colonnes, soit faire en sorte qu'elles s'adaptent automatiquement à leur contenu le plus long. Et peut-être aussi créer un bouton qui une fois que tout est fini, permet de créer une copie du fichier mais en xlsx, sans code, sans les formules, et sans protection.

J'aimerais que mes fichiers soient utilisés longtemps et sans problème, du coup j'essaie de penser à tous les cas de figure qui pourraient poser problème.

Menhir,

Bonne idée la feuille supplémentaire avec le mode d'emploi et les recommandations, je prends !


Je vous souhaite à tous une bonne soirée, avec mes remerciements.

[RyuAutodidacte]

@akkyleri

il n'existe pas de verrouillage avec Excel digne de ce nom.

Si tu n'as pas le choix et tu dois absolument utiliser Excel, il faut utiliser une limitation utilisateur (sorte de verrouillage) afin d'éviter un maximum de problème.

• Pour cela il faut que le fichier construit soit intuitif et ludique afin de limiter les erreurs.

• utilisation du "verrouillage feuille" (mot de passe - pas besoin de macro pour cela) laisser seulement les champs dont l'utilisateur à besoin en déverrouillé;
dans le cas ou il y a besoin de macro : verrouiller toutes les cellules des feuilles, et activer les plages utiles via macro (Possibilité de définir des users/plages) - PS : les macros peuvent être verrouillées par mot passe

• utilisation d'un useform pour entrer les données et contrôle de celles-ci pour le remplissage des données via le useform permettant d'éviter les erreurs …
les indications de remplissage dans l'useform peuvent être indiqué par un procédé que l'on aura choisi (forme d'aide)

Après cela, si il y a une erreur (si il n y a pas de problème de conception du fichier), la responsabilité en reviendra à l'utilisateur qui n'aura pas suivi les consignes

[Theze]

Bonjour,

Une piste, tu peux utiliser une fonction volatile qui affichera un message à chaque changement dans n'importe quelle feuille (le message s'affiche aussi si on modifie le nom de la feuille) si une ou plusieurs feuilles ont été déprotégées. Pour l'utiliser, il faut créer autant d'argument que de nombre de feuilles dans le classeur (ici nommés Arg1, Arg2, etc...) et affecter n'importe quelle cellule des différentes feuilles afin que la fonction soit appelée au moindre changement (ce qui va vite gonfler les utilisateurs qui auront ôtés les protections). Tu peux appeler la fonction dans une feuille cachée de façon à ce qu'elle ne se voit pas. Tu peux même la cacher avec cette ligne de code afin qu'elle ne soit pas visible dans Format-->Masquer & Afficher :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
Worksheets("Feuil4").Visible = xlSheetVeryHidden

Un exemple de fonction qui sera appelée à chaque modif dans les feuilles dont une cellule aura été passée en argument :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 
Function Proteger(Arg1 As Range, Arg2 As Range, Arg3 As Range) As Boolean
 
    Dim Noms As String
    Dim Mess As String
    Dim I As Integer
    Dim J As Integer
 
    Application.Volatile
 
    For I = 1 To Worksheets.Count
 
        If Worksheets(I).Name <> "Feuil4" Then 'feuille masquée où se trouve la fonction, à adapter
 
            If Worksheets(I).ProtectContents = False Then Noms = Noms & Worksheets(I).Name & vbCrLf: J = J + 1
 
        End If
 
    Next I
 
    If J > 1 Then
        Mess = "Les feuilles suivantes ne sont plus protégées :" & vbCrLf & Noms
    Else
        Mess = "La feuille suivante n'est plus protégée :" & vbCrLf & Noms
    End If
 
    If J > 0 Then
 
        MsgBox Mess & vbCrLf & vbCrLf & "En ayant ôté la protection, vous risquez d'endommager le classeur !" & vbCrLf & _
               "veuillez ne pas modifier les noms des feuilles ni modifier les cellules protégées !"
 
    End If
 
End Function

Exemple d'appel de la fonction, si tu as 5 feuilles, il te faut rajouter 2 arguments à la fonction sinon, la fonction ne sera appelée que si une modif est faite sur les feuilles qui ont passées un argument :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
=Proteger(Feuil1!A1;Feuil2!A1;Feuil3!A1)

[basto]

Bonjour

Je lis qu'il est important de faire en sorte que ca ne plante pas même si l'utilisateur a modifié des cellules / colonnes / lignes

Pour ma part, lorsque je travaille sur une feuille donnée, je l'accède via son nom interne et nom celui qui apparait dans l'onglet. Comme àa c'est toujours bon car peu de personnes savent modifier le nom interne.

Pour les cellules, colonnes, lignes, j'utilise des cellules, zones nommées.

Comme ça je n'ai pas de référence en dure mais bien des infos liées à une zone.

Maintenant c'est sûr que le code est plus complexe mais il est plus souple et fiable dans le temps.

Bon courage

[akkyleri]

@Ryu,

Le fichier est simple à utiliser et intuitif (d'après les retours que j'en ai eu), c'est peut-être justement ça qui donne l'impression qu'on peut y toucher sans risquer de faire une bêtise. Un rappel rapide des étapes de son utilisation est indiqué dans le formulaire d'accueil qui permet aussi de saisir les paramètres caractéristiques nécessaires aux calculs et de supprimer les données saisies en vue d'une nouvelle utilisation.

J'avais hésité au début entre faire saisir les données par l'utilisateur directement dans des cellules déverrouillées de feuilles protégées ou dans des userforms, la quantité de zones de saisie à créer (750) dans chaque userform m'a dissuadé de choisir la seconde solution.

[akkyleri]

Pour ma part, lorsque je travaille sur une feuille donnée, je l'accède via son nom interne et nom celui qui apparait dans l'onglet. Comme àa c'est toujours bon car peu de personnes savent modifier le nom interne.

Pour les cellules, colonnes, lignes, j'utilise des cellules, zones nommées.

Comme ça je n'ai pas de référence en dure mais bien des infos liées à une zone.

Bonjour basto,

Merci pour les conseils, si j'ai assez de temps, je modifierai les fichiers pour travailler sur les noms internes des feuilles de données. J'y avais vaguement pensé au début, mais je ne pensais pas qu'ils tenteraient de modifier les noms des feuilles tout seuls. Juste tenté car ils n'avaient pas encore repéré qu'on pouvait retirer la protection du classeur en plus de celles des feuilles, du coup, ils m'ont appelé.

Bonne fin de journée

[Pierre Fauconnier]

J'imagine bien que ce n'est pas du sadisme...