Problèmes d'interactions de PrivateSub OnChange et PrivateSub BeforeClose

Bonjour,

J'ai un fichier Excel avec 2 Private Sub qui sont enregistrées sur un onglet :
- 1 Private Sub On Change
- 1 Private Sub Before Close

----------------------
Ma Private Sub On Change :

Set isect = Application.Intersect(Target, Range("S:T"))
If isect Is Nothing Then
Exit Sub
End If

ActiveCell.Select
NumeroDeLigne = ActiveCell.Row

Dim NomFeuilleExcelTracking As Worksheet
Dim DerniereLigneTracking As Integer
Set NomFeuilleExcelTracking = Sheets("TRACKING")
DerniereLigneTracking = NomFeuilleExcelTracking.Cells(Application.Rows.Count, "A").End(xlUp).Row

Set isect = Application.Intersect(Target, Range("S:S"))

If isect Is Nothing Then
GoTo ModificationZone2
Exit Sub

ElseIf Range("S" & NumeroDeLigne).Value = "A prendre en charge" & Range("T" & NumeroDeLigne).Value = "Sans affectation" Then
Exit Sub

ElseIf Range("S" & NumeroDeLigne).Value = "Ne pas traiter" & Range("T" & NumeroDeLigne).Value = "Sans affectation" Then
Exit Sub

ElseIf Range("S" & NumeroDeLigne).Value = "En attente" & Range("T" & NumeroDeLigne).Value = "Sans affectation" Then
Exit Sub

ElseIf Range("S" & NumeroDeLigne).Value = "" Then
Range("S" & NumeroDeLigne).Value = "A prendre en charge"
GoTo Tracking
Exit Sub

ElseIf Range("S" & NumeroDeLigne).Value = "Affecté" Then
If Range("T" & NumeroDeLigne).Value = Application.UserName Then
GoTo Tracking
Exit Sub
End If

If Range("T" & NumeroDeLigne).Value = "Sans affectation" Then
Range("T" & NumeroDeLigne).Value = Application.UserName
GoTo Tracking
Exit Sub
End If

If Range("T" & NumeroDeLigne).Value <> Application.UserName Then
Exit Sub
End If

ElseIf Range("S" & NumeroDeLigne).Value = "Affecté et en attente" Then
If Range("T" & NumeroDeLigne).Value = Application.UserName Then
GoTo Tracking
Exit Sub
End If

If Range("T" & NumeroDeLigne).Value = "Sans affectation" Then
Range("T" & NumeroDeLigne).Value = Application.UserName
GoTo Tracking
Exit Sub
End If

If Range("T" & NumeroDeLigne).Value <> Application.UserName Then
Exit Sub
End If

ElseIf Range("S" & NumeroDeLigne).Value = "Affecté et ne pas traiter" Then
If Range("T" & NumeroDeLigne).Value = Application.UserName Then
GoTo Tracking
Exit Sub
End If

If Range("T" & NumeroDeLigne).Value = "Sans affectation" Then
Range("T" & NumeroDeLigne).Value = Application.UserName
GoTo Tracking
Exit Sub
End If

If Range("T" & NumeroDeLigne).Value <> Application.UserName Then
Exit Sub
End If

ElseIf Range("S" & NumeroDeLigne).Value = "Affecté" Then
Exit Sub

ElseIf Range("S" & NumeroDeLigne).Value = "Affecté et en attente" Then
Exit Sub

ElseIf Range("S" & NumeroDeLigne).Value = "Affecté et ne pas traiter" Then
Exit Sub

ElseIf Range("S" & NumeroDeLigne).Value = "Prise en charge en cours" & Range("T" & NumeroDeLigne).Value = Application.UserName Then
Exit Sub

ElseIf Range("S" & NumeroDeLigne).Value = "Prise en charge en cours" Then
Range("T" & NumeroDeLigne).Value = Application.UserName
GoTo Tracking
Exit Sub

ElseIf Range("S" & NumeroDeLigne).Value = "Clôturé" & Range("T" & NumeroDeLigne).Value = Application.UserName Then
Exit Sub

ElseIf Range("S" & NumeroDeLigne).Value = "Clôturé" Then
Range("T" & NumeroDeLigne).Value = Application.UserName
GoTo Tracking
Exit Sub

ElseIf Range("S" & NumeroDeLigne).Value = "A prendre en charge" & Range("T" & NumeroDeLigne).Value = "" Then
Range("T" & NumeroDeLigne).Value = "Sans affectation"
GoTo Tracking
Exit Sub

ElseIf Range("S" & NumeroDeLigne).Value = "A prendre en charge" & Range("T" & NumeroDeLigne).Value <> "" Then
Range("T" & NumeroDeLigne).Value = "Sans affectation"
GoTo Tracking
Exit Sub

End If

ModificationZone2:

Set isect = Application.Intersect(Target, Range("T:T"))
If isect Is Nothing Then
Exit Sub

ElseIf Range("T" & NumeroDeLigne).Value = "" Then
Range("S" & NumeroDeLigne).Value = "A prendre en charge"
GoTo Tracking
Exit Sub

ElseIf Range("T" & NumeroDeLigne).Value = "Sans affectation" Then
If Range("S" & NumeroDeLigne).Value = "Ne pas traiter" Then
Exit Sub
End If
If Range("S" & NumeroDeLigne).Value = "En attente" Then
Exit Sub
End If
If Range("S" & NumeroDeLigne).Value <> "A prendre en charge" Then
Range("S" & NumeroDeLigne).Value = "A prendre en charge"
GoTo Tracking
Exit Sub
End If

ElseIf Range("S" & NumeroDeLigne).Value = "A prendre en charge" Then
If Range("T" & NumeroDeLigne).Value = "Sans affectation" Then
Exit Sub
End If

If Range("T" & NumeroDeLigne).Value = "" Then
Exit Sub
End If

If Range("T" & NumeroDeLigne).Value = Application.UserName Then
Range("S" & NumeroDeLigne).Value = "Affecté"
GoTo Tracking
Exit Sub
End If

If Range("T" & NumeroDeLigne).Value <> Application.UserName Then
Range("S" & NumeroDeLigne).Value = "Affecté"
GoTo Tracking
Exit Sub
End If

ElseIf Range("S" & NumeroDeLigne).Value = "Ne pas traiter" Then
If Range("T" & NumeroDeLigne).Value = "Sans affectation" Then
Exit Sub
End If

If Range("T" & NumeroDeLigne).Value = "" Then
Exit Sub
End If

If Range("T" & NumeroDeLigne).Value = Application.UserName Then
Range("S" & NumeroDeLigne).Value = "Affecté"
GoTo Tracking
Exit Sub
End If

If Range("T" & NumeroDeLigne).Value <> Application.UserName Then
Range("S" & NumeroDeLigne).Value = "Affecté"
GoTo Tracking
Exit Sub
End If

ElseIf Range("S" & NumeroDeLigne).Value = "En attente" Then
If Range("T" & NumeroDeLigne).Value = "Sans affectation" Then
Exit Sub
End If

If Range("T" & NumeroDeLigne).Value = "" Then
Exit Sub
End If

If Range("T" & NumeroDeLigne).Value = Application.UserName Then
Range("S" & NumeroDeLigne).Value = "Affecté"
GoTo Tracking
Exit Sub
End If

If Range("T" & NumeroDeLigne).Value <> Application.UserName Then
Range("S" & NumeroDeLigne).Value = "Affecté"
GoTo Tracking
Exit Sub
End If

ElseIf Range("S" & NumeroDeLigne).Value = "Affecté" Then
If Range("T" & NumeroDeLigne).Value <> Application.UserName Then
Exit Sub
End If
If Range("T" & NumeroDeLigne).Value = Application.UserName Then
Range("S" & NumeroDeLigne).Value = "Affecté"
GoTo Tracking
Exit Sub
End If

ElseIf Range("S" & NumeroDeLigne).Value = "Affecté et en attente" Then
If Range("T" & NumeroDeLigne).Value <> Application.UserName Then
Exit Sub
End If
If Range("T" & NumeroDeLigne).Value = Application.UserName Then
Range("S" & NumeroDeLigne).Value = "Affecté et en attente"
GoTo Tracking
Exit Sub
End If

ElseIf Range("S" & NumeroDeLigne).Value = "Affecté et ne pas traiter" Then
If Range("T" & NumeroDeLigne).Value <> Application.UserName Then
Exit Sub
End If
If Range("T" & NumeroDeLigne).Value = Application.UserName Then
Range("S" & NumeroDeLigne).Value = "Affecté et ne pas traiter"
GoTo Tracking
Exit Sub
End If

ElseIf Range("S" & NumeroDeLigne).Value = "En attente" Then
If Range("T" & NumeroDeLigne).Value = Application.UserName Then
Range("S" & NumeroDeLigne).Value = "Affecté"
GoTo Tracking
Exit Sub
End If

ElseIf Range("S" & NumeroDeLigne).Value = "Ne pas traiter" Then
If Range("T" & NumeroDeLigne).Value = Application.UserName Then
Range("S" & NumeroDeLigne).Value = "Prise en charge en cours"
GoTo Tracking
Exit Sub
End If

ElseIf Range("S" & NumeroDeLigne).Value = "Prise en charge en cours" Then
If Range("T" & NumeroDeLigne).Value = Application.UserName Then
Exit Sub
End If

If Range("T" & NumeroDeLigne).Value = Application.UserName Then
Range("S" & NumeroDeLigne).Value = "Prise en charge en cours"
GoTo Tracking
Exit Sub
End If

If Range("T" & NumeroDeLigne).Value <> Application.UserName Then
Range("S" & NumeroDeLigne).Value = "Affecté"
GoTo Tracking
Exit Sub
End If

ElseIf Range("S" & NumeroDeLigne).Value = "Affecté" Then
If Range("T" & NumeroDeLigne).Value <> Application.UserName Then
Range("S" & NumeroDeLigne).Value = "Affecté"
GoTo Tracking
Exit Sub
End If

ElseIf Range("S" & NumeroDeLigne).Value = "Affecté et en attente" Then
If Range("T" & NumeroDeLigne).Value <> Application.UserName Then
Range("S" & NumeroDeLigne).Value = "Affecté et en attente"
GoTo Tracking
Exit Sub
End If

ElseIf Range("S" & NumeroDeLigne).Value = "Affecté et ne pas traiter" Then
If Range("T" & NumeroDeLigne).Value <> Application.UserName Then
Range("S" & NumeroDeLigne).Value = "Affecté et ne pas traiter"
GoTo Tracking
Exit Sub
End If

ElseIf Range("S" & NumeroDeLigne).Value = "Clôturé" Then
If Range("T" & NumeroDeLigne).Value = Application.UserName Then
Exit Sub
End If

If Range("T" & NumeroDeLigne).Value = Application.UserName Then
Range("S" & NumeroDeLigne).Value = "Affecté"
GoTo Tracking
Exit Sub
End If

If Range("T" & NumeroDeLigne).Value <> Application.UserName Then
Range("S" & NumeroDeLigne).Value = "Affecté"
GoTo Tracking
Exit Sub
End If
End If

Tracking:
NomFeuilleExcelTracking.Range("A" & DerniereLigneTracking + 1).Value = Application.UserName
NomFeuilleExcelTracking.Range("B" & DerniereLigneTracking + 1).Value = Date
NomFeuilleExcelTracking.Range("C" & DerniereLigneTracking + 1).Value = Format(Time(), "HH:MM:SS")
NomFeuilleExcelTracking.Range("D" & DerniereLigneTracking + 1).Value = Range("B" & NumeroDeLigne).Value
NomFeuilleExcelTracking.Range("E" & DerniereLigneTracking + 1).Value = Range("S" & NumeroDeLigne).Value
NomFeuilleExcelTracking.Range("F" & DerniereLigneTracking + 1).Value = Range("T" & NumeroDeLigne).Value
Exit Sub

Edit : merci de mettre le code entre balises, avec le bouton </>

----------------------

Ma Private Sub Before Close :

Dim NomFeuilleExcelEnQuittant As Worksheet
Dim DerniereLigneEnQuittant As Integer
Set NomFeuilleExcelEnQuittant = Sheets("test")
DerniereLigneEnQuittant = NomFeuilleExcelEnQuittant.Cells(Application.Rows.Count, "A").End(xlUp).Row

Dim x As Integer

For x = 2 To DerniereLigneEnQuittant Step 1
If Range("T" & x).Value = Application.UserName Then
If Range("S" & x).Value <> "Clôturé" Then
Range("S" & x).Value = "A prendre en charge"
End If
End If
Next x

Edit : merci de mettre le code entre balises, avec le bouton </>

----------------------

J'ai 7 utilisateurs qui travaillent en direct dans ce fichier partagé avec Excel 365 (en mode fichier partagé).
C'est pour un service de support clients, donc chaque ligne représente un ticket ou une demande client qu'il faut traiter que je gère en 2 colonnes :
- 1 colonne S avec le statut du ticket
- 1 colonne T avec le QUI

J'ai donc tous les cas de figure suivants de possibles :
- Colonne S : Prise en charge en cours;A prendre en charge;Affecté;Affecté et en attente;Affecté et ne pas traiter;En attente;Ne pas traiter;Clôturé
- Colonne T: Tous les noms d'utilisateurs possibles que je rentre via ma macro en dur en liste déroulante.

99% du temps ils ne changent que le contenu de la liste déroulante de la colonne S (le statut) et ma macro On Change fait un contrôle sur le contenu de la colonne S pour mettre à jour la colonne T avec des critères. Exemple si l'utilisateur clique dans la colonne S sur "Clôturé" alors la colonne T sur le même ligne récupère son nom d'utilisateur et le met à jour dans la colonne "T".

Voici mes questions :
- Je suis débutant dans le VBA donc je pense que mes macros ne sont pas du tout optimisées... (c'est déjà un 1er point :))
- Le On Change semble bien fonctionner, j'ai beau faire tous les essais dans tous les sens, ça ne plante pas et ça met à jour les bonnes cellules à chaque fois comme je veux
- Mais lorsque mon Before Close se lance, c'est là que ca coince... Mon before Close doit vérifier qu'un utilisateur ne quitte pas le fichier en ayant gardé un ticket "Prise en charge en cours" par exemple... Si il s'en va je veux que le ticket soit libéré pour tous les autres utilisateurs qui devront le prendre en charge. Donc mon Before Close doit contrôler le contenu de le cellule S de chaque ligne, si il trouve quelque chose de différent que "Clôturé" et le Application.UserName concerné par l'utilisateur en cours, alors je veux qu'il change le contenu de la cellule S en "A prendre en charge". Le pb c'est que lorsqu'il tombe sur une ligne concerné, le Before Close change le contenu de la colonne S ce qui génère le lancement de ma Macro On Change qui elle réagit en faisant des contrôles sur la modification apportée en S pour mettre à jour T (ce qui est très bien car oui si S est "A prendre en charge" alors T doit être "Sans affectation"), mais ma Macro Before Close a continué entre temps et est déjà rendue à l'autre ligne suivante qu'il trouve et du coup ca va trop vite pour Excel ces 2 macros qui interragissent entre elles...

J'ai aussi un onglet "TRACKING" qui me dit quelles cellules sont changées en direct et je vois bien que le contenu qu'il change ne correspond pas à chaque ligne concerné, il me répond que ce n'est que pour la 1ère ligne qu'il a trouvée... Alors que j'ai besoin que cet onglet tracking me disent que chaque ligne trouvée et modifiée par le BeforeClose soit donc désormais S ="A prendre en charge" et T="Sans affectation" en toute logique, ce qui n'est pas le cas, il me dit que c'est toujours pour la 1ère ligne qu'il a trouvé dans le BeforeClose.

J'espère que vous aurez compris mon message car ce n'est pas simple à expliquer.
Si jamais quelqu'un accepte de m'aider à optimiser mes 2 codes pour qu'ils puissent interagir correctement entre eux, merci d'avance (possibilité de s'appeler si vous le souhaitez, me laisser un message en privé).

Merci d'avance à la communauté.

Bonjour Aurelien,

Pour éviter les déconvenues avec le OnChange, il faut mettre : Application.EnableEvents = False

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Dim NomFeuilleExcelEnQuittant As Worksheet
  Dim DerniereLigneEnQuittant As Integer
  Set NomFeuilleExcelEnQuittant = Sheets("test")
  DerniereLigneEnQuittant = NomFeuilleExcelEnQuittant.Cells(Application.Rows.Count, "A").End(xlUp).Row

  Dim x As Integer

  For x = 2 To DerniereLigneEnQuittant Step 1
    If Range("T" & x).Value = Application.UserName Then
      If Range("S" & x).Value <> "Clôturé" Then
        Application.EnableEvents = False
        Range("S" & x).Value = "A prendre en charge"
        Application.EnableEvents = True
      End If
    End If
  Next x
End Sub

Pour le reste, je cède ma place

@+

Merci pour la réponse. Je viens d'essayer et en effet cette astuce me permettrait bien de mettre à jour toutes mes cellules de la colonnes S comme il faut mais par contre mon onglet TRACKING ne se met plus à jour, ce qui est logique. Or j'ai besoin que si la valeur de ma cellule S change alors ça renseigne mon onglet de tracking... Quelqu'un aurait-il une idée ? Merci

J'ai essayé de mettre "

 Application.EnableEvents = False

" Et ensuite de recopier dans mon Before Close tout le contenu de mon On Change, mais j'ai une erreur d'éxecution de mon Before Close qui me dit que mon isect est vide, il semble que je ne peux pas utiliser isect dans mon Before Close...