XL 2021 Verrouillage de cellules après exécution d'une macro

[MATLEA]

Problème de verrouillages de résultats issus d’une macro

Considérons un groupe de cellules (fichier joint) contenant des valeurs nominales (série des « tel »), colonnes AB et AC de 3 à 18 avec quelques trous exemple tel 21 (AC7), tel 28 (AC14), tel 30 (AC16).

L’objectif du travail est de créer un tirage aléatoire permettant de modifier l’ordre en éliminant les cellules vides.

Création de la colonne AG (de 3 à 34) reprenant les valeurs des colonnes AB et AC en utilisant la formule =""& et coordonnées de la cellule (exemple =""&AB3 qui donne tel 1 en AG3) pour éviter l’introduction d’un zéro (0) dans les cellules vides .

Génération de la colonne AK située obligatoirement 4 colonnes après la colonne AG reprenant les valeurs de la colonne AG (formule =) puis mise sous forme de tableau structuré de la colonne AK qui devient colonne1, les valeurs étant décalées vers le bas d’une cellule. Génération de la colonne2 (colonne2) par propagation de la cellule AK3 vers la droite ce qui active la macro (développée par job75 du forum) générant un nouvel ordre des cellules de la colonne AG sans prise en compte des cellules vides regroupées en bas de colonne2 (cellules AL33, 34,35).

Dans la colonne AN sont issues les valeurs transférées depuis la colonne AL après application de la formule =INDIRECT("AL"&LIGNE()) (AL étant la coordonnée de la colonne 2).(merci job75)

Mon problème tient au fait que toute tentative de récupération des valeurs de la colonne AN relance la macro et remodifie l’ordre (exemple en AR7 la saisie de AN6 qui aurait due être tel18 est devenue tel20). Toute tentative pour récupérer la valeur d’une cellule dans AN modifie l’ordre de la colonne entre AN4 et AN34.

Ma question est de savoir comment verrouiller les cellules de la colonne2 (AL) après exécution de la macro permettant de travailler sur les valeurs en AN sans craindre une nouvelle modification de l’ordre.

 

[job75]

Bonsoir MATLEA,

Vous n'en finissez pas ?

Pour récupérer la valeur d'une cellule à l'aide d'une formule de liaison, ajoutez simplement au début de la Worksheet_Change :

If Target.Count = 1 And Target(1).HasFormula Then Exit Sub

A+

 

[MATLEA]

Merci,

j'aurai fini quand j'aurai atteint mon objectif.
Cordialement.

 

[MATLEA]

Bonsoir MATLEA,

Vous n'en finissez pas ?

Pour récupérer la valeur d'une cellule à l'aide d'une formule de liaison, ajoutez simplement au début de la Worksheet_Change :

If Target.Count = 1 And Target(1).HasFormula Then Exit Sub

A+

Re-bonjour,

Mais avec ce changement la macro, chez moi ne fonctionne plus. Pas de remplissage automatique de la colonne 2 qui reste vide.
Bien à vous

 

[job75]

Mais avec ce changement la macro, chez moi ne fonctionne plus.

Chez moi elle fonctionne, modifiez une cellule quelconque sans formule.

 

[MATLEA]

Chez moi elle fonctionne, modifiez une cellule quelconque sans formule.

Bonsoir
Vraiment désolé mais ca ne marche pas.
Regardez dans le fichier joint un nouvel essai entre les colonnes D et N, la colonne N ne se remplit pas.
De même toute modification dans les cellules AB AC entre 3 et 18 est bien répercutée dans AG mais absolument pas dans AK (colonne1) ni AL (colonne2).

Mystère

Si je fais une erreur n'hésitez pas à me le dire
Envoyez-moi le fichier qui fonctionne chez vous pour voir si le problème vient de mon ordinateur.

Merci et bien cordialement

 

[job75]

A1 étant sélectionnée, quand j'appuie sur la touche Suppr :

- la colonne N du 1er tableau se remplit

- les colonnes AK et AL du 2ème tableau se mettent à jour.

Si ça ne se passe pas ainsi chez vous c'est peut-être que Application.EnableEvents a pris la valeur False.

Il suffit alors de fermer et rouvrir le fichier.

 

[MATLEA]

A1 étant sélectionnée, quand j'appuie sur la touche Suppr :

- la colonne N du 1er tableau se remplit

- les colonnes AK et AL du 2ème tableau se mettent à jour.

Si ça ne se passe pas ainsi chez vous c'est peut-être que Application.EnableEvents a pris la valeur False.

Il suffit alors de fermer et rouvrir le fichier.

Cest parfait, merci

 

[job75]

Cest parfait, merci

Merci de nous dire d'où venait votre problème.

 

[MATLEA]

Merci de nous dire d'où venait votre problème.

Après avoir adopté et maitrisé (après quelques errements sur le positionnement des cellules et peut-être un problème de corruption de fichier) la macro proposée par job75 pour changer aléatoirement l'ordre des données dans une colonne tout en se débarrassant des cellules vides j'ai été confronté à la récupération des données générées par la macro. Le problème a été résolu grâce à l'application de la formule "=INDIRECT("AL"&LIGNE())". Cependant l'activation de la macro à chaque utilisation des cellules qui entrainait un nouveau tirage aléatoire a pu être résolu par job75 en ajoutant une ligne au programme de la macro.
Je n'ai pu cependant utiliser cet outil qu'après une nouvelle intervention de job 75 qui m'a conseillé de sélectionner une cellule sans formule et d'appuyer sur la touche "suppress" puis de fermer et rouvrir le fichier car a priori Application.EnableEvents avait pris la valeur False.
J'en profite pour remercier à nouveau les experts du forum pour leur aide, et en particulier job75 qui a fait preuve de beaucoup de pédagogie et de patience avec un utilisateur d'excel très inexpérimenté pour ne pas dire candide.
Très cordialement

 

[MATLEA]

Merci de nous dire d'où venait votre problème.

Bonjour,

Désolé de devoir vous solliciter à nouveau mais le problème d'activation intempestive de la macro n'est pas résolu.
Le fichier joint montre la construction d'un outil de gestion des concours de boules.
J’utilise d'abord la macro (colonnes AE AF et AO AP) pour randomiser chacun des 2 groupes de 64 joueurs (64 pointeurs (G3) et 64 tireurs (O3)) permettant de créer des doublettes définies aléatoirement (H16) et qui vont s'affronter 2 par 2 d'abord dans la partie 1 puis dans les parties 2 et 3 après traitement des résultats et réutilisation de la macro.
Comme vous pourrez le constater la modification des scores dans le tableau des parties réactive la macro ce qui entraine des perturbations dans les colonnes F et O d'identification des doublettes.
Je ne sais comment résoudre ce problème que je pensais régler avec la modification que vous aviez apportée à la macro.
Y-a-t-il une solution.
Merci

 

[MATLEA]

Bonjour job75,

J’ai uploaded sur le site il y a quelques jours un fichier de gestion de concours de boules en construction, non fini, comportant des bugs de saisie mais surtout un bug d’activation inopinée de la macro, qui, pour chaque intervention de l’opérateur sur des cellules du fichier modifie la composition des équipes et le tableau des confrontations rendant le système inexploitable. Ceci en dépit de l’utilisation de la macro modifiée par vos soins pour éliminer ce problème et qui fonctionnait sur le fichier d’essai très simplifié que vous m’aviez transmis. Je suis reparti de ce fichier pour développer le fichier d’exploitation.

Est-ce que je fais une erreur dont je ne sais identifier l’origine, erreur de positionnement des colonnes (j’ai pourtant depuis tenté en vain différentes localisations), autre erreur ?

Par ailleurs, je ne sais comment interpréter votre absence de réaction/réponse à ma dernière sollicitation alors que vous étiez toujours très réactif.

Emploi du temps surchargé ? Refus volontaire de poursuivre le suivi de mon dossier dû à un agacement bien compréhensible suite à mes nombreuses sollicitations ? Exaspération d’interagir avec un néophyte qui ne possède pas les bases nécessaires ? Difficulté technique pour résoudre ce nouvel écueil ? Nécessité d’un délai pour trouver la solution ?

Quelle que soit la raison, je la comprendrai très bien mais j’aimerais seulement que vous me l’indiquiez. Comme je vous l’ai dit c’est la dernière étape de mon défi. Avec une macro qui serait activée seulement à la création de la colonne 2 j’aurais la totalité des outils nécessaires. Le système serait utilisable par n’importe quel opérateur qui n’aurait à intervenir que pour saisir le nom des joueurs et les scores des parties. Le système serait beaucoup plus convivial que ce qui est actuellement disponible.

Merci donc de m’indiquer si vous voulez encore ou pouvez résoudre le problème actuel, si cette option de la macro est encore adaptée ou si je dois rechercher une alternative ?

Avec mes plus sincères remerciements et mon profond respect.

 

[job75]

Bonjour MATLEA,

Je n'ai pas eu le temps de m'occuper de vous.

Comme vous pourrez le constater la modification des scores dans le tableau des parties réactive la macro ce qui entraine des perturbations dans les colonnes F et O d'identification des doublettes.

Si c'est bien là le dernier problème à résoudre ajoutez au début de la macro Worksheet_Change :

If Not Intersect(Target, [M:N]) Is Nothing Then Exit Sub

A+

 

[MATLEA]

Bonjour MATLEA,

Je n'ai pas eu le temps de m'occuper de vous.

Si c'est bien là le dernier problème à résoudre ajoutez au début de la macro Worksheet_Change :

If Not Intersect(Target, [M:N]) Is Nothing Then Exit Sub

A+

 

[MATLEA]

Bonjour Job75

Votre dernier ajout au début de la macro Worksheet_Change a éliminé le biais de l'activation de la macro quand on modifiait le score des parties. A l'heure actuelle le programme est a priori (presque) opérationnel comme vous pourrez le voir dans les 2 fichiers joints, l'un avec les 64 doublettes, l'autre avec 32 doublettes sur 64, les cellules vides (sans joueurs) n'interférant pas avec les autres dans les tableaux des parties, ce que je recherchais. J'ai dit presque parce que la macro Worksheet_Change s'active inopinément dès que des cellules vides autres que celles utilisées par le programme sont traitées par l'introduction d'une quelconque valeur. Par exemple la saisie du chiffre 8 en D28 active la macro et tout est chamboulé. Si par inadvertance l'opérateur du concours venait à introduire une valeur dans une de ces cellules au milieu du concours ce serait une catastrophe. Je ne sais pas non plus comment réagirait le système dans le cas d'une architecture du fichier différente (concours en triplettes par exemple). Y aurait-il moyen d'éviter ce problème et de restreindre l'activation de la macro à la seule étape de la création de la colonne2 des tableaux structurés.
J'ai noté aussi en générant le fichier avec 32 doublettes sur 64 "M 64 Doublettes 32 effectives" que la formule (=""& + coordonnées de la cellule) ne s'active pas systématiquement automatiquement lors du changement du nombre de cellules actives. J'ai eu par exemple besoin de retirer vers le bas à partir de AA3 ou de AK3 pour supprimer les cellules faussement vides. Il y a aussi des cas BX137, CH137, CR137 où la propagation ne réactive pas la formule, corrompant ainsi le tableau de répartition de la partie 3. Y aurait-il une solution?
Dernier point, beaucoup moins important, dans le fichier "M 64 Doublettes 32 effectives" à partir des cellules Q et V137 le classement des équipes est interrompu par les cellules vides (score meilleur que certaines équipes qui ont un goal average négatif).

Merci et bien cordialement