Créer alerte auto qd les mêmes cellules de 2 feuilles successives sont remplies
Jeannot_77140
Messages postés
5
Date d'inscription
Statut
Membre
Dernière intervention
-
Jeannot_77140 Messages postés 5 Date d'inscription Statut Membre Dernière intervention -
Jeannot_77140 Messages postés 5 Date d'inscription Statut Membre Dernière intervention -
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 !
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 !
A voir également:
- Créer alerte auto qd les mêmes cellules de 2 feuilles successives sont remplies
- Comment créer un groupe whatsapp - Guide
- Créer un compte google - Guide
- Créer un lien pour partager des photos - Guide
- Comment faire un livret avec des feuilles a4 - Guide
- Créer un compte gmail - Guide
6 réponses
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 :
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
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...
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...
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 :
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
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
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 :
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.
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.
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
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
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
De rien et merci pour ce retour : tant mieux si ça répond à votre besoin.
Il existe sans aucun doute des ouvrages sur la question : une rapide recherche sur le site de la fnac donne : https://www.fnac.com/SearchResult/ResultList.aspx?SCat=0%211&Search=programmation+vba&sft=1&sa=0
Vous trouverez aussi des formations sur le net ou des tutoriels avec exemples de codes suffisamment commentés pour apprendre tout seul.
Pour ne pas multiplier les références (vous en trouverez bien d'autres), je propose : https://vb.developpez.com/faqvba/ ; https://www.excel-pratique.com/fr/vba ; https://openclassrooms.com/fr/courses/825502-analysez-des-donnees-avec-excel/822888-premiers-pas-en-vba. et bien entendu CCM ;-) https://www.commentcamarche.net/faq/visual-basic-88
Et surtout, ne pas oublier la documentation du VBA accessible depuis Excel.
Par exemple en surlignant le mot Intersect dans un programme puis en appuyant sur F1 (help) on arrive sur la description de cette méthode avec des exemples de code : https://docs.microsoft.com/fr-fr/office/vba/api/excel.application.intersect?f1url=%3FappId%3DDev11IDEF1%26l%3Dfr-FR%26k%3Dk%28vbaxl10.chm183099%29%3Bk%28TargetFrameworkMoniker-Office.Version%3Dv16%29%26rd%3Dtrue
Si vous avez déjà des notions de programmation, vous pourrez transférer certaines compétences en utilisant l'enregistreur de macro d'Excel puis en analysant et en adaptant le code produit automatiquement.
L'idéal est toutefois d'avoir un véritable projet, donc un cahier des charges puis d'agir avec méthode :
- savoir choisir entre formules d'Excel ou macro (en gros je fais en macro tout ce que je ne peux pas faire autrement)
- penser voire écrire l'algorithme du programme
- le découper en sous parties
- écrire plusieurs procédures ou des fonctions qui réalisent des portions de tâches complémentaires plutôt qu'un programme monobloc, etc.
C'est très schématique mais les grandes lignes y sont me semble-t-il.
Il existe sans aucun doute des ouvrages sur la question : une rapide recherche sur le site de la fnac donne : https://www.fnac.com/SearchResult/ResultList.aspx?SCat=0%211&Search=programmation+vba&sft=1&sa=0
Vous trouverez aussi des formations sur le net ou des tutoriels avec exemples de codes suffisamment commentés pour apprendre tout seul.
Pour ne pas multiplier les références (vous en trouverez bien d'autres), je propose : https://vb.developpez.com/faqvba/ ; https://www.excel-pratique.com/fr/vba ; https://openclassrooms.com/fr/courses/825502-analysez-des-donnees-avec-excel/822888-premiers-pas-en-vba. et bien entendu CCM ;-) https://www.commentcamarche.net/faq/visual-basic-88
Et surtout, ne pas oublier la documentation du VBA accessible depuis Excel.
Par exemple en surlignant le mot Intersect dans un programme puis en appuyant sur F1 (help) on arrive sur la description de cette méthode avec des exemples de code : https://docs.microsoft.com/fr-fr/office/vba/api/excel.application.intersect?f1url=%3FappId%3DDev11IDEF1%26l%3Dfr-FR%26k%3Dk%28vbaxl10.chm183099%29%3Bk%28TargetFrameworkMoniker-Office.Version%3Dv16%29%26rd%3Dtrue
Si vous avez déjà des notions de programmation, vous pourrez transférer certaines compétences en utilisant l'enregistreur de macro d'Excel puis en analysant et en adaptant le code produit automatiquement.
L'idéal est toutefois d'avoir un véritable projet, donc un cahier des charges puis d'agir avec méthode :
- savoir choisir entre formules d'Excel ou macro (en gros je fais en macro tout ce que je ne peux pas faire autrement)
- penser voire écrire l'algorithme du programme
- le découper en sous parties
- écrire plusieurs procédures ou des fonctions qui réalisent des portions de tâches complémentaires plutôt qu'un programme monobloc, etc.
C'est très schématique mais les grandes lignes y sont me semble-t-il.