Affecter une macro aux boutons Suppr et Del

Bonjour à tous.

J'ai créé un tableau de 40 colonnes sur 100 lignes, dans lequel les utilisateurs sont amenés à ne modifier que quelques plages de cellules. Celles-ci paraissent vides, mais contiennent en réalité des formules pour permettre des affichages automatiques de valeurs en fonction d'autres parties du tableau. Lorsque les utilisateurs ont besoin de rentrer des caractères dans ces cellules, ça supprime évidemment la formule qu'elle contenait, mais je voudrais que lorsque qu'on appuie sur Suppr ou Del, pour rendre à la cellule sa valeur nulle, ça déclenche une macro dans laquelle je préciserai en fonction de la cellule sélectionnée, quelle formule remettre dedans...

Quelqu'un a-t-il un moyen ?

Merci à tous.

Bonjour,

Changer le fonctionnement d'une touche SUPPR me semble risqué. Par contre tu peux par macro déclencher une action si une cellule vient de changer de contenu.

L'action au changement pourrait être de tester le contenu de la cellule et, si celui-ci est vide, de lui affecter la formule.

Qu'en penses-tu ?

En effet, l'effet recherché serait atteint.

Ça consisterait à surveiller le contenu de chaque cellule après chaque saisie. Pourquoi pas ?

Mais est-ce que ça ne ralentirait pas trop le tout ? Avec une macro tournant en permanence ?

Sinon, peux-tu me proposer un code qui dirait que si la cellule de telle plage (C3:J62 par exemple) devient nulle, on lance la macro Restauration_de_formule() que je vais rédiger par ailleurs ?

Bonsoir. Je poursuis sur mon sujet...

Je tente d'apprendre VBA en autodidacte, et je ne maîtrise pas vraiment les macros qui tournent en permanence.

Quelqu'un saurait-il me donner un exemple de code qui reprendrait la suggestion de Ouisansdoute ?

Et est-ce que cette macro ralentirait perceptiblement l'utilisation du fichier ?

Merci à tous ceux qui voudront bien me répondre.

Bonjour,

Plus simplement pourquoi ne pas déverrouiller les cellules avec saisie et protéger la feuille ?

C'est fait pour ça.

eric

Bonsoir Ran Al'Thor, eriiic,

une possibilité toute simple...

Private Sub Worksheet_Change(ByVal Target As Range)
'
Target.FormulaLocal = Cells(Rows.Count, Columns.Count).FormulaLocal
'
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
If Not Application.Intersect(Target, Range("A1:J10")) Is Nothing Then
    Cells(Rows.Count, Columns.Count).FormulaLocal = Target.FormulaLocal
End If
'
End Sub

A+

eriiic a écrit :

Bonjour,

Plus simplement pourquoi ne pas déverrouiller les cellules avec saisie et protéger la feuille ?

C'est fait pour ça.

eric

En effet Eriic, c'est ce qui vient à l'esprit en premier mais les formules contenues dans mes cellules sont la pour faciliter la première étape de la saisie du tableau, mais la deuxième étape passe forcément par rentrer manuellement la valeur de certaines cellules, au dépend de la fonction qu'elle contenait. Donc, mon but est de réinscrire la formule dans la cellule lorsque, finalement, l'utilisateur veut supprimer le contenu rentré manuellement dans la cellule.

curulis57 a écrit :

Bonsoir Ran Al'Thor, eriiic,

une possibilité toute simple...

Private Sub Worksheet_Change(ByVal Target As Range)
'
Target.FormulaLocal = Cells(Rows.Count, Columns.Count).FormulaLocal
'
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
If Not Application.Intersect(Target, Range("A1:J10")) Is Nothing Then
    Cells(Rows.Count, Columns.Count).FormulaLocal = Target.FormulaLocal
End If
'
End Sub

A+

Bonjour Curulis57

Merci pour ta proposition.

Je n'ai pas la possibilité aujourd'hui de tester ton fichier mais j'essaie de comprendre ta proposition.

Ta première procédure s'applique à chaque changement de feuille ? Ou bien à chaque changement du contenu d'une cellule ?

Elle impose à la cible (Target ?) que sa formule soit égale à la formule d'une cellule à préciser ? (C'est ce que je crois comprendre...)

Par contre, pour la deuxième, elle s'appliquerait à chaque changement de cellule, et n'impose la formule que si le contenue de la cellule

est nul (Notting), c'est ça ?

Finalement, j'ai pu regarder ton fichier Curulis57.

Ce que tu as fait empêche de changer le contenu d'une cellule, alors que j'aurai besoin que la macro ré-intègre la formule (peut-être en appelant une procédure qui définira la formule en fonction de la localisation) lorsque qu'on supprime son contenu, autrement dit, lorsque le contenu passe de non vide (une formule ou des caractères) à nul...

Bonjour Rand Al'Thor,

effectivement, c'est ce que ces procédures effectuent!

En relisant mieux ton post, il y a 2 solutions :

• soit la formule à réintégrer est calculable logiquement et peut-être "fabriquée" à la demande ;
• soit elle est tout sauf calculable et dans ce cas, je te propose de la sauver ailleurs dans la feuille (ce que j'avais fait), une feuille Excel étant un océan de 16.000 X 1.000.000.000 cellules inutilisées!) pour la réintégrer quand c'est nécessaire!

Rien de compliqué! Il faut juste se mettre d'accord sur les circonstances de la réintégration de la formule : j'imagine que cela doit arriver uniquement si l'utilisateur remet la cellule concernée à vide?!

A te lire,

A+

C'est tout à fait cela Curulis57. Tant que les utilisateurs gardent des chaînes de caractères dans la cellule, ça ne me dérange pas, par contre je tiens à ce que la formule réapparaisse si ils vident la cellule, afin que celle-ci puisse interagir comme prévu avec avec d'autres.

Pour les formules, en fonction de la zone où est la cellule, la formule n'est pas la même. Je pourrais effectivement "planquer" les différentes formules ailleurs dans les feuilles, je vais y réfléchir. À la base, je pensais plutôt "appeler" une procédure qu'on pourrait appeler Restauration_de_formule() dans laquelle je comptais utiliser FormulaLocal. Je l'ai déjà utilisé avec succès pour une construction de classeur automatique, mais c'est vrai que c'est un peu fastidieux...

Par contre, je ne sais pas trop manipuler ces procédures qui surveillent les "événements". Est-ce que je dois la placer dans un des deux modules de mon classeur ? Ou bien est-ce que je dois la faire figurer dans le code VBA de chaque feuille (j'en ai 12...) ?

Et enfin, peux-tu m'aider à composer le code qu'elle doit contenir ?

Peut-être avec une "activecell" dont on vérifiera si elle est égale à "", avant de changer de cellule, et si c'est le cas, call Restauration_de_formule() ? Est-ce que c'est cela ?

À+

Bonsoir Rand Al'Thor,

à mon niveau de connaissance Excel (restons humbles devant tous ces cerveaux qui rôdent !), je mettrais le même code dans chaque feuille, code qui appellerait la même procédure dans le Module 1, quitte à passer des variables différentes en fonction des cas rencontrés dans chaque feuille pour résoudre leur problème spécifique.

Je ne suis pas le seul à pouvoir t'aider!

Renseigne-nous avec précision sur les formules de tes différentes feuilles (envoie les nous, ce serait plus simple!) afin que nous puissions analyser et te proposer la meilleure solution.

Un fichier vaut 100 messages!

A+

Je vous joins mon fichier qui comprend normalement 2x12 pages en plus de la première qui me sert à gérer les listes de validations de données.

Les cellules qui m'importent le plus sont dans la plage E3:J64, et se remplissent seules en fonction du contenu des colonnes U, W et AA.

Ces cellules contiennent des formules différentes selon qu'elles sont sur une ligne paire ou impaire, pour répondre au matin ou après-midi. Accessoirement, j'ai aussi des cellules avec formules en D, N et Q, mais qui sont de moindre importance et que je pourrai gérer de la même façon que les autres plus tard.

Dans la plage qui m’intéresse, je rentre un maximum d'infos dans les colonnes U, W et AA, puis je (ou les autres utilisateurs) complète manuellement le reste du contenu avec des chaines de caractères.

J'aimerai que lorsqu'on supprime le contenu d'une cellule de ma plage, quel que soit ce qu'elle contenait, la formule de base réapparaisse.

J'ai édité mes formules (avec les colonnes et les lignes relatives ou absolues, en fonction du besoin) de façon à ce n'importe quelle cellule de la plage puisse être copiée dans une cellule analogue (en respectant paire ou impaire), mais mes utilisateurs ne pensent jamais à le faire lorsqu'ils suppriment un contenu...

Bref, voilà mon bout de fichier.

Voyez-vous quelque chose à me conseiller ?

Bonne soirée.

Bonjour Rand Al'Thor,

voici ton fichier. Je n'ai "équipé" que la feuille JANV 17 pour que tu puisses tester.

Rien de compliqué : si tu es satisfait, il suffit de recopier telles quelles les deux procédures :

• Private Sub Worksheet_Change(ByVal Target As Range)
• Private Sub Worksheet_SelectionChange(ByVal Target As Range)

de la feuille JANV 17 vers les autres concernées.

Bon travail!

A+

Merci Curulis57, c'est génial !!!

Si j'ai bien compris la logique de tes procédures, à chaque fois qu'un utilisateur va modifier le contenu d'une cellule de la plage E3:J64, si la cellule contient une formule, FormulaRescue va la copier 100 colonnes plus loin. Ensuite, si après de multiples changements, on décide d’effacer totalement le contenu de la cellule, Formulagenerator va récupérer la formule affichée 100 colonnes plus loin, pour la copier dans la cellule vide.

Si je veux étendre l'application de ces procédures à des plages de cellules séparées, est-ce que tu me conseilles de les préciser séparées (comme C3:D64; E3:J64; N3:N64 et Q3:Q64), ou bien de prendre directement C3:Q64, puisque les cellules vides au départ ne génèreront pas de formules 100 colonnes plus loin de toute façon...?

Enfin, pour ma compréhension de VBA, peux-tu m'expliquer à quoi sert la ligne "Application.EnableEvents = False"

Pourquoi est-elle nécessaire ?

Merci beaucoup de ton aide.

Bonjour Rand Al'Thor,

oui, tu peux prendre directement Q64, pourquoi pas...

Application.EnableEvents=False neutralise l'interception des événements, ici le changement de valeur de la cellule.

Quand la cellule ="" -> FormulaGenerator -> la cellule retrouve sa formule = nouvel événement.

Dans ce cas-ci, FormulaGenerator ne serait pas ré-appelée puisque la cellule n'est plus vide mais l'événement WorkSheets_Change serait à nouveau exécuté, pour rien.

Bref, ça évite des événements en chaîne qui risquent souvent de f... le b... dans un programme!

Avec plaisir!

A+

Super.

Je crois que tu as résolu mon problème, et d'une façon beaucoup moins compliquée que CE que j'imaginais à la base, même si ça va augmenter un peu le poids du fichier en rajoutant du contenu aux cellules plus loin.

Merci beaucoup.

Je n'oublierai pas de marquer le sujet comme "résolu".

Bonne soirée.

Aïe.

Finalement, en manipulant un peu plus le fichier, j'ai une "erreur d'exécution ' 13': incompatibilité de type" lorsque je prends une plage de cellule (même deux) pour les coller ailleurs sur le tableau...

Peut-être par ce que la procédure n'accepte un changement de contenu pour une seule cellule à la fois ?

Eh, camarade, c'était pas prévu, ça! Il n'y a effectivement pas de protection VBA contre les sélections multiples!

Dans quelles circonstances opères-tu une sélection multiple? Faut-il l'autoriser, pour faire quoi? ...

Clair, précis, complet avec les exceptions, etc....

A+

Je peux être amené à avoir deux cellule, F9 et F10 par exemple, contenant chacune "OFF" et vouloir les copier puis les coller sur d'autres cellules (les suivantes, ou d'une autre colonne, voir d'une autre feuille, pour accélérer la saisie des cellule identiques... Et de la même façon, je pourrais vouloir supprimer le contenu de ces deux (ou plus) cellules d'un coup...

Tu me suis ?

De la même façon, si j'avais un changement de formule à effectuer puis à répertorier sur toutes les autres cellules de la plage, je ferai aussi un collage multiple...

Du coup, peut-être y a-t-il quelque chose du genre "for each cell of Target", où Target ne serait pas une cellule pais une plage de cellule...? (je ne saurai pas ramener cette proposition en langage VBA...)

Exact ! For each rCel in Target.Cells permettrait de traiter un lot de cellules...

Le tout est de cadenasser l'affaire! Je navigue dans ces eaux-là depuis quelques jours avec succès sur des circonstances très précises.

Il faut que je teste cela plus en profondeur dans ton cas.

Donc, tu veux pouvoir :

• changer une formule et la copier telle quelle (?) ou la tirer ?
• copier une valeur dans d'autres cellules ?
• mettre un lot de cellules à vide ?

Ça me promet une belle nuit de découvertes inédites pour moi!

Si tu penses à d'autres possibilités de traitement collectif, transmets et je verrai ça!

A+

Penses tout de même à dormir. J'ai vu que le post de CE matin était vers 5h30 je crois.

En effet, à la base, mon tableau est plein de formules dans les cellules, et on le remplit, soit par des actions dans d'autres plages, soit en tapant directement dans les cellules. Certaines semaines sont OFF, par exemple, pour une colonne, et il me suffit donc de remplir (en tapant les caractères, ou en utilisant la liste déroulante présente dans chaque cellule) les deux cellules du lundi et de les "tirer" pour remplir le reste de la semaine en gardant ma mise en forme (ligne simple ou épaisse). Je peux aussi copier une semaine remplie de OFF pour remplir une semaine d'une autre colonne.

Pour changer les formules, la "difficulté" que je rencontre souvent est que les formules ne sont pas les mêmes si la ligne est paire ou impaires, pour le matin ou l'après-midi. Donc lorsque je dois changer la formule pour l'un ou pour l'autre, je le fais dans une cellule, puis je copie matin/après-midi pour le coller ailleurs dans le tableau, ou bien s'il n'a pas encore été rempli, je peux faire glisser aussi...

Je peux aussi avoir à supprimer le contenu de toute une semaine d'un coup (une semaine marquée initialement OFF par exemple) si le planning change à l'improviste...