ou mettre une formule dans VBA pour qu'elle execute dès modification d'une cellule

[quentinp38]

Bonjour,
J ai un fichier qui permet de traiter les problèmes en y associant les actions, suivi, acteur,...

j ai un code dans un module qui me calcule le nombre d'action pour un problème et la moyenne du pourcentage d'avancement des actions. Les resultats sont dans deux cellules fusionnées (regroupant toutes les actions).

Ce calcul se fait uniquement lorsque je rajoute une action au problème, mais je voudrais que le code ci dessous s'execute à chaque fois que le pourcentage d'avancement d'une action (valeur colonne AB) est modifié.

La colonne AB est une formule qui calcule le pourcentage d'avancement d'une action.

Ou est ce qu'il faut que je place ce code pour que dès qu'une valeur dans la colonne AB change (automatiquement avec la formule) le code s'execute.

merci

ci dessous le code (sortie de son module, pas tous ce qui a avant)


'calcul formule avancement

numero_action = Range("A" & LigneFin).Value
derniere_ligne = LigneFin
premiere_ligne = LigneDébut

Do While numero_action = Range("A" & derniere_ligne).Value
derniere_ligne = derniere_ligne + 1
Loop
derniere_ligne = derniere_ligne - 1

Do While numero_action = Range("A" & premiere_ligne).Value
premiere_ligne = premiere_ligne - 1
Loop

premiere_ligne = premiere_ligne + 1
Nb_action = (derniere_ligne - premiere_ligne) + 1
somme = 0
For r = premiere_ligne To derniere_ligne
somme = Range("AB" & r).Value + somme
Next r

moyenne = somme / Nb_action
Range("AC" & premiere_ligne & ":AC" & derniere_ligne).Value = Nb_action
Range("AD" & premiere_ligne & ":AD" & derniere_ligne).Value = moyenne

 

[pascal21]

Re : ou mettre une formule dans VBA pour qu'elle execute dès modification d'une cellu

bonjour
je ne suis pas un pro du VBA et je vais surement te dire une bétise mais comme je n'ai pas l'habitude de les laisser passer (les bétises)
je me lance
je mettrais ton code dans worksheets calculate
ça ne coûte rien d'essayer
tu aurais dû fournir un bout de ton fichier avec juste les codes permettant le fonctionnement des macros

 

[Gorfael]

Re : ou mettre une formule dans VBA pour qu'elle execute dès modification d'une cellu

Bonne année, bonne santé quentinp38 et le forum
Clic-droit sur l'onglet concerné => Menu Contextuel>>Visualiser le code
Tu te retrouves sur le module lié à la feuille
En Haut
Case Gauche la sélection des objets
Case droite la sélection des macros à lancement automatique liées à l'objet de la case de gauche.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel As Range, Plage As Range
Set Plage = Intersect(Target, Columns("AB"))
If Plage Is Nothing Then Exit Sub

For Each Cel In Plage
    'traitement de chaque cellule modifiées de AB
Next Cel
End Sub

Change est lancée à chaque changement (passage en mode édition d'une cellule) de la feuille.
Target les cellules changées : j'emploie toujours une boucle pour les traiter, ça évite les erreurs en cas de sélection multiple (pour effacer, par exemple)
A+
EDIT
après avoir lu le poste ci-dessus (bonne et heureuse année pascal21), je me suis aperçu que je ne traitais qu'une seule feuille (ce que semble indiquer le sujet). Pour plusieurs, il est préférable d'utiliser Workbook_SheetChange du module ThisWorkBook, avec Sh : variable feuille modifiée.

 

[quentinp38]

Re : ou mettre une formule dans VBA pour qu'elle execute dès modification d'une cellu

je te remercie, mais comme je ne suis pas non plus un expert de VBA mais est ce qu'on peut m'en dire un peu plus par rapport au déclenchement du code suite à la modification d'une valeur colonne AB et la detection de changement de la valeur
merci

 

[quentinp38]

Re : ou mettre une formule dans VBA pour qu'elle execute dès modification d'une cellu

Si je veux que cela fonctionne sur n'importe quelle cellule editer au lieu de la colonne AB , toutes la feuille comment faire ? car formule dans la colonne AB , la valeur change sans que la case soit édité donc ne se déclenche pas.
merci
sinon le reste marche nickel

 

[pascal21]

Re : ou mettre une formule dans VBA pour qu'elle execute dès modification d'une cellu

re
et bonsoir gorfael et bonne année

car formule dans la colonne AB , la valeur change sans que la case soit édité donc ne se déclenche pas

d'ou mon idée avec le worksheets calculate
calculate détecte tout nouveau calcul ou changement de valeur dans la feuille si je ne me trompe pas
mais je ne sais si ça convenait à ton cas

 

[Gorfael]

Re : ou mettre une formule dans VBA pour qu'elle execute dès modification d'une cellu

Salut quentinp38 et le forum
Désolé, la fatigue sans doute : effectivement si AB est une colonne de formule, pas beaucoup de résultat :alors la même avec une petite modification :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel As Range, Plage As Range

On Error Resume Next
'ne pas tenir compte des erreurs
Set Plage = Target
For Each Cel In Target
'Pour chaque cellule de la cible
    Set Plage = Union(Plage, Cel.Dependents)
Next Cel
On Error GoTo 0
'recommencer la gestion des erreurs

Set Plage = Intersect(Plage, Columns("AB"))
If Plage Is Nothing Then Exit Sub

For Each Cel In Plage
    'traitement de chaque cellule modifiées de AB
Next Cel
End Sub

Attention, ça suppose que les cellules faisant évoluer la colonne AB sont dans la même feuille.
L'annulation de la gestion d'erreur : Je n'ai pas encore trouvé comment gérer les dépendants qui n'existent pas.

Définition de plage :
Dans un premier temps, on prend Target, puis on lui ajoute toutes les cellules de la feuille contenant des formules dont Target est un des éléments

Après, on réinitialise la variable pour qu'elle ne contienne que les cellules appartenant à AB. Un exemple :
AB1 : =X1+X2+Y2+Z3 / AB2 : =X1+X2+Y3+Z4
- Je modifie Z3 => Plage devient successivement "Z3", "Z3,AB1", puis avec le filtre "AB1"
- Je modifie X2 => Plage devient successivement "X2", "X2,AB1:AB2", puis avec le filtre "AB1:AB2"
- Je modifie Y2
- Je modifie par copier/coller Z3 et Z4 => Plage devient "Z3:Z4", "Z3:Z4,AB1:AB2", puis "AB1:AB2"
- Dans mon exemple, je modifie W1 => Plage devient "W1", puis avec les dépendants "W1", puis avec le filtre devient nothing

Si les précédents à AB ne sont pas sur la feuille, il faut prévoir de faire un "Change sur la feuille qui les contient, en limitant l'action de la macro à ces seules cellules : soit on utilises WorkBook_SheetChange, soit chaque feuille contenant des cellules modifiant la colonne AB a une macro WorkSheet_Change qui lance une macro commune servant au traitement de la colonne AB.

c'est plus facile à faire sur un exemple concret que d'essayer de l'expliquer 😉
A+