Organiser les congés sur un calendrier annuel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Organiser les congés des salariés

Dans une formation Excel précédente, nous avons conçu un outil puissant et ergonomique permettant de livrer la synthèse des absences pour un salarié désigné, sur un calendrier annuel. Ici, nous souhaitons aller plus loin et améliorer encore l'ergonomie et la souplesse de l'application. Le responsable doit pouvoir inscrire de nouvelles périodes d'absences, ou supprimer des congés finalement refusés ou déplacés, par simples sélections des dates dans le calendrier.



Dès lors, un clic sur le bouton Gérer, dans la barre d'outils sur la gauche de la feuille, affiche une interface graphique. Après avoir désigné la nature de l'indisponibilité grâce à la liste déroulante, il peut archiver ces nouvelles absences par simple clic sur le bouton Ajouter. A validation, les nouvelles dates sont instantanément et dynamiquement repérées, dans un jeu de couleurs fidèle au code de la légende. Mais vous l'avez remarqué, l'interface est aussi dotée d'un bouton Supprimer. Son action a pour effet d'éliminer les dates des archives. Du fait des règles de format dynamique que nous avions mises en place, à validation, les périodes réservées redeviennent automatiquement libres.

Source et présentation du concept
Avant de débuter ces travaux, il est bien sûr fondamental de récupérer les précédents développements. Le calendrier annuel se construit automatiquement et réagit dynamiquement en fonction du salarié désigné, pour repérer ses absences.

• Télécharger le classeur organiser-conges-salaries.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous l'avions déjà constaté, ce classeur est composé de plusieurs feuilles. La feuille active par défaut est le support de nos travaux. Elle se nomme Calendrier. Si vous choisissez une année à l'aide de la première liste déroulante en BD8, ici en l'occurrence l'année 2020 pour les congés archivés, et un salarié à l'aide de la seconde liste déroulante en BD11, le calendrier annuel se recompose en surlignant toutes les périodes d'absence de l'employé.



De même, vous remarquez l'intégration d'un bouton (Créer), sous ces deux listes déroulantes. Il doit permettre d'archiver les nouvelles périodes mentionnées par simples sélections des dates dans le calendrier.

Les absences en question sont toutes énumérées et stockées dans la feuille Conges.



C'est donc cette base de données qui doit être enrichie par le code VBA, au gré des ajouts de nouvelles périodes.

Un calcul intermédiaire de repérage est produit en colonne E. C'est grâce à lui que toutes les données correspondant à l'année et au salarié désignés depuis le calendrier, sont isolées dans la feuille Extraction. Cette technique permet de simplifier les règles de correspondance pour la mise en forme conditionnelle des périodes d'absence.

Le code VBA Excel devra aussi proposer de supprimer des périodes qui avaient été posées. Il va donc s'agir de supprimer des lignes entières de la base de données. Et avec elles, les calculs disparaîtront au fur et à mesure. C'est pourquoi, à l'issue de cette formation, nous bâtirons le code rendant l'application totalement autonome et donc, indépendante des formules.

Afficher un formulaire sur ordre
Au clic sur le bouton Gérer de la feuille Calendrier, nous devons offrir des outils à l'administrateur. Ces outils doivent lui permettre de définir la nature des absences pour les dates désignées, avant de valider. Mais ils doivent aussi lui permettre de supprimer les absences archivées, pour la période sélectionnée. Ces fonctionnalités doivent être agencées dans un formulaire à afficher.

• En haut de la fenêtre Excel, cliquer sur l'onglet Développeur pour afficher son ruban,
• Dans la section Contrôles du ruban, cliquer sur le bouton Mode création,
• Dès lors, sous les deux listes déroulantes, double cliquer sur le bouton Gérer de la feuille,

Grâce à cette action, nous basculons dans l'éditeur de code Visual Basic Excel, plus précisément entre les bornes de la procédure événementielle Gerer_Click. Son code VBA se déclenchera donc au clic sur le bouton. Et ce code doit afficher le formulaire des outils. A ce titre, en consultant l'explorateur de projet sur la gauche de l'écran, vous constatez qu'il existe déjà. Il se nomme Outils et il est rangé dans la catégorie Feuilles.

• Dans cet explorateur, double cliquer sur le formulaire Outils pour l'afficher,

Il est doté d'une liste déroulante permettant de définir la nature de l'absence. Le bouton Ajouter qui lui est associé, doit déclencher le traitement permettant d'archiver les nouvelles périodes de congés. Un bouton Supprimer est de même proposé. Comme nous le disions, il doit autoriser à effacer des périodes anciennement définies et remises en cause.

• Dans l'explorateur de projet, déployer l'arborescence du dossier Modules,

Deux modules de codes VBA existent déjà. Ils se nomment respectivement Module1 et Module2.

• Double cliquer sur l'élément Module1 pour afficher son code,

Il offre la fonction Reperer.

Function reperer() As Integer
Dim feuille As Worksheet

reperer = 3
Set feuille = Sheets('conges')

Do While feuille.Cells(reperer, 2).Value <> ''

reperer = reperer + 1

If reperer > 10000 Then Exit Do
Loop

End Function

Elle est destinée à repérer et retourner la dernière ligne de la base de données des congés. Grâce à elle, nous saurons où nous positionner pour poursuivre l'implémentation des archives. Comme il s'agit de traitements que nous avons reproduits à grande échelle dans diverses formations, nous proposons ici de l'offrir.

Le module2 héberge quant à lui une procédure nommée extraire. Nous l'exploiterons pour remplacer les calculs intermédiaires et rendre l'application totalement autonome. Elle se charge en effet d'isoler dans la feuille Extraction, tous les enregistrements concordant avec l'année et le salarié désignés depuis le calendrier.

• Dans l'explorateur de projet, double cliquer sur l'élément Feuil82(Calendrier),

Nous revenons ainsi sur la feuille de code hébergeant la procédure événementielle Gerer_Click. Ce bouton appartient en effet à la feuille Calendrier.

• Entre les bornes de la procédure événementielle, ajouter l'instruction VBA suivante :

Outils.Show

Outils est le nom d'objet du formulaire. Il suffit de consulter sa feuille de propriétés pour le constater. Show est la méthode associée qui permet de l'afficher à l'écran.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Calendrier,
• Dans le ruban Développeur, cliquer le bouton Mode création pour désactiver la conception,
• Puis, cliquer sur le bouton Gérer,

Comme vous le remarquez, le UserForm apparaît aussitôt. Mais il n'est absolument pas fonctionnel à ce stade. Tout est à développer. D'ailleurs, sa liste déroulante vide de choix, en atteste.

Contrôler les dates sélectionnées
Avant de procéder, au chargement du formulaire, nous devons réaliser quelques contrôles et quelques réglages. Les contrôles consistent à savoir si les cellules sélectionnées sont bien des dates du calendrier. Les réglages consistent notamment à remplir la liste déroulante des choix sur la nature de l'indisponibilité.

• Cliquer sur la croix de la fenêtre du UserForm pour fermer le formulaire,
• Revenir dans l'éditeur de code VBA Excel,
• Dans l'explorateur de projet, double cliquer sur l'élément Outils,
• Double cliquer sur un emplacement vide du formulaire pour basculer dans sa feuille de code,

Nous créons ainsi la procédure événementielle suivante :

Private Sub UserForm_Click()

End Sub

Mais nous ne souhaitons pas déclencher le code VBA au clic sur le formulaire. Nous souhaitons l'exécuter au chargement de ce dernier.

• En haut de la feuille de code, déployer la liste déroulante de droite,
• Dans les propositions, choisir l'événement Activate,

Nous générons ainsi la procédure événementielle UserForm_Activate. C'est elle qui intime l'ordre au code VBA d'agir au chargement du formulaire.

• Supprimer la procédure UserForm_Click en effaçant ses lignes,

Nous devons bien sûr commencer par déclarer et initialiser les variables nécessaires à ces contrôles.

• Entre les bornes de la procédure UserForm_Activate, ajouter les déclarations suivantes :

...
Dim nom As String
Dim cellule As Range: Dim test As Boolean

test = False: nom = Range('BD11').Value
...

La variable nom est déclarée comme un String pour prélever l'information textuelle sur le salarié désigné. C'est pourquoi elle est ensuite affectée au contenu de la cellule BD11 : nom = Range('BD11').Value. La variable cellule est déclarée comme un objet de type range. Elle représente une cellule ou une plage de la feuille au sens large. Nous l'exploiterons pour scruter le contenu des cellules sélectionnées avant d'agir. L'objectif est de contrôler la présence de dates. La variable test est déclarée comme un booléen. Elle doit être utilisée pour confirmer le succès du contrôle. Le cas échéant, le formulaire devra être fermé, en l'absence de données cohérentes à traiter.

Désormais, nous devons parcourir l'ensemble des cellules sélectionnées, à la recherche de cohérence. Et c'est une boucle For Each, apprise lors d'une formation, qui permet de les analyser, dans l'ordre de la sélection.

• A la suite du code de la procédure, ajouter les instructions suivantes :

...
For Each cellule In Selection

If (cellule.Value <> '' And IsDate(cellule.Value) And Len(cellule.Value) = 10) Then
test = True
Exit For
End If

Next cellule

If test = False Then
MsgBox 'Désolé aucune date à archiver n'est fournie dans la sélection'
Outils.Hide
End If
...

Nous exploitons l'objet VBA Selection. Comme son nom l'indique, il représente la collection des cellules sélectionnées au moment de l'exécution. Selection est un objet de type Range. C'est pourquoi, nous pouvons exploiter notre objet cellule dans la boucle For Each, afin de les parcourir toutes. Nous réalisons un triple test. La cellule en cours ne doit pas être vide (cellule.Value <> ''). Son contenu doit être une date. Nous le vérifions grâce à la fonction booléenne IsDate. Enfin, cette date doit nécessairement être composée de dix caractères (jj/mm/aaaa) : Len(cellule.Value) = 10. En effet, d'autres dates sont présentes sur le calendrier, en titres des mois.

Si ce triple test est un succès, nous basculons la variable booléenne à True et mettons fin à l'exécution de la boucle (Exit For). Nous avons désormais la certitude qu'au moins l'une des cellules sélectionnées peut être traitée.

Si après cette boucle, la variable booléenne est restée dans son état d'origine (False), nous en concluons qu'aucune donnée cohérente ne peut être exploitée. Nous en avertissons l'utilisateur par la fonction MsgBox et masquons le formulaire grâce à sa méthode Hide.

Il ne nous reste plus qu'à inscrire quelques indications et charger la liste déroulante.

• A la suite de la procédure événementielle, ajouter les instructions VBA suivantes :

...
Invite1.Caption = 'Ajouter des congés pour le salarié : ' & nom
liste_conges.Clear
liste_conges.AddItem 'CP'
liste_conges.AddItem 'RTT'
liste_conges.AddItem 'ABS'
...

Dans le premier Label, nommé Invite1, nous inscrivons une indication avec le nom du salarié sélectionné, pour rappel. Nous vidons la liste déroulante de son potentiel contenu, grâce à sa méthode Clear. C'est ensuite sa méthode AddItem qui permet de la remplir tour à tour des éléments désirés.

Il est temps de tester le comportement de ce formulaire.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Calendrier,
• Sélectionner des cellules en dehors des mois puis cliquer sur le bouton Gérer,

Comme vous le constatez, le message d'avertissement apparaît, jugeant les données choisies incorrectes. A validation de la boîte de dialogue, le formulaire disparaît aussitôt.

• Sélectionner désormais quelques dates dans l'un des mois du calendrier,
• Puis, cliquer sur le bouton Gérer,

Cette fois, la sélection est jugée conforme. Et en même temps que le formulaire se charge, ses contrôles s'initialisent. La liste déroulante propose bien les choix nécessaires à émettre avant de procéder à l'enregistrement des nouvelles périodes.

Le code VBA complet de la procédure UserForm_Activate est le suivant :

Private Sub UserForm_Activate()
Dim nom As String
Dim cellule As Range: Dim test As Boolean

test = False: nom = Range('BD11').Value

For Each cellule In Selection

If (cellule.Value <> '' And IsDate(cellule.Value) And Len(cellule.Value) = 10) Then
test = True
Exit For
End If

Next cellule

If test = False Then
MsgBox 'Désolé aucune date à archiver n'est fournie dans la sélection'
Outils.Hide
End If

Invite1.Caption = 'Ajouter des congés pour le salarié : ' & nom
liste_conges.Clear
liste_conges.AddItem 'CP'
liste_conges.AddItem 'RTT'
liste_conges.AddItem 'ABS'

End Sub

Archiver les périodes d'absence
Tout est fin prêt pour enclencher le traitement consistant à inscrire chacune des nouvelles dates sélectionnées, dans la feuille conges, pour le salarié choisi. Et ce traitement doit intervenir au clic sur le bouton Ajouter.

• Fermer le UserForm en cliquant sur la croix de son onglet,
• Puis, revenir dans l'éditeur de code VBA Excel,
• Sur le formulaire en conception, double cliquer sur le bouton Ajouter,

Nous générons ainsi la procédure événementielle Ajouter_Click.

Private Sub Ajouter_Click()

End Sub

• Entre les bornes de la procédure Ajouter_Click, ajouter les instructions suivantes :

...
Dim nom As String: Dim ligne_ext As Integer
Dim cellule As Range: Dim feuille As Worksheet

If liste_conges.Value = '' Then
MsgBox 'Vous devez préciser la nature de l'absence avec la liste déroulante'

Else

End If
...

Les fonctions des variables nom et cellule sont identiques à celles de la procédure précédente. Nous déclarons de même la variable ligne_ext comme un entier. Elle doit servir à repérer la ligne de la base de données, à partir de laquelle les nouvelles inscriptions peuvent se faire. Nous déclarons ensuite la variable feuille comme un objet de type Worksheet. Il s'agit donc d'un objet à initialiser pour désigner et manipuler une feuille précise du classeur.

Avant de débuter le traitement, nous réalisons un contrôle de routine. Grâce à la propriété Value de l'objet liste_conges (La liste déroulante), nous nous assurons qu'un choix a bien été émis sur la nature de l'absence. Si tel n'est pas le cas, nous en avertissons l'utilisateur grâce à la fonction MsgBox. De fait, le code est interrompu puisque le traitement est destiné à se déclencher dans la branche Else de l'instruction conditionnelle.

Dans le cas où l'indication est bien formulée, nous devons enclencher le traitement. Après initialisation des variables, il s'agit de passer de nouveau en revue toutes les dates sélectionnées. Et pour chaque date conforme, l'inscription sur le nom, la date et la nature de l'absence doit avoir lieu à la suite, dans la base de données de la feuille conges.

• Dans la branche Else de l'instruction conditionnelle, ajouter les lignes VBA suivantes :

...
Set feuille = Sheets('conges')
ligne_ext = reperer: nom = Range('BD11').Value
For Each cellule In Selection

If (cellule.Value <> '' And IsDate(cellule.Value) And Len(cellule.Value) = 10) Then
feuille.Cells(ligne_ext, 2).Value = nom
feuille.Cells(ligne_ext, 3).Value = cellule.Value
feuille.Cells(ligne_ext, 4).Value = liste_conges.Value
ligne_ext = ligne_ext + 1
End If

Next cellule
...

Nous initialisons l'objet feuille sur la feuille conges du classeur. C'est désormais par ce nom de variable que nous allons pouvoir pointer dessus plus facilement. Nous exploitons la fonction reperer du module1, présentée en début de formation, pour récupérer la dernière ligne de la base de données. Cette information est désormais stockée dans la variable ligne_ext.

Nous enclenchons ensuite une boucle For Each, comme précédemment, afin de parcourir toutes les dates sélectionnées. Avant de procéder à leur inscription, nous nous assurons une fois encore comme précédemment, qu'elles sont bien conformes. Dès lors, nous n'avons plus qu'à pointer sur les colonnes respectives de la base de données, pour la ligne en cours (ligne_ext), afin d'y inscrire les données récoltées (feuille.Cells(ligne_ext, 2).Value = nom).

Pour terminer, que le traitement soit enclenché ou non, nous devons fermer le formulaire à l'issue.

• Après le End If et Avant le End Sub, ajouter les instructions VBA suivantes :

...
Outils.Hide
Range('A4').Activate
...

Très simplement, nous masquons le formulaire puis sélectionnons une cellule située sur les lignes supérieures de la feuille. Cette action a pour effet de désactiver la sélection des dates.

• Enregistrer les modifications et basculer sur la feuille Calendrier,
• A l'aide de la liste déroulante en BD11, choisir le salarié Doeuf John,

Pour ce dernier, aucun congé n'a encore été enregistré. Et à ce titre, vous voyez le calendrier annuel réagir parfaitement. Il se vide de toute mise en forme de couleur.

• Sélectionner quelques dates dans l'un des mois, par exemple la plage de cellules AF23:AJ:24,

Nous désignons ainsi les deux dernières semaines du mois de Juillet 2020 en excluant les jours de Week End.

• Puis, cliquer sur le bouton Gérer,
• Déployer la liste déroulante du UserForm et choisir l'option CP,
• Cliquer ensuite sur le bouton Ajouter,

Presqu'aussitôt, le formulaire disparaît et la plage de dates sélectionnée est mise en valeur, conformément aux couleurs proposées par la légende.

Notre code VBA a inscrit ces nouvelles informations dans la feuille Conges.



Vous pouvez le constater en affichant les derniers enregistrements de cette dernière. Le relai est pris par nos précédents travaux. Un calcul de repérage s'enclenche dans la dernière colonne. Il est exploité pour isoler ces enregistrements dans la feuille Extraction. Et les règles de mise en forme conditionnelle établissent la correspondance par rapport à cette dernière, pour faire réagir les cases du calendrier dynamiquement.

Le code VBA complet de la procédure Ajouter_Click est le suivant :

Private Sub Ajouter_Click()
Dim nom As String: Dim ligne_ext As Integer
Dim cellule As Range: Dim feuille As Worksheet

If liste_conges.Value = '' Then
MsgBox 'Vous devez préciser la nature de l'absence avec la liste déroulante'

Else

Set feuille = Sheets('conges')
ligne_ext = reperer: nom = Range('BD11').Value
For Each cellule In Selection

If (cellule.Value <> '' And IsDate(cellule.Value) And Len(cellule.Value) = 10) Then
feuille.Cells(ligne_ext, 2).Value = nom
feuille.Cells(ligne_ext, 3).Value = cellule.Value
feuille.Cells(ligne_ext, 4).Value = liste_conges.Value
ligne_ext = ligne_ext + 1
End If

Next cellule

End If

Outils.Hide
Range('A4').Activate

End Sub

Supprimer des périodes de congés
Nous devons maintenant développer le code associé au bouton Supprimer. Il doit se charger d'effacer tous les enregistrements de la base de données, pour lesquels les dates et le nom du salarié concordent. Comme nous le disions en préambule de cette formation, à terme ces traitements auront pour effet d'éliminer les calculs posés, du fait de la suppression des lignes. C'est pourquoi à l'issue, nous appellerons la procédure extraire du module2. Son code se charge de réaliser l'extraction, comme les formules, à partir de l'année et du salarié.

• Revenir dans l'éditeur de code VBA Excel,
• Sur le formulaire en conception, double cliquer sur le bouton Supprimer,

Nous créons ainsi la procédure événementielle Supprimer_Click :

Private Sub Supprimer_Click()

End Sub

• Entre les bornes de la procédure Supprimer_Click, ajouter le code VBA suivant :

...
Dim nom As String
Dim ligne_ext As Integer
Dim cellule As Range: Dim feuille As Worksheet

Set feuille = Sheets('conges')
nom = Range('BD11').Value

For Each cellule In Selection

If (cellule.Value <> '' And IsDate(cellule.Value) And Len(cellule.Value) = 10) Then
ligne_ext = 3

End If

Next cellule

extraire

Outils.Hide
Range('A4').Activate
...

Ces instructions sont pour l'instant semblables à celles de la procédure précédente. Nous déclarons les variables nécessaires et notamment l'objet feuille sur la feuille Conges. Nous parcourons l'ensemble des cellules sélectionnées grâce à une boucle For Each. Nous testons leur cohérence grâce à un triple test. Nous initialisons la variable ligne_ext à l'indice 3. Il s'agit en effet de la première ligne de la base de données à parcourir. Le traitement de nettoyage devra suivre.

Après la boucle et donc potentiellement la suppression des dates sélectionnées pour le salarié, nous appelons la procédure extraire. Elle isole les données du salarié choisi sur le calendrier pour l'année définie. C'est ainsi que le format conditionnel peut continuer de réagir dynamiquement. A l'issue, nous masquons le formulaire et activons une cellule en haut de la feuille.

Désormais, dans l'instruction conditionnelle de la boucle, nous devons passer en revue tous les enregistrements de la base de données, à la recherche de correspondances conjointes sur le nom du salarié et des dates.

• Dans l'instruction conditionnelle, après l'affectation de la variable ligne_ext, ajouter le code VBA suivant :

...
Do While feuille.Cells(ligne_ext, 2).Value <> ''

If (feuille.Cells(ligne_ext, 2).Value = nom And feuille.Cells(ligne_ext,3).Value = cellule.Value) Then
feuille.Cells(ligne_ext, 2).EntireRow.Delete
Exit Do 'Pour passer à la date suivante sélectionnée
Else
ligne_ext = ligne_ext + 1
End If
Loop
...

Grâce à une boucle While, nous parcourons l'ensemble des enregistrements de la feuille Conges, en partant de la ligne 3 (ligne_ext). Ce traitement récursif se poursuit en effet tant qu'une cellule non vide est détectée (Cells(ligne_ext,2).Value <> ''). C'est pourquoi, à chaque passage, nous n'oublions pas d'incrémenter la variable de boucle (ligne_ext = ligne_ext + 1). Ainsi, nous progressons ligne à ligne. Pour chacune d'elles, grâce à un double test, nous vérifions la correspondance sur le salarié et la date en cours. Nous sommes en effet à l'intérieur d'une boucle For Each qui énumère toutes les dates sélectionnées.

Si la correspondance est avérée, nous supprimons l'intégralité de la ligne soit de l'enregistrement, grâce à la méthode Delete de la propriété EntireRow de l'objet Cells. Après une suppression, le pointeur se place automatiquement sur la ligne du dessous. C'est pourquoi l'incrémentation de la variable de boucle ne doit pas intervenir. Puis, nous mettons fin à la boucle Do While (Exit Do) pour recommencer le test avec une nouvelle date livrée par la boucle For Each.

Dans lecas en revanche où la correspondance n'est pas établie (Else), nous poursuivons l'analyse sur les lignes du dessous (ligne_ext = ligne_ext + 1).

• Enregistrer les modifications et basculer sur la feuille Calendrier,
• Sélectionner les deux dernières semaines de Juillet pour le salarié Doeuf John,
• Cliquer sur le bouton Gérer puis sur le bouton Supprimer du UserForm,

Aussitôt posées, les vacances du salarié ont été annulées. Comme vous avez pu le voir, la mise en forme conditionnelle a disparu. En effet, si vous consultez la base de données des congés, ses enregistrements ont bien été supprimés. Donc, le contenu de la feuille Extraction, reconstruit par la procédure extraire, est vide. De fait, plus aucune correspondance n'est trouvée par les règles de mise en forme conditionnelle du calendrier.

Le code VBA complet de la procédure Supprimer_Click est le suivant :

Private Sub Supprimer_Click()
Dim nom As String
Dim ligne_ext As Integer
Dim cellule As Range: Dim feuille As Worksheet

Set feuille = Sheets('conges')
nom = Range('BD11').Value

For Each cellule In Selection

If (cellule.Value <> '' And IsDate(cellule.Value) And Len(cellule.Value) = 10) Then
ligne_ext = 3
Do While feuille.Cells(ligne_ext, 2).Value <> ''

If (feuille.Cells(ligne_ext, 2).Value = nom And feuille.Cells(ligne_ext,3).Value = cellule.Value) Then
feuille.Cells(ligne_ext, 2).EntireRow.Delete
Exit Do 'Pour passer à la date suivante sélectionnée
Else
ligne_ext = ligne_ext + 1
End If
Loop
End If

Next cellule

extraire

Outils.Hide
Range('A4').Activate
End Sub

Actualiser les données extraites
Maintenant que nous cherchons à bâtir un système autonome, donc indépendant des formules de calcul, nous devons réactualiser la liste extraite à plusieurs occasions. Tout d'abord, elle doit être reconstruite directement après l'ajout de nouvelles périodes. L'appel de la procédure extraire doit donc intervenir en fin de la procédure Ajouter_Click. Mais la liste doit aussi être rafraîchie à chaque changement d'année ou de salarié, dans les listes déroulantes de la feuille Calendrier. Et pour cela, il va s'agir d'intercepter les coordonnées de la cellule dont le contenu a été modifié.

• Tout d'abord, en bas de la procédure Ajouter_Click, intégrer son appel comme suit :

...
End If

extraire

Outils.Hide
Range('A4').Activate

End Sub
...

• Ensuite, double cliquer sur l'élément Feuil82 (Calendrier) dans l'explorateur de projet,
• En haut de la feuille de code, déployer la liste déroulante de gauche,
• Dans les propositions, choisir l'objet Worksheet,

Nous créons ainsi la procédure événementielle Worksheet_SelectionChange. Mais nous souhaitons déclencher un code VBA au changement de valeur dans une cellule et non au changement de sélection.

• Avec la liste de droite, toujours en haut de la feuille de code, choisir l'événement Change,

Nous créons ainsi la procédure Worksheet_Change. Elle transmet un objet de type Range en paramètre (Target). Il s'agit de la cellule dont le contenu a été modifié. Grâce à cet objet nous allons pouvoir prélever les coordonnées pour savoir s'il s'agit bien d'une des deux cellules de liste déroulante.

• Supprimer les lignes de la procédure Worksheet_SelectionChange,
• Entre les bornes de la procédure procédure Worksheet_Change, ajouter le code VBA :

...
Dim ligne As Byte: Dim colonne As Byte

ligne = Target.Row: colonne = Target.Column

If (colonne = 56 And (ligne = 8 Or ligne = 11)) Then
If (Cells(8, 56).Value <> '' And Cells(11, 56).Value <> '') Then
extraire
End If
End If
...

Grâce aux propriétés Row et Column de l'objet Target passé en argument, nous stockons les valeurs de ligne et de colonne dans les variables déclarées à cet effet. Nous vérifions qu'il s'agit bien soit de la cellule BD8 (ligne 8 et colonne 56) soit de la cellule BD11 (ligne 11 et colonne 56). Si les deux sont bien renseignées, nous appelons la procédure extraire afin d'isoler les enregistrements correspondants dans la feuille Extraction.

Enfin, il convient d'associer le code au bouton Annuler du formulaire. Il est trivial. Il consiste simplement à masquer ce dernier : Outils.Hide.

Le code VBA de la procédure extraire que nous avons exploitée à maintes reprises, sans devoir la développer, est le suivant :

Sub extraire()
Dim ligne_ext1 As Integer: Dim ligne_ext2 As Integer
Dim lannee As Integer: Dim le_nom As String
Dim feuille1 As Worksheet: Dim feuille2 As Worksheet

le_nom = Range('BD11').Value: lannee = Range('BD8').Value
Set feuille1 = Sheets('Extraction')
ligne_ext1 = 2

Do While feuille1.Cells(ligne_ext1, 2).Value <> ''
feuille1.Cells(ligne_ext1, 2).EntireRow.Delete
Loop

Set feuille2 = Sheets('conges')
ligne_ext2 = 3: ligne_ext1 = 2

Do While feuille2.Cells(ligne_ext2, 2).Value <> ''

If (feuille2.Cells(ligne_ext2, 2).Value = le_nom AndRight(feuille2.Cells(ligne_ext2, 3).Value, 4) = lannee) Then
feuille1.Cells(ligne_ext1, 2).Value = feuille2.Cells(ligne_ext2, 2).Value
feuille1.Cells(ligne_ext1, 3).Value = feuille2.Cells(ligne_ext2, 3).Value
feuille1.Cells(ligne_ext1, 4).Value = feuille2.Cells(ligne_ext2, 4).Value
feuille1.Cells(ligne_ext1, 5).Value = feuille1.Cells(ligne_ext1, 2).Value & Replace(feuille1.Cells(ligne_ext1, 3).Value, '/', '-') & feuille1.Cells(ligne_ext1, 4).Value
ligne_ext1 = ligne_ext1 + 1
End If

ligne_ext2 = ligne_ext2 + 1

Loop

End Sub

En résumé, puisqu'il s'agit de techniques similaires aux précédentes, nous supprimons tout d'abord toutes les informations de la feuille Extraction. Puis, nous parcourons la base de données de la feuille Conges à la recherche de correspondances avec le salarié et l'année définis depuis le calendrier. A ce titre, notez l'emploi de la fonction Right pour isoler l'année d'une date. Dès qu'une concordance est trouvée, les informations de l'enregistrement sont ajoutées à la suite dans la feuille Extraction. En dernière colonne, un assemblage de ces informations est réalisé pour concorder avec la concaténation des critères émis dans les règles de mise en forme conditionnelle.

Désormais, si vous changez de salarié, ajoutez des congés payés, RTT ou absences ou que vous supprimiez des périodes vaquées, tout le calendrier annuel se met instantanément à jour.

Nous avons donc réussi à développer un outil précieux et ergonomique en termes d'organisation pour les responsables.