SOMME avec condition couleur - problème d'actualisation

Bonjour,

j'avais trouvé sur ce forum un code de module pour permettre d'effectuer une somme en fonction de la couleur de la cellule. Le code était le suivant :

Function SOMME_SI_COULEUR(PlageSomme As Range, PlageCouleur As Range) As Variant

'*********************************************************

' Effectuer la somme des cellules en couleur *

'*********************************************************

Dim Cel As Range

Dim Som As Double

If PlageCouleur.Cells.Count > 1 Then

SOMME_SI_COULEUR = CVErr(xlErrValue)

Exit Function

End If

For Each Cel In PlageSomme

If Cel.Interior.ColorIndex = PlageCouleur.Interior.ColorIndex Then Som = Som + Cel

Next

SOMME_SI_COULEUR = Som

End Function

Celui-ci fonctionne partiellement (exemple1) . En effet, j'ai un souci d'actualisation de cette formule, dans plusieurs cas :

Cas 1 : je change la couleur manuellement d'une cellule contenue dans la plage à sommer (erreur1 : le 7 à changer de couleur)

Cas 2 : je change manuellement la couleur de la cellule source (référence pour la couleur en question) (erreur 2 : le rouge est maintenant la couleur de référence)

--> ces deux problèmes se résolvent si j'effectue la combinaison alt gr + F9 (actualisation du résultat). pareillement, si au lieu de sélectionner manuellement la couleur, j'utilise l'outil "copier la mise en forme", le résultat se met à jour

Avons nous la possibilité de compléter le code pour que celui-ci intègre une mise à jour "automatique"?

Merci par avance pour votre aide,

Bonsoir,

pas de réponse depuis le 6 octobre !

En fait tout fonctionne bien, c'est juste un problème de "mise à jour du calcul"...

Alors il vous faut jouer sur les surveillances événementielle d'Excel !

Vous surveillez la feuille en question et dès qu'une valeur change, on lance le calcul...

Vous surveillez la feuille en question et dès que la cellule sélectionnée change, on lance le calcul...

Pour cela rien de plus simple :

Faites un clic droit sur le nom de l'onglet, sélectionner Afficher le code.

Vous arrivez sur VBA dans le code de la feuille, après sur la partie droite il y a deux listes de choix :

dans celle de gauche sélectionnez : Worksheet (SélectionChange est automatiquement initialisée)

ensuite dans la liste de droite vous avez accès à toutes les surveillances événementielles, dans le cas dit au dessus vous avez le "Change" et le "Sélection Change"

reste à mettre l'instruction calculate et le tour est joué...

Voici les deux codes à "coller" sur la page VBA de la feuille :

Private Sub Worksheet_Change(ByVal Target As Range)
    Calculate
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Calculate
End Sub

Bien évidemment sans fichier je n'ai pas pu essayer...

@ bientôt

LouReeD

Bonjour,

j'ai fait un premier test où cela a marché, puis lorsque j'ai fermé puis rouvert le fichier impossible à refaire marcher...

Peut être que joindre le fichier est effectivement plus intelligent? En PJ donc

Bonsoir,

ci joint le fichier modifié : en fait c'est VBA qui utilise la fonction et ensuite il inscrit la valeur du calcul dans la cellule G9.

Ce calcul est fait suite à une modification de valeur sur la feuille, ou un changement de sélection de cellule.

Cela marche bien dans "tous les sens" pour peut que l'on clic sur la feuille pour quitter la cellule qui vient d'être modifiée

soit pour la couleur soit pour la valeur qu'elle contien.

@ bientôt

LouReeD

Bonjour,

et merci à nouveau pour cette réponse!

Sur le fichier exemple, l'actualisation marche effectivement. Toutefois, ce fichier reste un exemple : ainsi, si je veux utiliser la fonction dans un autre fichier, ou même dans une autre case, je dois réécrire le code inscrit sur la feuille (ce qui peut vite devenir problématique si j'ai un système plus complexe, avec par exemple plusieurs cases avec la formule liées à différentes plages, etc.).

Afin de faire fonctionner cette fonction "n'importe où" (cf fichier joint), peut-on intégrer au sein du module une actualisation comme effectué sur la feuille, par exemple en le liant à :

Function SOMME_SI_COULEUR(PlageSomme As Range, PlageCouleur As Range) As Variant

je ne sais pas si je commets une hérésie en proposant cela... j'ai tenté d'inscrire le code de la feuille dans le module en transformant G9 par SOMME_SI_COULEUR, D4:D11 par PlageSomme et F9 par PlageCouleur

Private Sub Worksheet_Change(ByVal Target As Range)
    Range("G9").Value = SOMME_SI_COULEUR(Range("D4:D11"), Range("F9"))
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("G9").Value = SOMME_SI_COULEUR(Range("D4:D11"), Range("F9"))
End Sub

Mais ça n'a pas marché bien évidemment.. Possible ou non tout de même??

Merci encore,

Bonjour,

la solution (il y en a peut -être d'autre...) :

vous nommez les cellules d'addition comme je l'ai fait sur le fichier joint en par exemple :

_somme1

_somme2

_somme3

le principal est d'avoir un chiffre compteur à la fin, par exemple

Ensuite la procédure VBA va boucler sur ces cellules grâce à Range("_somme" & i)

où i va avoir grâce à la boucle les valeurs de 1 à 4 si 4 cellules additions.

Ensuite le référence de la cellule couleur n'est autre que la cellule qui se trouve une colonne à gauche de celle de l'addition donc :

OffSet(0,-1) c'est à dire "décalage de 0 ligne et de -1 colonne)

Avant tout cela on met la mise à jour de l'écran à false et surtout la surveillance événementielle à false sinon boucle infinie !!!

Une fois toutes les formules additions faites on remet la surveillance événementielle en marche ainsi que la mise à jour de l'écran, on incorpore une gestion error simple :

on error rsumme next permet de continuer le déroulement du code même s'il y a une erreur comme dans le fichier joint, on boucle sur 6 cellules somme alors qu'il n'y en a que 5 !!!!

@ bientôt

LouReeD

Bonjour,

A nouveau, merci pour cette réponse. encore une fois, cela marche pour le fichier exemple... Mais est il possible de complètement dissocier la formule de l'exemple?

Je pense que cette fonction gagnerait à être complètement autonome. compte tenu qu'elle est écrite ainsi :

Function SOMME_SI_COULEUR(PlageSomme As Range, PlageCouleur As Range) As Variant
'*********************************************************
' Effectuer la somme des cellules en couleur *
'*********************************************************
Dim Cel As Range
Dim Som As Double

If PlageCouleur.Cells.Count > 1 Then
SOMME_SI_COULEUR = CVErr(xlErrValue)
Exit Function
End If
For Each Cel In PlageSomme
If Cel.Interior.ColorIndex = PlageCouleur.Interior.ColorIndex Then Som = Som + Cel
Next
SOMME_SI_COULEUR = Som
End Function

Peut on inclure dans le module directement, l'actualisation automatique de la feuille? En liant l'actualisation aux champs et cellules définis dans le module? (à savoir ici PlageSomme, PlageCouleur...). J'y vois en effet plusieurs avantages :

- on s'affranchit du positionnement de la cellule de référence couleur

- on s'affranchit de la taille / position de la plage où les valeurs sont sommées

Bref : on rend la fonction autonome! En effet, votre solution marche sur l'exemple (et je suis déjà fort impressioné ). Toutefois, elle reste toujours conditionnée par la structure de l'exemple (position cellule référence, plage de référence, etc.) et est donc non transférable dans un autre fichier qui serait structuré différement. L'idée étant que cette fonction, si complètement autonome, pourrait être inclue dans de nombreux fichiers que j'utilise, chaque fois avec un format de feuille différent!

Merci de votre retour!

Bonsoir,

le code VBA porte bien son nom : c'est une fonction !

avec en paramètre une PlageSomme (qui correspond à une plage de cellule que l'on veut additionner)

une PlageCouleur (qui correspond à une cellule qui "donne" la couleur de référence pour faire la somme de la plage ci-dessus)

Hors vous voulez mettre cette fonction directement dans le module d'actualisation... (ceci dit dans le module d'actualisation, qu'il y est le code ou qu'on y fasse appel grâce au nom de la fonction c'est pareil)...pour qu'elle soit entièrement autonome.

Mais comment lui donnez vous les références des plages ?

Dans votre fichier de départ dans une cellule il est inscrit =SOMME_SI_COULEUR(D4:D11;F9)

plagesomme D4:D11 plagecouleur = F9

comment voulez vous que VBA sache cela si vous ne lui dites pas !!!! si vous créez un code de somme (ou d'autre chose) entièrement autonome alors Bingo pour vous !!!!!

en effet comme dire à VBA qu'il doit additionner F4 àH7 en fonction de la couleur de la cellule A23 sans lui donner de référence à ces plages ? VBA va gentiment regarder la feuille et deviner ce qu'il a à faire ?

Là je vous avoue je ne suis pas assez caler (si c'est possible) pour le faire....

Donc votre function pour qu'elle marche avec le code que j'ai fournie devra pouvoir trouver des cellules avec comme nom "_somme1" etc et connaître la zone de valeur à additionner d'une manière ou d'une autre, ce peut être aussi une zone nommée...

@ bientôt

LouReeD

ok merci. si ce n'est pas possible ce n'est pas grave..

Merci en tout cas pour l'aide!