Microsoft 365 worksheet_change

[CortoXls]

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.

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

 

[sylvanu]

Bonsoir CortoXls,
Vous n'auriez pas le petit fichier qui va avec ?
L'erreur peut aussi venir de la feuille ( erreur de nommage par ex ) et sans fichier ....

 

[CortoXls]

Bonsoir CortoXls,
Vous n'auriez pas le petit fichier qui va avec ?
L'erreur peut aussi venir de la feuille ( erreur de nommage par ex ) et sans fichier ....

Bonsoir,
Merci de votre réaction si rapide !

J'ai bien vérifié le nom de la feuille (copier-coller étant un bon réflexe !)
Idem pour la cellule nommée

Dans le fichier il y a beaucoup de données privées.
Je travaille sur une copie mais les données sont réelles et surtout très nombreuses.

Dans la 1ère procédure je parviens bien à calculer le total mais uniquement à l'activation de la feuille
J'aurai voulu que la mise à jour de la cellule se fasse "à la volée".
Ce qui est étonnant c'est que si j'écris la formule "en dur" dans la cellule ça fonctionne.
voici la formule : =SOMME(Tableau16[Taxe fonçière])
Je pourrais me contenter de ça mais je préfère chercher à comprendre ce qui bloque dans le code.

 

[sylvanu]

Re,

Dans le fichier il y a beaucoup de données privées.

Un fichier test est un fichier anonyme, et sans données sensibles.
C'est un peu d'huile de coude mais aide à trouver une solution, et surtout à tester.
Sinon c'est au pif, testez :

Total = Application.Sum([Tableau16[Taxe fonçière]])

 

[Dranreb]

Bonsoir.
Dans votre Sub Worksheet_Change, n'oubliez pas Application.EnableEvents = False avant tout changement de cellule, sinon c'est un coup à planter effectivement Excel par saturation de la pile des appels.

 

[CortoXls]

Re,

Un fichier test est un fichier anonyme, et sans données sensibles.
C'est un peu d'huile de coude mais aide à trouver une solution, et surtout à tester.
Sinon c'est au pif, testez :

Total = Application.Sum([Tableau16[Taxe fonçière]])

C'est ce que j'ai pris comme solution mais ça ne fonctionne pas.
Aliors j'ai écrit :

Range("Total_Taxe").Formula = "=Sum(E5:E16)"

Il me reste à trouver comment paramétrer E5 et E16...


Par contre je cherche à comprendre ce qui bloque dans le code...
Je pense à une interférence avec l'évènement change mais je ne vois pas comment contourner ce pb.
Simple question de curiosité...

 

[CortoXls]

Bonsoir.
Dans votre Sub Worksheet_Change, n'oubliez pas Application.EnableEvents = False avant tout changement de cellule, sinon c'est un coup à planter effectivement Excel par saturation de la pile des appels.

Même problème avec Application.EnableEvents = False, si j'écris Range("Total_Taxe")=Application.WorksheetFunction.Sum(Tbl.DataBodyRange.Columns(4))
excel plante...

 

[Dranreb]

Pourquoi ne calculez vous pas ça avec des formules de ligne de total ? Elles se mettraient à jour automatiquement !

 

[CortoXls]

Pourquoi ne calculez vous pas ça avec des formules de ligne de total ? Elles se mettraient à jour automatiquement !

Un exemple ?

 

[Dranreb]

En général quand j'essaie d'entrer une formule juste en dessous d'un tableau, et si elle n'existe pas déjà, il l'interprète comme une nouvelle ligne de total. C'est ballot, quand je voulais juste une ligne normale avec une formule …

 

[CortoXls]

Un exemple ?

Voilà la mise en forme actuelle sur laquelle je dois m'appuyer sans la modifier...

 

[Dranreb]

Tant pis alors. Mais vous pourriez toujours y mettre des formules à références structurées.

 

[sylvanu]

Re,
Testez :

Range("Total_Taxe").Formula = "=Sum(Tableau2[Taxe fonçière])"

Sinon, faites l'effort de nous donner un fichier test anonyme et sans données sensibles, sinon on va continuer à faire du pifomètre.

 

[CortoXls]

Re,
Testez :

Range("Total_Taxe").Formula = "=Sum(Tableau2[Taxe fonçière])"

Sinon, faites l'effort de nous donner un fichier test anonyme et sans données sensibles, sinon on va continuer à faire du pifomètre.

Voilà un extrait (finalement très simple à produire...)
Dans la 1ère procédure j'ai désactivé l'insertion de la formule :

Range("Total_Taxe").Formula = "=Sum(E5:E16)"

elle fonctionne mais je cherche à comprendre pourquoi excel plante si j'active dans la 2ème procédure la formule suivante :

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

Il y a quelque chose que je n'ai pas compris dans le déclenchement des évènements...

 

[Dranreb]

Je n'essayerais pas de l'exécuter. Ce serait un plantage assuré là.