Créer alerte auto qd les mêmes cellules de 2 feuilles successives sont remplies Fermé

Question

Bonjour à tous,

Je tente de résoudre un problème sur une macro assez simple, mais mon manque de connaissance me bloque assez rapidement. J’espère que quelqu’un pourra m’aider…

J’ai réalisé pour le travail un tableau de suivi mensuel des agents de mon service. Il comporte trois onglets identiques (les jours du mois correspondent aux mêmes cellules sur les trois onglets, et les agents aux mêmes lignes) qui me permettent :

-	Onglet 1 : de suivre les absences 
-	Onglet 2 : de programmer la participation de certains à la mission d’accueil
-	Onglet 3 : de programmer la participation de certains à des commissions

Il va de soi qu’un agent ne peut pas à la fois être en congé et à l’accueil de l’établissement. Pour faciliter la réalisation des plannings, et après quelques recherches sur internet (car je ne suis pas très calé en VBA) j’ai réalisé la macro suivante associée à chaque Feuille du classeur :


Private Sub Worksheet_Change (ByVal Target As Range)
If Application.Intersect (Target, ActiveCell) Is Nothing Then
If Not IsEmpty (Worksheets ("Nom de la feuille 2"). Range (Target.Address)) Then MsgBox ("Saisie impossible. L'agent s'est engagé sur l'accueil à cette date !")
ElseIf Not IsEmpty (Worksheets ("Nom de la feuille 3"). Range (Target.Address)) Then MsgBox ("Saisie impossible. L'agent est engagé sur une commission d'aides financières !")
End If
End Sub


Ainsi, si je tente de saisir un congé pour un agent qui doit être à l’accueil sur la demi-journée concernée, ou inversement, une alerte s’affiche.

Ça fonctionne pas mal, sauf qu’à certains moments la macro marche à l’envers ! Aucune alerte ne s’affiche si j’effectue une saisie théoriquement impossible…. Mais elle s’affiche quand je supprime cette saisie ??

J’ai essayé avec les deux syntaxes « If Application.Intersect » et « If Not Application.Intersect » mais ça ne change rien….

Si vous avez une solution, je suis preneur ????

Merci !

Utilisateur anonyme · Answer

Bonjour,
Il y a des incohérences dans votre traitement. Par exemple si on a déjà placé l'agent à une commission et qu'il vient à être en congé (maladie ou autre, je suppose), il faut que son placement à l'accueil ou dans une commission soit effacée. A moins que ce soit une erreur de le positionner en congé et dans ce cas, il faut abandonner l'opération.

Par ailleurs, vous ne pouvez pas placer exactement le même code dans les 3 onglets : si je place l'agent à l'accueil, il faut faire 2 tests distincts:  vérifier qu'il n'est pas en congé ET qu'il n'est pas positionnée dans une commission. Ce n'est donc pas un ELSE If qu'il faut mais un If.

Le test avec Application.Intersect (Target, ActiveCell) Is Nothing n'a pas de raison d'être : Target reçoit la cellule que l'on vient de modifier de fait c'est ActiveCell.
Le test devrait plutôt restreindre la vérification à une plage de cellules (par défaut, c'est toute cellule de la feuille qui déclenche le _Change) : donc pour limiter la plage à vérifier on écrira if not Application.Intersect (Target, Plage_à_contrôler) Is Nothing Then...

Donc l'idée à mettre en œuvre c'est :
- si agent en congé, l'enlever de l'accueil ou d'une commission s'il avait déjà été positionné + éventuellement signaler dans Accueil et Commission qu'il est en congé 
- si agent à l'accueil : vérifier qu'il n'est pas en congé ou en commission (sinon annuler la saisie que l'on vient de faire dans Accueil)
-Si agent en commission : vérifier qu'il n'est pas en congé ou à l'accueil (sinon annuer la saisie que l'on vient de faire en commission).

C'est loin d'être optimisé mais ça pourrait ressembler à ceci, à coller dans ThisWorkbook  à la place des routines de chaque feuille ET remplacer "Feuil1, 2 et 3" par les noms de vos feuilles :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)   
    If Not IsEmpty(Target) Then 'Ou mieux If not intersect(Target,plage_à_surveiller) is Nothing then
      Select Case Sh.Name
        Case "Feuil1"
            If Not IsEmpty(Worksheets("Feuil2").Range(Target.Address)) Then
              MsgBox ("L'agent est en congé ! Sa participation à l'accueil va être effacée!")
              Worksheets("Feuil2").Range(Target.Address).Value = ""
            End If
            If Not IsEmpty(Worksheets("Feuil3").Range(Target.Address)) Then
              MsgBox ("L'agent est en congé ! Sa participation aux commissions va être effacée !")
              Worksheets("Feuil3").Range(Target.Address).Value = ""
            End If
        Case "Feuil2"
          If Not IsEmpty(Worksheets("Feuil1").Range(Target.Address)) Then
            MsgBox ("Saisie impossible. L'agent est en congé !")
            Target.Value = ""
          End If
          If Not IsEmpty(Worksheets("Feuil3").Range(Target.Address)) Then
            MsgBox ("Saisie impossible. L'agent est engagé sur une commission d'aides financières !")
            Target.Value = ""
          End If
          
        Case "Feuil3"
          If Not IsEmpty(Worksheets("Feuil1").Range(Target.Address)) Then
            MsgBox ("Saisie impossible. L'agent est en congé !")
            Target.Value = ""
          End If
          If Not IsEmpty(Worksheets("Feuil2").Range(Target.Address)) Then
            MsgBox ("Saisie impossible. L'agent est à l'accueil !")
            Target.Value = ""
          End If
      End Select
    End If
End Sub

Jeannot_77140 · Answer

Bonsoir RoMa_31,

Tout d'abord, merci beaucoup d'avoir pris le temps de me faire une réponse aussi complète et détaillée. Vous avez parfaitement compris ce que je voulais faire et allez même encore plus loin !

Toutefois, j'ai peut-être commis une ou plusieurs erreurs en recopiant le code ci-dessous (désolé, je ne sais pas l'insérer proprement dans mon message comme vous l'avez fait). En effet, j'obtiens les anomalies suivantes :

- Si je saisi un accueil un jour de congé : la MsgBox "Saisie impossible..." s'affiche en boucle et je dois forcer la fermeture d'Excel !
- idem pour la saisie d'une commission un jour d'accueil ou de congé...
- Si je saisi un congé un jour où l'agent devait être à l'accueil, la MsgBox s'affiche, mais lorsque je clique sur "Ok", le message [Erreur d’exécution '1004'. La méthode 'Intersect' de l'objet '_Global' a échoué]

Voici le code avec les ajustements que vous m'avez conseillés :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Intersect(Target, Range("N6:BW30")) Is Nothing Then
          Select Case Sh.Name
        Case "ABSENCES"
            If Not IsEmpty(Worksheets("ACCUEIL").Range(Target.Address)) Then
              MsgBox ("L'agent est en congé ! Sa participation à l'accueil va être effacée!")
              Worksheets("ACCUEIL").Range(Target.Address).Value = ""
            End If
            If Not IsEmpty(Worksheets("COMMISSION").Range(Target.Address)) Then
              MsgBox ("L'agent est en congé ! Sa participation aux commissions va être effacée !")
              Worksheets("COMMISSION").Range(Target.Address).Value = ""
            End If
        Case "ACCUEIL"
          If Not IsEmpty(Worksheets("ABSENCES").Range(Target.Address)) Then
            MsgBox ("Saisie impossible. L'agent est en congé !")
            Target.Value = ""
          End If
          If Not IsEmpty(Worksheets("COMMISSION").Range(Target.Address)) Then
            MsgBox ("Saisie impossible. L'agent est engagé sur une commission d'aides financières !")
            Target.Value = ""
          End If
          
        Case "COMMISSION"
          If Not IsEmpty(Worksheets("ABSENCES").Range(Target.Address)) Then
            MsgBox ("Saisie impossible. L'agent est en congé !")
            Target.Value = ""
          End If
          If Not IsEmpty(Worksheets("ACCUEIL").Range(Target.Address)) Then
            MsgBox ("Saisie impossible. L'agent est à l'accueil !")
            Target.Value = ""
          End If
      End Select
    End If
End Sub


Auriez-vous une idée de ce qui bloque ?.... J'ai l'impression que nous ne sommes pas très loin de la solution...

Utilisateur anonyme · Answer

Bonjour,

1) Pour afficher le code avec la coloration syntaxique en fonction du langage, il faut utiliser les balises de code de l'éditeur :  

Dans notre cas, on choisira donc BASIC : soit sélectionner le texte puis choisir les balises pour encadre le texte soit insérer les balises puis ajouter le texte à l'intérieur. C'est expliqué en détail ici : https://codes-sources.commentcamarche.net/faq/11288-les-balises-de-code

2) J'ai codé un peu trop vite... pour éviter ce gros bug, il faut écrire : If Not Intersect(Target, Range("N6:BW30")) Is Nothing And Not IsEmpty(Target) Then

Je ne doute pas qu'on puisse faire mieux comme programmation même si ça devrait fonctionner avec cette correction.

Si vous avez besoin de plus d'aide l'idéal serait de mettre à disposition une copie de votre fichier dans lequel les données personnelles ou confidentielles auront été remplacées par des valeurs fictives.
Comme on ne peut pas mettre de fichier en pièce jointe, il faut utiliser un service d'hébergement puis mettre le lien du partage dans votre message.
Sur ce forum; l'habitude est d'utiliser https://www.cjoint.com/ mais tout autre service fera l'affaire.

PS : Ma réponse ne prends pas en compte le problème le message [Erreur d’exécution '1004'. La méthode 'Intersect' de l'objet '_Global' a échoué] Je dois partir travailler : je verrai cela ce soir à moins que quelqu'un d'autre prenne le relais entre temps !
Bonne continuation

Utilisateur anonyme · Answer

Voilà ce que je propose comme modifications en début de la procédure. J'ai mis des commentaires dans le code pour l'expliciter :
 If Target.Count > 1 Then Exit Sub ' Evite un bug lorsqu'on sélectionne plusieurs cellules pour (copier)-coller du contenu ou suppression. Ca plante dans le traitement qui suit car on suppose qu'une seule cellule est concernée.
   
 If Not Intersect(Target, Sh.Range("N6:BW30")) Is Nothing And Not IsEmpty(Target) Then ' L'ajout de Sh à .Range("N6:BW30") évite le bug [Erreur d’exécution '1004'. La méthode 'Intersect' de l'objet '_Global' a échoué]

L'erreur 1004 est dans ce cas liée au fait que le programme fait référence à une "mauvaise plage" en l'occurrence sans doute celle de la feuille précédemment sélectionnée alors qu'on a changé d'objet entre temps.

Jeannot_77140 · Answer

Bonjour RoMa_31

Super, ça fonctionne parfaitement avec cette dernière modification ! ("Sh.Range")

Mille fois merci !

Merci également pour l'info sur l'affichage du code dans les messages ;-)

Une dernière petite question. Quel est selon vous le moyen le plus efficace de progresser en VBA. Il existe des bouquins sur le sujet ? Ou faut-il passer par des formations ? J'aimerais bien devenir plus autonome sur le sujet...

Jean

Jeannot_77140 · Answer

Bonjour RoMa_31,

Merci pour ces précieux conseils et à bientôt sans doute sur le forum....

Créer alerte auto qd les mêmes cellules de 2 feuilles successives sont remplies

6 réponses

Discussions similaires