Microsoft 365 worksheet_change

Créer une discussion
Répondre

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

S'inscrire
C

CortoXls

XLDnaute Junior
Bonjour à tous,

Voici les codes sur la feuille "Loyers"
La 1ère procédure fonctionne très bien. Elle peut sans doute être améliorée mais ça fonctionne.

Code: 
Private Sub Worksheet_Activate() 'Feuille Loyers
Dim Tbl As ListObject
Dim PLoyer, PTaxe, plage, cell As Range
Dim i, colL, colT, MoisNum, Lastline, ligne, Derligne As Integer

Application.ScreenUpdating = False
Application.EnableEvents = False

Me.Unprotect

Set Tbl = Me.ListObjects("Tableau16")
Set PLoyer = Tbl.ListColumns("Loyer").DataBodyRange
Set PTaxe = Tbl.ListColumns("Taxe fonçière").DataBodyRange
Set plage = Union(PLoyer, PTaxe, Range("Total_Loyers"), Range("Total_Taxe"))
Set cell = PTaxe.Rows(1)

colL = Tbl.ListColumns("Loyer").DataBodyRange.Column
colT = Tbl.ListColumns("Taxe fonçière").DataBodyRange.Column

ligne = Tbl.HeaderRowRange.Row + 1 '1ère ligne du tableau
Derligne = Tbl.ListColumns("Mois").DataBodyRange.Count + ligne - 1

MoisNum = 0

For i = ligne To Derligne
    If MoisNum <= 12 Then
        MoisNum = MoisNum + 1
        Lastline = Sheets(MoisNum).Range("A:J").Find("*", , , , xlByRows, xlPrevious).Row
        Cells(i, colL) = WorksheetFunction.SumIfs(Sheets(MoisNum).Range("H7:H" & Lastline), Sheets(MoisNum).Range("D7:D" & Lastline), "Loyer*")
    End If
Next i

Range("Total_Loyers") = Application.WorksheetFunction.Sum(Tbl.DataBodyRange.Columns(2))
Range("Total_Taxe") = Application.WorksheetFunction.Sum(Tbl.DataBodyRange.Columns(4))

    With plage 'Mise en forme des cellules
        .NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"  'Nombre sous format comptabilité
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlCenter
        .AddIndent = False
        .IndentLevel = 1
    End With

Application.ScreenUpdating = True
Application.EnableEvents = True

cell.Select
If cell = "" Then MsgBox ("Renseignez l'échéancier")

Call ProtectFeuil

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Tbl As ListObject
Dim plage, TotalT As Range
Dim i, colT, ligne, Derligne As Integer

Me.Unprotect

Set Tbl = Me.ListObjects("Tableau16")
Set TotalT = Me.Range("Total_Taxe")
colT = Tbl.ListColumns("Taxe fonçière").DataBodyRange.Column
ligne = Tbl.HeaderRowRange.Row + 1 '1ère ligne du tableau
Derligne = Tbl.ListColumns("Mois").DataBodyRange.Count + ligne - 1

If Not Intersect(Target, Range(Cells(ligne, colT), Cells(Derligne, colT))) Is Nothing Then
    If Target.Value = "" Then
        Target.Offset(0, -1).Value = ""
        With Target.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0.599993896298105
            .PatternTintAndShade = 0
        End With
    Else
        With Target.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        
        If Target.Value > 0 Then
            Target.Offset(0, -1).Value = "dont"
        Else
            Target.Offset(0, -1).Value = ""
        End If
    End If
End If

Call ProtectFeuil

End Sub

Dans la procédure worksheet_change je voudrais que la cellule Range("Total_Taxe") se mette à jour au fur et à mesure du remplissage de la colonne 4 du tableau16
Mais lorsque j'écris la formule : Range("Total_Taxe") = Application.WorksheetFunction.Sum(Tbl.DataBodyRange.Columns(4)) dans cette procédure, Excel plante !

j'ai essayé en passant par worksheet_calculate() mais sans succès et je suis pas sûr du fonctionnement de cette procédure.

Merci de votre aide et de vos explications détaillées sur mes erreurs dans mon code
 
Trier par date Trier par votes
Re,
Sur mon PC c'est ok avec :
VB: 
Range("Total_Loyers").Formula = "=Sum(Tableau16[Loyer])"
Range("Total_Taxe").Formula = "=Sum(Tableau16[Taxe fonçière])"
Une possibilité de non fonctionnement est que vous mettez les formules uniquement en cas de changement de feuille ( Worksheet_Activate ) mais pas en cas de changement de valeur (Worksheet_Change).
Le plus simple est d'implémenter ces formules dans le Worksheet_Change.
Sans oublier les Application.EnableEvents = False/True dans le Worksheet_Change.
 

Pièces jointes

Vote positif 0 Vote négatif
sylvanu à dit:
Re,
Ceci dit, pourquoi ne pas mettre en "dur" les formules dans la feuille :
VB: 
C18 : =SOMME(Tableau16[Loyer])
E20 : =SOMME(Tableau16[Taxe fonçière])
Les formules s'adapteront automatiquement à la taille du tableau. Et ça simplifie le VBA.
Cliquez pour agrandir...
Bonjour,
C'est ce que j'ai fait pour que le fichier puisse etre utilisé.
Mais, hé oui il y a un mais, je voudrai essayer de comprendre ce qui bloque.
Je n'aime pas rester sur un échec par incompréhension...
Je pense que c'est aussi la raison d'être de ce site et de la présence des aimants comme vous. Et je trouve votre implication formidable. Je me suis un peu promené sur le forum...
 
Vote positif 0 Vote négatif
Bonjour.
Vous ne semblez pas avoir compris que si vous changez le contenu d'une cellule dans une Sub Worksheet_Change, ça provoque immédiatement, avec un nouveau jeu de variables locales, la réexécution à son début de cette même Sub. Le processus peu vite se reconduire jusqu'à saturation de l'espace mémoire alloué aux variables locales.
Qu'il y ait ou non d'autres causes de plantage supplémentaires, il faut avant tout éviter cela en mettant des instructions Application.EnableEvents = False avant chaque instruction modifiant une plage de la feuille, et Application_EnableEvents = True derrière pour que ça ne laisse pas ultérieurement inactive la prise en charge des évènements Excel.
 
Dernière édition:
Vote positif 0 Vote négatif
Dranreb à dit:
Bonjour.
Vous ne semblez pas avoir compris que si vous changez le contenu d'une cellule dans une Sub Worksheet_Change, ça provoque immédiatement, avec un nouveau jeu de variables locales, la réexécution à son début de cette même Sub. Le processus peu vite se reconduire jusqu'à saturation de l'espace mémoire alloué aux variables locales.
Qu'il y ait ou non, d'autres causes de plantage supplémentaires, il faut avant tout éviter cela en mettant des instruction Application.EnableEvents = False avant chaque instruction modifiant une plage de la feuille, et Application_EnableEvents = True derrière pour que ça ne laisse pas ultérieurement inactive la prise en charge des évènement Excel.
Cliquez pour agrandir...
Bonjour Dranreb,
Merci de vous pencher sur mon sujet !

Sur le fichier transmis j'ai essayé en encadrant la formule avec ces instructions mais j'ai toujours le même problème.
J'ai donc enlevé les instructions.
Par contre je pense qu'il y a un lien avec la réélection de la procédure
Je vais réessayé en en rajoutant au niveau des premières instructions.
PS : si l'écriture de l'ensemble du code peut être améliorée n'hésitez pas à m'expliquer. J'apprends et j'ai du temps (retraite et il pleut...)
 
Vote positif 0 Vote négatif
Bonjour,
@CortoXls
Avez vous essayé la PJ du post #16, sur mon PC c'est ok.
Si ça bloque chez vous c'est qu'il y a un problème autre.
Qu'entendez vous par "Ca plante" ?
Dans votre dernière PJ j'ai mis
VB: 
Range("Total_Loyers") = Application.WorksheetFunction.Sum(Tbl.DataBodyRange.Columns(2))
Range("Total_Taxe") = Application.WorksheetFunction.Sum(Tbl.DataBodyRange.Columns(4))
et rajouté les EnableEvents qui vont bien dans Worksheet_Change, qui étaient encore absent, et tout marche.
Si je selectionne Feuil1 puis feuille Loyers alors les deux cases sont bien mises à jour.
 
Vote positif 0 Vote négatif
sylvanu à dit:
Bonjour,
@CortoXls
Avez vous essayé la PJ du post #16, sur mon PC c'est ok.
Si ça bloque chez vous c'est qu'il y a un problème autre.
Qu'entendez vous par "Ca plante" ?
Dans votre dernière PJ j'ai mis
VB: 
Range("Total_Loyers") = Application.WorksheetFunction.Sum(Tbl.DataBodyRange.Columns(2))
Range("Total_Taxe") = Application.WorksheetFunction.Sum(Tbl.DataBodyRange.Columns(4))
et rajouté les EnableEvents qui vont bien dans Worksheet_Change, qui étaient encore absent, et tout marche.
Si je selectionne Feuil1 puis feuille Loyers alors les deux cases sont bien mises à jour.
Cliquez pour agrandir...
Je n'ai pas encore testé votre solution
Je vous tiens au courant !
 
Vote positif 0 Vote négatif
Re,
Pour bien comprendre je vous ai fait deux gifs en pas à pas.
Je modifie E5 sans EnableEvents=False :
Test4.gif

A chaque fois qu'on écrit dans Total_loyers, on ré-éxécute Worksheet_Change, donc jamais on n'écrit dans Total_Taxes et ça finit par se planter par débordement de la pile.
Maintenant j'ai encadré les écritures avec EnableEvents=False et EnableEvents=true :

Comme les events sont masquées les deux écritures ont bien lieu et on continue la macro.
Test5.gif
 
Vote positif 0 Vote négatif
sylvanu à dit:
Re,
Pour bien comprendre je vous ai fait deux gifs en pas à pas.
Je modifie E5 sans EnableEvents=False :
Regarde la pièce jointe 1213762
A chaque fois qu'on écrit dans Total_loyers, on ré-éxécute Worksheet_Change, donc jamais on n'écrit dans Total_Taxes et ça finit par se planter par débordement de la pile.
Maintenant j'ai encadré les écritures avec EnableEvents=False et EnableEvents=true :

Comme les events sont masquées les deux écritures ont bien lieu et on continue la macro.
Regarde la pièce jointe 1213764
Cliquez pour agrandir...
Et donc la formule pour total_taxe peut être rédigée sous la même forme que total-salaire...
Il y a-t-il d'autres améliorations a envisager ?
De plus il y a-t-il un tuto pour apprendre à faire des gif ?
Merci d'avance
 
Dernière édition:
Vote positif 0 Vote négatif
Re,
Avez vous compris le piège de la ré-entrance montré avec les gifs ?
On peut peut être simplifier avec ( mais non tout testé ) :
VB: 
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect
Application.EnableEvents = False
[Total_Loyers] = Application.Sum([Tableau16[Loyer]])
[Total_Taxe] = Application.Sum([Tableau16[Taxe fonçière]])
If Not Intersect(Target, [Tableau16[Taxe fonçière]]) Is Nothing Then
    ' Traitement mise en forme
End If
Call ProtectFeuil
Application.EnableEvents = True
End Sub
En utilisant la syntaxe [Tableau xxx[Colonne yyy]] cela évite de faire des recherches sur plage.

NB: Vous remarquerez que sans PJ il était impossible de trouver la solution, le problème n'était pas du tout sur la ligne que vous incriminiez. 🙂
 
Vote positif 0 Vote négatif
sylvanu à dit:
Re,
Avez vous compris le piège de la ré-entrance montré avec les gifs ?
On peut peut être simplifier avec ( mais non tout testé ) :
VB: 
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect
Application.EnableEvents = False
[Total_Loyers] = Application.Sum([Tableau16[Loyer]])
[Total_Taxe] = Application.Sum([Tableau16[Taxe fonçière]])
If Not Intersect(Target, [Tableau16[Taxe fonçière]]) Is Nothing Then
    ' Traitement mise en forme
End If
Call ProtectFeuil
Application.EnableEvents = True
End Sub
En utilisant la syntaxe [Tableau xxx[Colonne yyy]] cela évite de faire des recherches sur plage.

NB: Vous remarquerez que sans PJ il était impossible de trouver la solution, le problème n'était pas du tout sur la ligne que vous incriminiez. 🙂
Cliquez pour agrandir...
sylvanu à dit:
Re,
Avez vous compris le piège de la ré-entrance montré avec les gifs ?
On peut peut être simplifier avec ( mais non tout testé ) :
VB: 
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect
Application.EnableEvents = False
[Total_Loyers] = Application.Sum([Tableau16[Loyer]])
[Total_Taxe] = Application.Sum([Tableau16[Taxe fonçière]])
If Not Intersect(Target, [Tableau16[Taxe fonçière]]) Is Nothing Then
    ' Traitement mise en forme
End If
Call ProtectFeuil
Application.EnableEvents = True
End Sub
En utilisant la syntaxe [Tableau xxx[Colonne yyy]] cela évite de faire des recherches sur plage.

NB: Vous remarquerez que sans PJ il était impossible de trouver la solution, le problème n'était pas du tout sur la ligne que vous incriminiez. 🙂
Cliquez pour agrandir...
Super ça marche très bien et le code est bien plus simple !
 
Vote positif 0 Vote négatif
Vote positif 0 Vote négatif
Vote positif 0 Vote négatif
Rejoins nos membres "Supporter XLD" et booste ton expérience Excel !
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

N
  • Résolu(e)
Microsoft 365 Modification impossible d'une TextBox
2 3 4
Réponses
56
Affichages
1 K
NONO14
N
J
  • Question Question
XL 2021 transfert listview vers feuille excel
Réponses
9
Affichages
147
Piment
P
N
  • Résolu(e)
Microsoft 365 Calcul de plages horaires
2 3 4
Réponses
49
Affichages
1 K
NONO14
N
F
  • Question Question
Microsoft 365 aide VBA excel
Réponses
4
Affichages
368
Oneida
Oneida
G
  • Question Question
Microsoft 365 Export données
Réponses
4
Affichages
513
GClaire
G
T
  • Question Question
Microsoft 365 erreur syntaxe pour coller sous un tableau
Réponses
5
Affichages
351
Dranreb
Dranreb
lynyrd
  • Question Question
XL 2019 code pour tous les disques
Réponses
3
Affichages
608
AtTheOne
AtTheOne
N
  • Résolu(e)
Microsoft 365 Transfère de données d'un Userform vers un tableau structuré d'une feuille de calcul
Réponses
4
Affichages
302
NONO14
N
G
  • Résolu(e)
XL 2016 Pb de tri et sous totaux
2
Réponses
27
Affichages
365
Gestionnaire_rh
G
E
  • Résolu(e)
XL 2019 transfer listbox vers une colonne à partir de la 4 ligne
Réponses
2
Affichages
190
escudo
E
M
  • Question Question
Microsoft 365 Erreur sur mon Code VBA
Réponses
2
Affichages
426
Gégé-45550
Gégé-45550
G
  • Question Question
XL 2016 Extraire des données d'un pdf et les copier dans excel
2
Réponses
19
Affichages
1 K
kiki29
kiki29
cathodique
  • Résolu(e)
XL 2010 Module de classe avec Dictionary
3 4 5
Réponses
66
Affichages
3 K
patricktoulon
patricktoulon
Nicolas JACQUIN
  • Question Question
XL 2021 Exportation feuille msgbox yes no avec module Thisworkbook
2
Réponses
21
Affichages
1 K
Nicolas JACQUIN
Nicolas JACQUIN
F
  • Question Question
Microsoft 365 Poursuivre ou pas la macro
Réponses
2
Affichages
428
Francky79
F
M
  • Résolu(e)
XL 2016 comment créer une Barre de défilement ou faire simplement défiler son texte ?
2
Réponses
25
Affichages
1 K
melltel
M
S
  • Question Question
Microsoft 365 créer un macro vba
Réponses
0
Affichages
386
souhaib ad
S
S
  • Question Question
XL 2021 macro VBA excel
Réponses
1
Affichages
474
crocrocro
C
G
  • Résolu(e)
XL 2021 VBA dates au mauvais format
Réponses
5
Affichages
335
vgendron
vgendron
halecs93
  • Résolu(e)
XL 2016 Indice n'appartient pas à la sélection : HELP !!!!
2
Réponses
21
Affichages
1 K
halecs93
halecs93
Retour