Bonjour le forum, bonjour didier,

Je reviens vers vous car suite aux précédents conseils de didier_MDF , j’ai fait plusieurs tentatives pour soulager la procédure Worsheet_Change mais sans succès.
J’ai volontairement laissé en commentaires quelques unes des différentes tentatives que j’ai pu faire après avoir pas mal lu sur les sujets touchant aux variables : c’est désespérant ! Mais bon, je me dis qu’il s’agit là du parcours du débutant...

Mais revenons en au programme :

Ma préférence « intuitive » va à l’utilisation d’une procédure au niveau du classeur Workbook_beforeClose ou Workbook_beforeSave car je ne saisis pas vraiment la différence et je ne sais pas laquelle est la plus adaptée afin de soulager Worksheet_change dès que l’utilisateur répond à la MSGBOX: Epuration, tri et enregistrement sans fermer le classeur.
Là encore, je me suis heurté à de nombreux messages d’erreur : procédure non définie, variable non définie etc…

La portée des variables a encore bien des mystères pour moi…et la robustesse du programme est importante dans mon programme car il est destiné à enregistrer beaucoup d’informations en étant multi-utilisateurs.

En attendant des jours meilleurs, je joints le fichier avec les commentaires « attestant de mes erreurs et incompréhension" !

Merci par avance de votre aide et de vos explications éventuelles

Cordialement

dss

Bonjour dss,

L'évènement Workbook_BeforeSave() de l'objet ThisWorkbook me paraît une très bonne idée et même mieux que le Workbook_BeforeClose() que j'avais suggéré dans nos précédents échanges.

Tu trouveras donc ci-joint une proposition de solution partant sur cette base.

De plus, tu verras que dans cet exemple, j'ai changé la méthode de protection de ta feuille (voir Workbook_Open).
Le projet étant déjà assez complexe à l'origine, lors de nos échanges précédents je n'avais pas souhaité te perturber d'avantage et te parler encore de la protection de feuille en utilisant l'argument UserInterfaceOnly. Tu vas voir que ce mode de protection est très intéressant puisqu'il permet de protéger la feuille des manipulations utilisateur tout en permettant au code VBA d'agir sans besoin de déprotéger l'ensemble.

Par ailleurs, j'ai remplacé ta formule de calcul colonne G (durée) par le résultat d'un calcul purement VBA (voir évènement Worksheet_Change de la feuille Reservation).
Ca me parait plus judicieux pour plusieurs raisons :
[list]
[*]tout est déjà en place pour pouvoir le faire facilement par le code.
[*]le mélange formule sur feuille et code VBA n'est pas toujours souhaitable (avis personnel) et n'a pas vraiment d'intérêt ici.
[*]avec la formule, il te faudrait la prévoir également sur les lignes suivantes encore vide. Et dans ce cas, ton fichier s'alourdirait inutilement (ça pourrait également remettre en cause le code de suppression des lignes vides).
[/list]
Espérant t'avoir aidé.

Cordialement,

Nb: je n'ai pas compris pourquoi tu avais échangé la ligne :
par ceci (qui ne fonctionne plus) :

Bonsoir didier, le forum,

J'ai pris connaissance de ton envoi tout à l'heure et t'en remercie encore une fois vivement.

Je n'ai pu le tester pour l'instant, raisons professionnelles obligent! Mais je te tiendrai au courant dans les tous prochains jours.

Je vois que tu t'es fortement investi sur mon projet en comblant bon nombre de mes lacunes et je t'en suis fortement reconnaissant, d'autant que ta disponibilité à me fournir les explications utiles et nécessaires a été constante et importante.

Une chose avant l'envoi de ce nouveau programme sur laquelle je bute encore : l'emploi de la déclaration Static au niveau de la variable encours . Quelle est son intérêt et sa portée et surtout l'impact des valeurs qu'elle prend à True ou False et son incidence dans le déroulement de la procédure ; je n'ai pas vraiment compris même si j'ai lu par ailleurs que l'emploi de ce type de variable permet de mémoriser la valeur précédente : je n'ai quand même pas compris sa fonction dans le programme.
Si à temps perdu et en quelques mots tu peux m'expliquer... car tes explications et conseils me sont plus précieux que le fait de lire des dizaines et dizaines de pages dans le sens où ils sont ciblés sur un thème en fonction des possibilités offertes par VBA que tu connais et que j'ignore bien souvent : de là, je me perds souvent dans ma lecture là où tes quelques lignes suffisent, et me permettent de progresser beaucoup plus rapidement.

Merci encore pour tout

A bientôt

Cordialement

dss

NB : Désolé pour l'anomalie que tu relèves dans la colonne M et qui tient tout simplement d'une mauvaise recopie de ma part dans l'envoi de ce fichier par rapport à sa première version pour y corriger l'instruction en colonne H. Cette erreur ne figure pas dans mon fichier cible.

Re dss,

Citation :
Tout d'abord, merci de ta remarque car l'objectif du présent site est effectivement avant tout d'apporter les explications utiles et nécessaires à chacun pour une meilleure compréhension et une bonne maitrise de son propre projet. Le tout n'est pas de fournir un code tout prêt à un demandeur, mais de s'assurer qu'il saura le comprendre, voire l'adapter à ses besoins futures. C'est en tout cas dans cette intention que j'ai créé cet endroit...

Citation :
Prenons un exemple simple :
Dans le module de code d'une feuille de calcul, j'ai cette procédure :
En résumé, l'objectif ici est d'ajouter 1 à la valeur de A1 dès que cette cellule est modifiée par l'utilisateur (utilisation de l'évènement Worksheet_Change permettant de "capter" la modification de cellule par le user).
En réalité, ce code est une anomalie et est inccorrect. Pourquoi ?

Si tu le testes, tu vas vite t'apercevoir que ce code rentre dans une boucle sans fin ! (pour interrompre, appuyer sur Echap)
Car une fois la cellule modifiée par l'utilisateur (qui rentre une valeur numérique), la ligne "Target.Value = Target.Value + 1" va elle-même déclencher une nouvelle fois l'évènement Change(), qui va lui aussi déclencher une nouvelle fois ce même code, etc...

Pour contourner ce genre de situation, il y a plusieurs façon de faire.
[list=1]
[*]Utilisation de Application.EnableEvents = False / True qui permet de désactiver les évènements de VBA :
On pourrait l'employer comme ceci :
Là, la procédure semble correcte puisque l'évènement est désactivé juste avant de modifier la valeur de cellule puis réactivé ensuite.
Mais je déconseille fortement d'utiliser cette méthode ! Si par un malheureux hasard, ton code plante entre les 2 instructions, ton classeur perdra sa gestion d'évènements VBA et plus aucune procédure évènementielle ne se déclenchera automatiquement pendant que l'utilisateur continuera de travailler... bonjour les dégâts !
Dans l'exemple ci-dessus, ça sera le cas si l'utilisateur saisit une valeur non numérique en A1 : le code va planter sur la ligne "Target.Value = Target.Value + 1" pour incompatibilité de type et la propriété EnableEvents ne retrouvera jamais sa valeur True...

[*]Utilisation d'une variable de portée Module :
On peut aussi contourner le problème comme suit (tu trouveras le plus souvent cette solution dans les forums de discussions) :
Le principe de contournement est simple :
[list]
[*]Une variable boolean (valeur True ou False) est mise à True (ici elle se nomme "EnCours") juste avant l'instruction fautive qui entraine la boucle non souhaitée.
[*]En tête de procédure, un contrôle est fait sur la valeur de cette variable : si True, alors... passe ton chemin ! (Exit Sub)
[*]Juste après l'instruction fautive, on redonne une valeur False à cette variable de contrôle.

Pour que ça puisse fonctionner, on a déclaré cette variable en tête de module afin qu'elle puisse conserver toujours sa valeur entre les différents appels de l'évènement (ça ne marcherait pas si on la déclarait à l'intérieur même de la procédure).
[/list]
[*]Utilisation d'une variable déclarée Static :
Il s'agit exactement du même principe que pour la variable déclarée en tête de module.
Le mot clé Static (pour une variable déclarée à l'intérieur d'une procédure) permet toutefois à cette variable de conserver sa valeur même si on quitte la dite procédure et bien que celle-ci n'ait pas une portée Module.

[/list]

Alors variable Static ou variable de portée Module ?
Pour moi, la clé du problème se situe non seulement au niveau de la durée de vie de la variable mais aussi, et surtout, au niveau de la portée que l'on veut donner à la dite variable.

Une variable Public ou Private déclarée au niveau Module trouve son utilité uniquement si elle doit être partagée entre plusieurs procédures. Par exemple, si je décidais de chronométrer la durée d'ouverture d'un classeur, je déclarerais une variable vTemps en tête de module de l'objet ThisWorkbook, je lui affecterais l'heure courante dans l'évènement Open() et je réutiliserais sa valeur dans l'évènement BeforeClose() du même objet pour calculer la durée de la session.

Dans le cas cité plus haut (et également dans ton projet), il s'agit uniquement de permettre à la variable de conserver sa valeur, une seule procédure est concernée ici et la valeur n'est pas partagée entre plusieurs évènements. La portée étant donc restreinte, une simple variable déclarée Static à l'intérieur même de cette procédure, suffit à répondre à la seule contrainte de durée de vie.

Personnellement, je privilégie l'utilisation des variables Static quand j'en ai la possibilité. Je les trouve beaucoup plus faciles à gérer. Pour moi, plus la portée de la variable est restreinte, et plus il devient facile d'en maîtriser le contenu. On réduit d'autant les risques de changement de valeur inattendu à un autre endroit dans le code...

On peut ainsi déclarer des variables Static avec le même nom dans plusieurs procédures différentes. Chacune de ses variables reste donc indépendante et conserve sa propre valeur dans sa propre procédure. Moi, je trouve ça pratique...

Par ailleurs, même si je suis dans l'incapacité de le prouver, j'ai dans l'idée qu'une variable Static consomme moins de ressources qu'une variable déclarée en tête de module.

Cordialement,

Bonsoir le forum, bonsoir didier,

Comme je m'y étais engagé, j'ai procédé à de nombreux tests et une seule conclusion s'impose : CHAPEAU !

J'ai en tête de faire évoluer mon projet de façon suivante : Possibilité dans la colonne R, colonne devant être protégée par mot de passe et réservée à seulement quelques utilisateurs d'y inscrire "annulé".

Pour l'instant, je me suis consacré à essayer de trouver le moyen de pouvoir avertir l'utilisateur par boîte de dialogue que la saisie de la ligne est incomplète et si possible de l'inviter à remplir les cellules dans lesquelles l'information est absente : Mais je m'y suis cassé les "dents" pour l'instant !

Est ce cependant possible?

Cordialement et encore merci

dss

Bonsoir dss,

Pour la colonne R, la solution la plus simple est sans doute d'utiliser le même principe que pour la colonne L. Cela dit, cette fois, je te conseillerais plutôt d'utiliser l'évènement Worksheet_BeforeDoubleClick(). Ca aurait l'avantage de ne pas déclencher la boîte de saisie de mot de passe à la moindre sélection par inadvertance dans cette colonne. Les seuls Users autorisés n'auront qu'à faire un double-clic pour afficher cette boîte et saisir leur mot de passe.

Pour le reste, on en revient toujours à la même problématique : quand et comment déclencher ces contrôles de saisie et alertes correspondantes ?
Autrement dit, quand déclencher l'alerte de ligne incomplète ? Au fur et à mesure de la saisie par l'utilisateur ? Ca risque fort de devenir vite pénible...
A l'ouverture ou fermeture du classeur ? En cas de lignes multiples incomplètes, un défilement de boîtes d'alerte ne sera pas du plus bel effet...
Un gros effort de réflexion s'impose...


Cordialement,

Nb : merci pour ton message dans le livre d'or

Bonjour Didier, le forum,


Pour compléter mon tableau et pendre en compte la possibilité d’annulation d’un transport, j’ai ajouté à mon tableau 2 colonnes R et S : la première tant égal à «Transport annulé» et la seconde portant la date de l’annulation. J'ai repris tes conseils et utilisé la macro suivante :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim lig As Long
lig = Target.Row
Const Mot2 As String = "y"
If Target.Count > 1 Then Exit Sub
If Not Application.Intersect(Target, Columns(18)) Is Nothing Then
If Target.Offset(0, -1).Value = "enregistré" Then
InputBox ("Mot de passe, Svp")
If Mot2 = "y" Then Target.Value = "Transport annulé"
If cells(lig, 18).Value = "Transport annulé" Then cells(lig, 19).Value = Now
End If
End If

Par ailleurs, j’ai d’une part, déprotégé les cellules des colonnes R et S.

Cependant, après bien des tentatives, je n’arrive pas à résoudre plusieurs difficultés: protéger la saisie en colonne R hors doubleclick , empêcher de pouvoir supprimer les données saisies à l’aide de la touche « Suppr », et libérer la plage de réservation du véhicule précédemment réservée.

1- J’aurais besoin de protéger l’accès à la saisie des cellules de la colonne R par un mot de passe réservé à 2 personnes dès qu’on essaie de saisir à l’intérieur d’une cellule de la colonne R. Attention, la saisie sera effectuée après que la ligne ait déjà été enregistrée et protégée (colonne A à R).
2- J’ai réussi à protéger l’accès en utilisant la même procédure avec SelectionChange mais compte tenu de tes remarques précédentes, est ce une bonne solution pour une solution viable à long terme ?
3- Empêcher d’autres utilisateurs ne disposant pas du mot de passe de pouvoir supprimer les données saisies en colonne R et S.
4- Libérer la plage de réservation du véhicule si le transport est annulé.


Merci par avance de ton aide.

Cordialement

dss

Bonjour dss, le Forum,

Quelques questions et remarques tout d'abord :
[list]
[*]Sur quelle version Excel travailles-tu et à quelle(s) version(s) Excel ce projet est-il destiné ? (ça peut avoir une importance quant au type de protection Excel qui peut être envisagé pour ta feuille).
D'ailleurs, sache que tu peux indiquer la (ou les) version(s) Excel que tu utilises en éditant ton profil de membre du site et en complétant la rubrique correspondante.

[*]Je n'ai pas compris le code que tu as mis :
[list=a]
[*]Tu ne fais pas cas de la réponse donnée par l'utilisateur ensuite (?!)

[*]Compte tenu de Const Mot2 As String = "y" mis plus haut, le test ici ne sert à rien, ta variable Mot2 vaudra toujours "y".

[*]Il en sera donc de même pour cet autre test, visiblement la Cells(lig, 18).value vaudra toujours "Transport annulé" compte tenu du code précédent. J'ajoute que cells(lig, 18).Value équivaut à Target.value et qu'en utilisant cette expression, tu gagnerais en clarté.
[/list]
[*]Ton projet va-t'il se compliquer d'avantage ? Si tel est le cas, j'ai bien peur qu'il faille à terme repenser l'ensemble de ton projet. A mon avis, la seule façon de pouvoir contrôler au maximum la saisie des utilisateurs, c'est de canaliser cette saisie par le biais d'un formulaire (userform) et d'interdire toute saisie directe dans la feuille Excel. Mais tu risques fort de te lancer dans quelque chose que, visiblement, tu ne maitrises pas... et le jeu n'en vaut certainement pas la chandelle.
De toutes façons, tu ne pourras pas contrôler tout, quelque soit l'option choisie, ou alors tu vas vite flirter avec l'usine à gaz...
[/list]

Cordialement,

Nb : merci d'utiliser les balises [ code ] [ /code ](accessibles via le bouton ) dans ce forum pour insérer du code VBA dans les posts.

Bonjour Didier et le forum,

J'ai bien reçu tes commentaires et entretemps j'ai continué à faire des essais et suis arrivé à la situation suivante :




Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lig As Long
lig = Target.Row
Const Mot2 As String = "y"
If Target.Count > 1 Then Exit Sub
If Not Application.Intersect(Target, Columns(18)) Is Nothing Then
If Target.Offset(0, -1).Value = "enregistré" Then
If InputBox("Mot de passe, Svp") = Mot2 Then
Target.Value = "Transport annulé"
If Target.Value = "Transport annulé" Then cells(lig, 19).Value = Now
End If
End If
End If
End Sub



J'ai effectivement conscience que le test if target.value est inutile mais c'est la seule façon que j'ai trouvé pour renvoyer "Now" en colonne 19 sur la ligne concernée : Faute de mieux, c'était toujours ça...

Pour finaliser mon projet, je te confirme que j'ai aujourd'hui besoin :

1- d'éviter la suppression de la saisie sur les colonnes R et S dès qu'il s'y trouve la mention "transport annulé" (col 18) et la date (col 19) alors que la même ligne (cellule colonne 1 à 17) est déjà protégée. Et là je n'ai pas reussi à trouver la solution pour la ligne concernée : soit toute la colonne est protégée soit aucune des cellules de cette colonne ne l'est (idem pour la colonne 19)

2- Si la mention "transport annulé" (qui ne peut être saisie qu'à l'aide d'un mot de passe et donc sécurisée) est inscrite sur une ligne en colonne 18, alors je voudrais pouvoir libérer la plage de réservation initialement prévue pour ce transport : Là aussi, je n'ai pas reussi à intégrer le test sur la colonne R (transport annulé") au niveau de la partie du programme

J'ai pris connaissance sur ton site du thème concernant les boites de dialogue personnalisées et j'envisage d'utiliser cette procédure pour exploiter la valeur de renvoi de la boite de dialogue et renforcer le controle de la saisie : qu'en penses-tu?

Merci par avance de ton aide car pour répondre à tes interrogations, la logique de sécuriser la saisie sur la colonne 18 et d'empêcher sa suppression permet de sécuriser l'annulation du transport et d'obtenir, du moins je le crois, un programme fiable et cohérent.

Cordialement

A+

dss

NB : J'y pris bonne note de ta remarque pour l'envoi du code, désolé, je ne savais pas...
La version que j'utilise est excel 2002 mais le programme sera également traité par des postes sur différentes versions dont excel 97.
Je peux renvoyer le code mais j'ai pensé que cela n'était pas utile et éviterait de surcharger pour rien le forum.

Re,

Tout d'abord, merci pour l'utilisation des balises [ code ], cela dit, le code VBA doit se situer entre la balise [ code ] et la balise [ /code ] pour que ça fonctionne lol

Pour en revenir à ton problème, ci-dessous une mise en oeuvre de ce que je te proposais précédemment, c'est à dire l'utilisation de l'évènement BeforeDoubleClick :

Pour que ça puisse fonctionner, il te faut mettre en "verrouillée" l'ensemble des cellules des colonnes R et S au préalable (aucune saisie manuelle ne doit être possible tant que la feuille est protégée).

Par ailleurs, pour libérer la plage horaire, je n'ai pas trouver mieux que de redéfinir cette plage à 00:00.

Tu trouveras ci-joint ton fichier modifié en conséquence.

Cordialement,

EDITION
Citation :
L'utilisation de l'API Windows est toujours délicat et source de nombreux plantages si mal maitrisée. Par ailleurs, ton projet est destiné visiblement à plusieurs versions Excel différentes (dont Excel 97). Je te déconseille fortement d'y recourir pour ce projet.