Julie aimerait avoir des cellules d’entrée sur une feuille de calcul qui, lorsque quelqu’un entre une valeur, ajoute automatiquement 20% à ce qui a été saisi.

Par exemple, si quelqu’un entre 200 dans l’une de ces cellules, ce qui est réellement entré est 240.

Il existe une multitude de façons d’aborder cette tâche à l’aide de macros.

La meilleure approche consiste à créer une macro qui est exécutée, automatiquement, lorsqu’une cellule est modifiée dans la feuille de calcul. Vous pouvez ensuite vérifier si la modification a été effectuée dans l’une des cellules d’entrée et ajuster les valeurs en conséquence.

L’exemple suivant modifie la valeur entrée si elle a été effectuée dans l’une des trois cellules: A1, C3 ou B8.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rInput As Range     Dim rInt As Range     Dim rCell As Range

'change the input cell range as desired     Set rInput = Range("A1, C3, B8")



Set rInt = Intersect(Target, rInput)

If Not rInt Is Nothing Then         For Each rCell In rInt             If IsNumeric(rCell) Then                 With Application                     .EnableEvents = False                     rCell = rCell * 1.2                     .EnableEvents = True                 End With             End If         Next     End If End Sub

N’oubliez pas qu’il s’agit d’un gestionnaire d’événements, ce qui signifie qu’il est déclenché (dans ce cas) chaque fois que quelque chose change dans la feuille de calcul. Pour utiliser cette macro, cliquez avec le bouton droit sur l’onglet de la feuille de calcul et choisissez Afficher le code dans le menu contextuel résultant. Excel affiche l’éditeur VB et vous pouvez ensuite ajouter le code Worksheet_Change.

Notez que la clé pour déterminer si la modification a été effectuée dans l’une des trois cellules d’entrée définies est la fonction Intersection. Il vérifie s’il existe une intersection entre la plage cible (les cellules modifiées qui ont déclenché le gestionnaire Worksheet_Change) et la plage rInput (vos cellules d’entrée). Si tel est le cas, rInt contiendrait les cellules qui se croisent.

La macro parcourt ensuite ces cellules et, si les cellules contiennent des valeurs numériques, elle multiplie ces cellules par 120%. (La multiplication par 120% équivaut à augmenter la valeur de 20%.) Notez que la propriété .EnableEvents est définie sur False lorsque la multiplication est effectuée; si cette sauvegarde n’était pas prise, chaque multiplication déclencherait à nouveau ce gestionnaire d’événements et vous multiplieriez à plusieurs reprises (et pour toujours) la valeur de la cellule par 120%.

Si vous souhaitez effectuer un autre traitement des valeurs, par exemple arrondir à un nombre spécifique de points décimaux ou à une valeur entière, il vous suffit de modifier la ligne unique qui effectue réellement la multiplication.

Si vos cellules d’entrée se trouvent dans une zone contiguë, une meilleure approche peut être de définir ces cellules d’entrée comme une plage nommée, puis d’utiliser cette plage nommée dans la macro pour déterminer l’intersection des cellules modifiées. De cette façon, vous n’avez pas besoin de modifier la macro lorsque ou si votre groupe de cellules d’entrée change.

Pour utiliser cette approche, disons que votre plage de cellules d’entrée est B7: B19. Sélectionnez ces cellules, puis, en utilisant la zone de nom dans le coin supérieur gauche de la zone des cellules, entrez le nom «plus20pct». Cette action attribue le nom à la plage. Vous pouvez ensuite utiliser ce nom dans la macro.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rInt As Range     Dim rCell As Range

'change the input cell range as desired

Set rInt = Intersect(Target, Range("plus20pct"))

If Not rInt Is Nothing Then         For Each rCell In rInt             If IsNumeric(rCell) Then                 With Application                     .EnableEvents = False                     rCell = rCell * 1.2                     .EnableEvents = True                 End With             End If         Next     End If End Sub

Notez que le seul changement concerne la manière dont l’intersection des cellules est déterminée – la fonction Intersection utilise la plage « plus20pct » comme paramètre. Tout le reste fonctionne comme avant.

Maintenant que vous avez vu comment faire cela en utilisant des macros, la question demeure de savoir si vous devriez vraiment le faire avec des macros. Premièrement, il y a des limites à ce que ces macros peuvent faire. Par exemple, que se passe-t-il si votre utilisateur entre une date ou une heure dans l’une des cellules d’entrée? En interne, Excel gère les dates et les heures sous forme de nombres, ce qui signifie qu’eux aussi seraient augmentés de 20%.

Deuxièmement, vous devez considérer ce qui arrive à votre feuille de calcul si quelqu’un modifie la structure de la feuille de calcul en ajoutant ou en supprimant des lignes ou des colonnes.

Les macros utilisent des références de cellule absolues (A1, C3 et B8) ou une plage nommée (plus20pct). Bien que la plage nommée puisse être ajustée par l’ajout ou la suppression de lignes ou de colonnes, les références de cellules absolues ne changeraient pas. Ainsi, vous pourriez vous retrouver avec la macro-vérification (et ajustement)

cellules qui ne sont plus les cellules de saisie de données attendues.

Troisièmement, disons que quelqu’un entre une valeur (200) dans l’une de vos cellules d’entrée. Il est automatiquement augmenté de 20% et devient 240. La personne voit ce changement et se demande ce qui s’est passé, elle sélectionne donc la cellule et appuie sur F2 pour commencer à éditer la cellule. Avant de faire le changement, ils se souviennent que « Oh, oui, il est censé augmenter de 20% automatiquement. » Ainsi, ils appuient simplement sur Entrée pour accepter la valeur 240.

Cependant, Excel voit cela comme un changement et augmente le 240 de 20%, ce qui donne 288 – pas ce que vous ou l’utilisateur vouliez.

Cette deuxième considération – la confusion de l’utilisateur – est le plus gros problème potentiel lié à la modification automatique de ce qu’un utilisateur entre dans la feuille de calcul. Une approche moins déroutante serait d’avoir une zone de saisie bien définie pour votre classeur. L’utilisateur place des chiffres dans la zone de saisie et ces chiffres restent tels qu’ils les saisissent. Ensuite, dans d’autres cellules ou dans vos formules, vous effectuez l’ajustement de 20%.

Cette approche de conception (modifier la conception de la feuille de calcul pour faciliter la saisie des données) est potentiellement moins déroutante pour l’utilisateur que de modifier automatiquement ce qu’il a entré dans une cellule de feuille de calcul. Il élimine également un risque inhérent à tout classeur à macro-activé: l’utilisateur peut charger le classeur sans activer les macros, garantissant ainsi que les chiffres ne sont pas ajustés comme vous le souhaitez.

_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 (12684) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365.