Microsoft 365 Récupération donnée en fonction du numéro de semaine

[guiyom]

Bonsoir,

Je sollicite votre aide car je ne sais pas comment réaliser le résultat que je souhaite obtenir.

J’ai un tableau utilisé pour gérer les congés, les années sont générées de façon automatique, il est possible de saisir 3 personnes absente en simultané pour un même jour, les personnes saisies sont enregistrées sur 3 feuilles diffèrent BD_CAL, BD_CAL2 et BD_CAL3 et identifié par une couleur.

Je souhaite pouvoir récupérer les données saisie (feuille Calendrier) et les insérer dans la feuille planning en fonction du numéro de semaine choisi.

Je ne sais pas par quelle approche y parvenir, j’ai testé avec des SI

Exemple : =SI(ET(Calendrier!D4=BD_PERS!E2;BD_CAL!C:C=9);BD_PERS!E2;Q1)

Mais cela ne fonctionne pas.

Je vous joins un fichier d’exemple pour une meilleur compréhension

Bien cordialement

 

[vgendron]

Bonjour

Par formule ca va me sembler compliqué..
il faut la correspondance "Couleur / Personne" puis correspondance de semaine et congés...
Je pense que le VBA est surement plus indiqué...?
après.. il faudrait savoir ce que tu souhaites exactement..
peux tu remplir le résultat attendu pour la semaine 1

 

[vgendron]

Je suis allé faire un tour dans ton code..
c'est assez indigeste.... pas ou peu d'indentation
de nombreuses boucles imbriquées (for .. next). qui ne sont pas "correctement" fermées
exemple:

For i = 1 To 20
For mois = 1 To 12
For jour = 1 To 31
Next
Next
Next

est beaucoup moins lisible que

For i = 1 To 20
    For mois = 1 To 12
        For jour = 1 To 31
        Next jour 'on sait quelle boucle on ferme
    Next mois
Next i

des Si imbriqués en veux tu en voila.. alors qu'un select case serait plus simple
exemple:

Private Sub ComboBox_couleurs_Change()
no_couleur = ComboBox_couleurs.ListIndex
Select Case no_couleur
    Case 0
        TextBox_couleurs.BackColor = RGB(151, 71, 6)
    Case 1
        TextBox_couleurs.BackColor = RGB(218, 150, 148)
    Case 2
        TextBox_couleurs.BackColor = RGB(255, 0, 0)
    Case3
        TextBox_couleurs.BackColor = RGB(0, 0, 0)
    Case 4
        TextBox_couleurs.BackColor = RGB(0, 176, 240)
    Case 5
        TextBox_couleurs.BackColor = RGB(177, 160, 199)
    Case 6
        TextBox_couleurs.BackColor = RGB(196, 215, 155)
    Case 7
        TextBox_couleurs.BackColor = RGB(255, 192, 0)
    Case 8
        TextBox_couleurs.BackColor = RGB(96, 73, 122)
    Case 9
        TextBox_couleurs.BackColor = RGB(255, 255, 0)
    Case 10
        TextBox_couleurs.BackColor = RGB(225, 7, 183)
    Case 11
        TextBox_couleurs.BackColor = RGB(0, 176, 80)
    Case Else
        TextBox_couleurs.BackColor = &HFFFFFF
End Select
If no_couleur >= 0 Then TextBox_notes.SetFocus
End Sub

autre exemple d'indentation indispensable

Private Sub CommandButton_enreg_Click()
If ComboBox_type.Value = "" And ComboBox_couleurs.ListIndex = -1 Then
    dd = MsgBox("Merci de renseigner au moins un des champs suivants :" & Chr(10) & Chr(10) & " - Initiales" & Chr(10) & " - Couleur de fond", 48, "Erreur")
    Exit Sub
End If

If Label_ligne.Caption = "LIGNE" Then
    ligne_insertion = Sheets("BD_CAL").Range("A65000").End(xlUp).Row + 1
Else
    ligne_insertion = Label_ligne.Caption
End If

Sheets("BD_CAL").Cells(ligne_insertion, 1) = CDate(Label_date.Caption)
Sheets("BD_CAL").Cells(ligne_insertion, 2) = ComboBox_type.Value
Sheets("BD_CAL").Cells(ligne_insertion, 3) = ComboBox_couleurs.ListIndex
Sheets("BD_CAL").Cells(ligne_insertion, 4) = TextBox_notes.Value
ActiveCell = ComboBox_type.Value
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If ComboBox_couleurs.ListIndex = 6 Then
    ActiveCell.Font.ColorIndex = 1
    ActiveCell.HorizontalAlignment = xlCenter
    ActiveCell.Font.Bold = True
    ActiveCell.Interior.Color = TextBox_couleurs.BackColor
Else
    ActiveCell.Interior.Color = ActiveCell.Offset(0, -1).Interior.Color

    If ComboBox_couleurs.ListIndex = 7 Then
        ActiveCell.Font.ColorIndex = 1
        ActiveCell.HorizontalAlignment = xlCenter
        ActiveCell.Font.Bold = True
        ActiveCell.Interior.Color = TextBox_couleurs.BackColor
    Else
        ActiveCell.Interior.Color = ActiveCell.Offset(0, -1).Interior.Color
   
        If ComboBox_couleurs.ListIndex = 9 Then
            ActiveCell.Font.ColorIndex = 1
            ActiveCell.HorizontalAlignment = xlCenter
            ActiveCell.Font.Bold = True
            ActiveCell.Interior.Color = TextBox_couleurs.BackColor
        Else
            ActiveCell.Interior.Color = ActiveCell.Offset(0, -1).Interior.Color
   
            If ComboBox_couleurs.ListIndex >= 0 Then
                ActiveCell.Font.ColorIndex = 2
                ActiveCell.HorizontalAlignment = xlCenter
                ActiveCell.Font.Bold = True
                ActiveCell.Interior.Color = TextBox_couleurs.BackColor
            Else
                ActiveCell.Interior.Color = ActiveCell.Offset(0, -1).Interior.Color
            End If
            Unload Me
        End If
        Unload Me
    End If
    Unload Me
End If
Unload Me

End Sub

on a finalement l'impression que ce code fait et défait ce qui a été fait juste avant...

 

[guiyom]

Bonsoir,

Merci d’avoir pris le temps de me lire et d’avoir regardé mon projet.

Concernant la qualité de mon code je suis navré cela résulte d’une formation VBA en autodidacte, je passe beaucoup de temps à chercher, tester, et essayer de comprendre sans parfois beaucoup de résultat je dois le reconnaitre.

J’ai tenu compte de vos remarques et observations afin de rendre tout ceci plus digeste à la lecture.

Concernant la génération du planning, à l’origine j’avais essayé de faire la réalisation en VBA mais j’ai vite compris la difficulté de la tache au vu de mes connaissances. J’ai donc pensé à une génération des 52 plannings sur une même feuille via formules avec une zone d’impression variable en fonction de ToggleButtons correspondant au 52 numéro de semaine.

Vous trouverez ci-joint le fichier modifié avec sur la feuille PLANNING le résultat souhaité pour la semaine 1.

Dans l'idée, l'utilisateur appuie sur le bouton "GENERATION PLANNING" de la feuille Calendrier et appuie sur les ToggleButtons correspondant au semaines désirées.

Bien cordialement

 

[vgendron]

Bon voici un début

j'ai fait quelques modifs dans ton fichier
déjà, j'ai appliqué les différentes choses dont je t'ai déjà parlé: le code "simplifié" ainsi que les tables structurées pour présenter les données dans les différentes feuilles (il en reste encore avec tous les elseif)

Feuille Planning
j'ai ajouté l'année en F1
en fonction du numéro de semaine en K1, les jours de la semaine (ligne 4) se mettent à jour
à vérifier que la formule est la bonne.. elle fonctionne pour 2023, mais pour 2024, j'ai l'impression que non.. mais ca ira pour l'instant

en E24, j'ai modifié ta formule avec des si imbriqués pour la simplifier =>à reporter sur tout le planning
mais pour ca.. il faut que tu expliques les heures de début pour chaque employé.. ils ont chacun des heures de début différentes?
il faudrait ajouter une table qui indique ces heures (voir tableau que j'ai ajouté dans la feuille BD_Feries)
afin qu'on puisse les remettre quand la personne n'est pas absente..

et j'ai donc écrit une macro (lancer par le bouton "Update Planning") qui remplit le planning pour la semaine

 

[guiyom]

Incroyable !!

C'est très exactement le résultat recherché, merci infiniment pour votre temps consacré à mon problème.

Il y a visiblement un problème sur les semaines divisé sur deux mois, je vais m'employer à comprendre d'où vient le problème ainsi qu'a étudié votre travail.

Bien cordialement.

 

[vgendron]

Incroyable !!

C'est très exactement le résultat recherché, merci infiniment pour votre temps consacré à mon problème.

Il y a visiblement un problème sur les semaines divisé sur deux mois, je vais m'employer à comprendre d'où vient le problème ainsi qu'a étudié votre travail.

Bien cordialement.

j'ai bien noté ce problème aussi. et je vois bien l'origine.. mais je ne me l'explique pas encore...

 

[vgendron]

allez. hop.. problème réglé (du moins;. contourné)

 

[guiyom]

Bonjour,

Merci, je regarde ça.

Je posterais le fichier terminé si d'éventuel intéressé.

Cordialement

 

[guiyom]

Après visualisation le tableau marche impeccablement bien.

Je voudrais vous remercier une nouvelle fois pour le travail effectué et les finitions apporté.

Bien cordialement

Edit : Il y avait 2-3 bugs que j'ai pu corriger seulement un bug persiste que je n'arrive pas à résoudre concernant l'employé ayant pour nom Paul Pomme, celui-ci est identifié comme étant Pierre Poire pendant la génération du planning.

Edit2 : J'ai sensiblement changé la couleur concernée et le problème a disparu.

 

[vgendron]

Hello
une version v2 dans laquelle j'ai effectué quelques "gros" changements pour simplifier.
Donc voici un peu de lecture:

1) Génération du calendrier quand tu changes d'année
le code est bien plus simple et rapide
2) j'ai inhibé le code associé au bouton 18.. je ne comprend pas ce qu'il est censé faire..et pour l'instant.. il casse le planning

3) des MFC pour colorer les week end et Jour fériés

4) plus qu'une seule table Tab_Cal au lieu de 3
==>dans cette table
j'ai regroupé les 3 tables
une colonne "Bd_Cal" pour garder la colonne d'inscription dans le calendrier
==> un colonne Employé au lieu de Couleur
je trouve ca beaucoup plus pratique d'identifier un employé par son nom complet plutot qu'une couleur (qui pourrait changer en cours de route..)

5) Feuille BD_Pers
la colonne Couleur: c'est là que tu définis le code couleur à utiliser pour l'employé
pour chacun, les colonnes lundi à vendredi donnent les heures de début de journée
la colonne Durée Jour = Temps de travail quotidien
sur cette feuille. un bouton "UpdateCouleurs"
il te permet d'appliquer les couleurs et nom aux différents shapes "légende" de la feuille Calendrier
==> comme ca. tu es sur de voir la meme couleur d'une feuille à l'autre

6) Feuille BD_Feries
liste les jours féries de l'année en cours
==> j'ai supprimé tous les jours fériés qui apparaissaient inutilement dans la feuille BD_Cal
dans le calendrier, ils sont écrits en rouge / gras

7) Feuille BD_Vacances
ne sert à rien pour l'instant.. mais on pourrait imaginer de tracer les vacances scolaires sur le calendrier (je l'ai déjà fait sur un classeur perso de suivi de congés..)

8) feuille IndexCouleurDefaut
c'etait pour moi pour le débugage pour avoir la correspondance entre des colorindex et color.. certainement l'origine du bug que tu as réglé en modifiant la couleur de pomme..
on peut la supprimer

9) Feuille Planning
quand tu changes le numéro de semaine en K1, la macro est automatiquement lancée pour mettre à jour ce tableau

10) les formulaires...
j'en ai supprimé quelques uns...

un seul "Userform_notes" qui permet d'ajouter, modifier, supprimer une entrée dans le calendrier

je te laisse aller voir le code.. j'ai essayé de mettre un maximum de commentaires pour expliquer

après.. les autres formulaires.. je ne sais pas à quoi ils servent .. je n'y ai pas touché


voila.. c'est tout pour l'instant

 

[guiyom]

Je n’ai pas les mots c’est bien au-delà du résultat espéré, et bien au-delà de mes compétences au passage.

Le code associé au bouton 18 ne sert à rien, il était utilisé pour des tests.

La feuille BD_Vacances a pour but d’être utilisé via MFC dans la 5eme colonnes de chaque mois pour afficher les congés scolaires

Le codage utilisé est bien au-delà de mes compétences, heureusement que les commentaires m’éclair sur les processus en cours.

Encore merci du temps consacré au projet, le résultat obtenu est incroyable.

Bien cordialement

 

[vgendron]

Hello

une petite V3 en PJ
1) j'ai corrigé la formule pour trouver le 1er jeudi de la semaine dans la feuille "Planning"
==> c'est OK quelque soit l'année

2) un double clic sur le calendrier te permet d'enregistrer une nouvelle absence
SAUF si tu cliques sur un jour férié

3) j'ai splitté la macro qui génère le calendrier
une partie "Initialisation" qui génère un calendrier vide AVEC les Jours fériés ET les vacances
une partie "Remplissage" qui permet de remplir le calendrier avec les absences (bouton 18 renommé)

4) l'année est maintenant mise en A1 plutot qu'en AE1

j'ai ajouté un maximum de commentaires
pour te faciliter la compréhension sur les tables strucuturées
TableStructurée (en excel) = Listobject (en VBA)
un peu de lecture sur le sujet

Tableaux structurés Excel

Tableaux structurés Excel

fauconnier.developpez.com

The VBA Guide To ListObject Excel Tables

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA.

www.thespreadsheetguru.com

ce qui reste à faire (de ce que je vois) c'est au sujet du formulaire "Userform_Pers"
à quoi sert il?

à quoi servent les deux "boutons" silhouette en haut à gauche de la feuille Calendrier??

 

[guiyom]

Bonsoir,

Toujours impressionné par la qualité du travail, vous avez rendu le projet initial parfaitement fonctionnel.

Le Userform_Perf dois servir à ajouter une personne, les 2 boutons silhouette sont pour justement faire appel au formulaire d’ajout et de suppression d’utilisateur.

Terminer le projet est grâce à vous devenu une formalité, merci encore.

Il sera ainsi utilisé pendant des années sur mon lieu de travail.

Bien cordialement

 

[vgendron]

hello

je te prépare une petite variante pour afficher la légende (oui oui, je m'amuse)
Dans ton fichier, tu n'as "que" 12 personnes
les légendes tiennent sur une ligne..
mais.. si à l'avenir, le nombre de personnes augmente.. ca va vite devenir impossible d'afficher tout le monde sur la ligne.. en plus. tu es obligé de créer les shapes à la main...

==> plutot que de mettre les personnes et couleur sur la feuille.. je te fais une légende (formulaire) que tu peux afficher ou pas
et ce formulaire créé autant de boites qu'il faut tout seul
Combien de personnes sont prévues justement??