Postes de travail en tenant compte des absences

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


Sujets que vous pourriez aussi aimer :


Postes de travail et absences

Grâce au VBA Excel, nous allons apporter une solution très simple pour affecter des salariés sur des postes de travail, sur un mois complet, tout en tenant compte des absences recensées en fonction des dates.



Sur l'exemple illustré par la capture, nous travaillons sur un planning des affectations des postes de travail. Si l'utilisateur clique sur une case de la première ligne, pour le premier jour du mois, une liste déroulante se propose et lui offre un choix parmi cinq salariés. Pourtant, s'il clique sur une case de la deuxième ligne, pour le deuxième jour du mois, c'est une liste déroulante de sept salariés qui se suggère cette fois.

Dans cette solution finalisée, c'est un code VBA Excel qui se déclenche automatiquement pour reconstruire ces listes déroulantes à la volée, en fonction des indisponibilités constatées dans un autre planning, celui des absences.

Classeur Excel à télécharger
Nous suggérons d'appuyer les travaux sur un classeur Excel hébergeant ces deux plannings.

• Télécharger le classeur listes-choix-gerant-absences.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous débouchons sur la première des deux feuilles de ce classeur. Elle est nommée Affectations. Et précisément, des affectations de postes sont déjà réalisées dans le planning qui se propose au centre de la feuille. Si vous cliquez sur l'une ou l'autre de ses cases, vous remarquez la présence de listes déroulantes qui se suggèrent automatiquement. Mais à ce stade bien sûr, elles ne sont pas dynamiques. Elles sont figées sur une liste statique des salariés. Elles ne tiennent donc pas compte des absences potentielles.

• En bas de la fenêtre Excel, cliquer sur l'onglet Absences pour activer sa feuille,

Nous y découvrons un planning architecturé exactement de la même façon que le premier. Mais celui-ci est destiné à y marquer les absences en regard des jours et des salariés concernés.



C'est grâce à cette organisation similaire des deux plannings que nous allons résoudre le cas en toute simplicité. L'idée est la suivante. A chaque clic de l'utilisateur dans une cellule du premier planning, le code VBA Excel doit parcourir toutes les cellules de la même ligne dans le second planning, celui des absences. Dès qu'aucune mention n'est observée dans une case, le nom du salarié disponible correspondant doit être ajouté à la liste à proposer à l'utilisateur pour l'affectation de poste.

La procédure événementielle
L'analyse par le code VBA doit être déclenchée automatiquement à chaque clic de l'utilisateur dans une cellule du premier planning. Pour cela, l'objet Worksheet (feuille) propose une petite panoplie d'événements associés, dont un particulièrement dédié.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• Dans l'explorateur de projet sur la gauche, double cliquer sur l'élément Feuil3(Affectations),

Ainsi et au centre de l'écran, nous affichons la feuille de code VBA associée à la première feuille du classeur, celle du planning des affectations. Ce sont en effet les clics sur ce dernier que nous devons intercepter.

• En haut de la feuille, déployer la liste déroulante de gauche,
• Puis, choisir l'objet Worksheet,

Nous créons ainsi la procédure événementielle Worksheet_SelectionChange. Son code se déclenchera à chaque nouvelle sélection, donc à chaque clic de l'utilisateur sur cette première feuille.

Remarque : Pour simplifier la construction du code VBA notamment, nous avons attribué des noms à ces deux plannings. Le premier est reconnu sous l'intitulé planning tandis que le second est nommé absences. Vous pouvez le constater en déployant la zone Nom en haut à gauche de l'une ou l'autre feuille du classeur.

Les variables
Ensuite, nous avons besoin de quelques variables, notamment pour connaître l'indice de la ligne cliquée ainsi que le nombre de colonnes à parcourir.

• Dans les bornes de la procédure, ajouter les déclarations de variables suivantes :

...
Dim laLigne As Byte: Dim nbCol As Byte: Dim colonne As Byte
Dim laListe As String: Dim feuilleDispo As Worksheet
...

Les trois premières variables sont déclarées comme des entiers courts (Byte). Elles doivent respectivement représenter l'indice de la ligne cliquée, le nombre de colonnes à analyser dans le second planning, et un compteur à incrémenter pour justement les passer toutes en revue, tour à tour.

La variable laListe est typée comme un texte (String). Son rôle est d'enregistrer tous les salariés disponibles pour la date demandée. Enfin, la dernière variable (feuilleDispo) est un objet déclaré comme une feuille (Worksheet). Il doit représenter la feuille des absences sur laquelle le code VBA doit porter son analyse.

Les coordonnées du clic
Il n'est pas nécessaire de déclencher le traitement lorsque la cellule cliquée est en dehors du planning des affectations. Nous devons donc réaliser un test pour vérifier ses coordonnées. Et justement, l'objet Target passé en paramètre de cette procédure événementielle représente cette cellule, au moment où elle reçoit le clic.

• Après la déclaration des variables, créer l'instruction conditionnelle suivante :

...
If Not Intersect([planning], Target) Is Nothing Then

End If
...

C'est un grand classique désormais dans nos développements, nous exploitons la fonction VBA Intersect pour recouper deux plages de cellules, celle du planning et celle représentant la cellule cliquée. Si cette intersection n'est pas vide (Not ... Is Nothing), nous savons que la cellule cliquée est bien à l'intérieur du planning. Dans ces conditions (Then), nous décidons de poursuivre.

L'initialisation des variables
Avant de poursuivre, nous devons initialiser les variables, notamment pour l'indice de ligne, le nombre de colonnes à analyser et la feuille à piloter.

• Dans les bornes de l'instruction conditionnelle, ajouter les affectations suivantes :

...
laListe = ""
laLigne = Target.Row
nbCol = [absences].Columns.Count + 2
Set feuilleDispo = Sheets("Absences")
...

Nous prélevons l'indice de la ligne cliquée grâce à la propriété Row de l'objet Target. La propriété Count de la collection Columns appartenant à la plage absences renseigne sur le nombre de colonnes de cette dernière. Comme il existe deux colonnes en entête de ce planning, nous ajoutons deux unités à ce score. En effet, en valeur absolue, nous aurons besoin de boucler jusqu'à la dernière colonne de ce planning pour n'omettre aucune cellule. Enfin, nous initialisons (Set) l'objet feuilleDispo sur la feuille des absences.

Parcourir les colonnes des absences
Maintenant que le nombre de colonnes est connu et recalibré, nous allons pouvoir engager une boucle For Next, pour les parcourir toutes en partant de la troisième. En effet, l'inscription des absences comme des affectations débute à partir de la colonne C.

• A la suite du code de l'instruction conditionnelle, créer la boucle suivante :

...
For colonne = 3 To nbCol

Next colonne
...

Liste des salariés disponibles
A chaque passage dans cette boucle et pour la ligne en cours (laLigne), chaque salarié disponible doit être ajouté à la suite, dans la variable laListe.

• Dans les bornes de la boucle, créer la nouvelle instruction conditionnelle suivante :

...
For colonne = 3 To nbCol
If (feuilleDispo.Cells(laLigne, colonne).Value = "") Then
laListe = laListe & "," & feuilleDispo.Cells(3, colonne).Value
End If
Next colonne
...

Sur la ligne de la cellule cliquée (laLigne) et pour la colonne en cours d'analyse par la boucle (colonne), si la cellule correspondante dans le planning des absences ne porte aucune mention (Value = ""), nous ajoutons le salarié en cours d'analyse (feuilleDispo.Cells(3, colonne).Value) à la liste des personnes disponibles. Avec ce processus récursif, a l'issue du traitement, nous obtiendrons bien la consolidation des salariés qui ne sont pas absents, pour le jour demandé par le clic de l'utilisateur sur le premier planning.

Vous remarquez la présence de la virgule dans cette concaténation (& "," &). Elle est très importante dans la syntaxe pour la construction de la liste déroulante, à partir de cette liste des personnes disponibles.

Créer la liste déroulante
La cellule active sur le premier planning est représentée en VBA par l'objet Selection. C'est effectivement sur cette sélection que nous devons ajouter une liste déroulante. Cet objet Selection propose une propriété qui se nomme Validation, pour notamment créer une liste déroulante, comme nous le ferions en manuel depuis l'outil Excel Validation des données.

• Toujours dans l'instruction conditionnelle mais après la boucle, créer le bloc With suivant :

...
Next colonne

With Selection.Validation
.Delete
.Add xlValidateList, , , laListe
End With

End If
...

Ce bloc permet de ne pas répéter l'objet (Selection) et sa propriété (Validation) puisqu'ils sont appelés à deux reprises. Toute d'abord, avec la méthode Delete pour détruire la potentielle présence d'une précédente liste déroulante en lieu et place. C'est ensuite la méthode Add qui intervient pour construire cette liste déroulante, grâce à la valeur xlValidateList en premier argument et à la source de données (laListe) en quatrième argument. La succession des virgules nous permet d'ignorer les paramètres de la méthode qui ne sont pas nécessaires dans cette construction.

Notre développement est terminé. Nous pouvons donc tester le résultat.

• Enregistrer les modifications (CTRL + S) et basculer sur la première feuille du classeur,
• Puis, réaliser quelques clics dans différentes cellules,

Comme vous pouvez l'apprécier, d'un jour sur l'autre, les contenus de ces listes déroulantes différent, en fonction des absences des uns et des autres. En revanche à ce stade, rien n'empêche l'utilisateur d'affecter deux fois le même salarié disponible sur une même journée. Mais une mise en forme conditionnelle l'alerte aussitôt, avec une couleur orange.

Si vous souhaitez la consulter :

• Cliquer sur une case du planning,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas des propositions, choisir l'option Gérer les règles,

Grâce à la fonction Nb.Si, elle détecte la présence des doublons sur une même ligne, dont elle change la couleur.

Cela dit, nous aurions très bien pu résoudre le cas en étoffant notre code VBA. Pour cela, il aurait suffi d'imbriquer une seconde boucle dans la première, mais cette fois pour parcourir les cellules du premier planning pour la ligne en cours. Le critère aurait consisté à vérifier la présence des salariés déjà affectés, pour ne pas les ajouter dans la liste des personnes disponibles.