Suivi des jours d'absence

[david84]

Bonjour tout le monde,

suite à la discussion initiée ici, voici comme promis un fichier permettant le suivi des jours d'absences du lieu de travail de plusieurs personnes : suivi des congés, RTT, formation continue, maladie, journée de repos, journée de travail exceptionnelle (jours fériés par ex),...
Les nouveautés par rapport au fichier "calendrier dynamique" (si vous ne connaissez pas les possibilités offertes par ce fichier, reportez-vous à la discussion indiquée par le lien ci-dessus) :
- une feuille par personne
- possibilité de créer, supprimer, atteindre une feuille donnée
- création d'une feuille modèle
- création d'une feuille récapitulative (BD_recap)
- dans chaque feuille :
- possibilité de saisir les dates en utilisant un calendrier (se positionner dans la cellule de saisie de la 1ère ,double-clic pour appeler le calendrier, saisir les dates de début et de fin et valider)
- une MFC signale les dates en doublon.

Fichier spécifique en .xls :
Pour les personnes qui possèdent une version d'Excel antérieure à 2007, j'ai créé un autre fichier en .xls dans lequel ne subsistent que 3 MFC. Le reste est géré par macro. Pour modifier la couleur des items, se rendre dans la feuille "Modèle" et modifier la couleur de l'item correspondant en "A1:M1".
Ne possédant pas de version antérieure à 2007, je ne peux tester et savoir si le temps d'exécution est raisonnable ou pas.

Concernant le calendrier utilisé, je n'ai fait qu'adapter un calendrier fait par JB et que vous pouvez trouver sur son site ici.

L'idée de base concernant la gestion des couleurs par macro est également issue de son site ici, ainsi que l'idée de la feuille récapitulative.

Ci-joint 2 fichiers :
- une version en .xlsm pour les version 2007 et +
- une version en .xls pour les versions antérieures.
Merci de me communiquer vos retours de test (en me précisant à quel fichier vous faites allusion) et vos idées d'amélioration.
A+

 

[Dull]

Re : Suivi des jours d'absence

Salut David, le Forum

J'en étais sur... j'en ai rêvé et tu là fait faut dire, que sur ce coup, j'ai joué la montre...j'étaisenvacances... , je savais que tu ferais un petit chef-d’œuvre et je ne me suis pas trompé.

Chapeau bas David la version 2007 & + fonctionne à merveille si ce n'est le Db_Click sur la feuille Agent... si tu click sur la 1ère Ligne(Lig1) cela efface ta cel et la remplace par la date choisie: j'ai modifié ton Private Sub B_valid_Click(), pour qu'elle s'incrémente au fur et à mesure, et selon la colonne du DB_Click, par celle là

Private Sub B_valid_Click()
Col = ActiveCell.Column
DerLig = ActiveSheet.Cells(Rows.Count & Col).End(xlUp).Row + 1
Application.ScreenUpdating = False
  If IsDate(Me.date_début) And Me.date_fin = "" Then
    Cells(DerLig, Col).Value = CDate(Me.date_début)
    'Cells(DerLig, Col).Select
  End If
  If IsDate(Me.date_fin) Then
    Dim a(), compt&
    compt = DateDiff("d", Me.date_début, Me.date_fin)
      ReDim Preserve a(0 To compt)
          For i = 0 To compt
            a(i) = DateAdd("d", i, Me.date_début)
          Next i
  
  For i = LBound(a) To UBound(a)
        Cells(DerLig, Col) = a(i)
        'Cells(DerLig, Col).Select
  Next i
  End If
  Call raz
  With Me
    .date_début = ""
    .date_fin = ""
  End With
  Application.ScreenUpdating = True
End Sub

et ça à l'air de fonctionner

Une broutille mais les "select" sont souvent inutiles

Il, leFichier est adopté et mis en place dès demain en réel. Je te ferais signe si une question, ou une amélioration se fait sentir...

Saches, David , que mon Citronnier t'attends de pied ferme ...Heu...C'estJCquifournitleliquideambré...

Un grand merci à Jacques pour ses réalisations qui nous aident énormément.
La reconnaissance d'XLd, au moins la mienne, t'es acquise David

EDITION: Hi!!! JM

Bonne Journée à Tous

 

[david84]

Re : Suivi des jours d'absence

Re
@ Jean-Marcel :

Tu es sur tout les coups

j'ai préféré finaliser ce fichier avant de me pencher sur le tien !

@Dull : non mais faut être accro au liquide ambré pour cliquer sur l'entête de colonne ! T'as raté la cellule du dessous ou quoi ? Bien vu et merci pour ta modif.
Je modifierai les fichiers en conséquence.
N'hésite pas si tu vois autre chose.
A+

 

[Dull]

Re : Suivi des jours d'absence

Re le Fil

Héééééééééééééé...jeune Homme tu dit...

Merci de me communiquer vos retours de test (en me précisant à quel fichier vous faites allusion) et vos idées d'amélioration.

alors... je teste (n'oublie pas que je sort de vacances et... j'ai la patate ) encore merci David

N'hésite pas si tu vois autre chose.

...Heu...tunesaispasceaquoitut'attends...

Bonne Journée

 

[david84]

Re : Suivi des jours d'absence

Re

...Heu...tunesaispasceaquoitut'attends...

Ben j'en ai bien une petite idée, déjà que j'ai dû laissé traîner un select dans le fichier (bon, ptet deux) et que je me suis fait rattrapé par la patrouille Réunionnaise!
A+

 

[david84]

Re : Suivi des jours d'absence

Re Dull,
Bon, j'ai testé ton code. Pour tout te dire, j'avais procédé initialement de la sorte mais cela me répercutait dans la feuille de calcul des dates qui pouvaient être inversées selon les mois (alors que les dates sont affichées correctement dans le tableau VBA) et c'est pour cela que j'avais changé d'orientation.

Là, j'avais complété ton code pour prévoir le coup des plages de dates sélectionnées :

Private Sub B_valid_Click()
Col = ActiveCell.Column
DerLig = ActiveSheet.Cells(Rows.Count & Col).End(xlUp).Row + 1
Application.ScreenUpdating = False
  If IsDate(Me.date_début) And Me.date_fin = "" Then
    Cells(DerLig, Col).Value = CDate(Me.date_début)
    'Cells(DerLig, Col).Select
  End If
  If IsDate(Me.date_fin) Then
    Dim a(), compt&
    compt = DateDiff("d", Me.date_début, Me.date_fin)
      ReDim Preserve a(0 To compt)
          For i = 0 To compt
            a(i) = DateAdd("d", i, Me.date_début)
          Next i
 
  For i = LBound(a) To UBound(a)
        Range(Cells(DerLig, Col),Cells(DerLig+UBound(a), Col)) = application.transpose(a)'code complété
        'Cells(DerLig, Col).Select
  Next i
  End If
  Call raz
  With Me
    .date_début = ""
    .date_fin = ""
  End With
  Application.ScreenUpdating = True
End Sub

Mais à nouveau le même problème (notamment sur les dates de décembre).

J'ai donc retravaillé le code de la manière suivante :

Private Sub B_valid_Click()
Application.ScreenUpdating = False
Col = ActiveCell.Column
DerLig = ActiveSheet.Cells(Rows.Count & Col).End(xlUp).Row + 1
  If IsDate(Me.date_début) And Me.date_fin = "" Then
    Cells(DerLig, Col) = CDate(Me.date_début)
  End If
  If IsDate(Me.date_fin) Then
    Dim a(), compt&
    compt = DateDiff("d", Me.date_début, Me.date_fin)
      ReDim Preserve a(0 To compt)
          For i = 0 To compt
            a(i) = DateAdd("d", i, Me.date_début)
          Next i
  
  For i = LBound(a) To UBound(a)
        Cells(DerLig, Col) = a(i): DerLig = DerLig + 1
  Next i
  End If
  Call raz
  Me.date_début = ""
  Me.date_fin = ""
  Application.ScreenUpdating = True
End Sub

et là pas de problème apparemment (mais teste de ton côté).
Merci pour ta proposition car le code actuel est plus performant.
A+

 

[Dull]

Re : Suivi des jours d'absence

Salut David, le Fil

Effectivement, le dernier code fonctionne correctement mais de mon coté...C'ayest...j'aicassélejouet... j'explique:

j'ai ajouté une colonne dans Modèle en 2ème Position (1/2 TRex) jusque là tu me suis
j'ai essayé de comprendre ta formule dans le calendrier et ai changé les plages en fonction de l'ajout: je croyais tenir le bon bout mais j'ai perdu tout mes "fériés" dans les colonnes "Abs" Sob!!!!

Voilà mon abomination en rouge mes Modifs

=SI(OU(MOIS(DATE(An;ENT(COLONNES($A:C)/3)+$E$2-1;LIGNES($1:1)))=ENT(COLONNES($A:C)/3)+$E$2-1;MOIS(DATE(An;ENT(COLONNES($A:C)/3)+$E$2-1;LIGNES($1:1)))=(ENT(COLONNES($A:C)/3)+$E$2-1)-12);SI(MOD(COLONNES($A:A);3)=1;DATE(An;ENT(COLONNES($A:C)/3)+$E$2-1;LIGNES($1:1));SI(MOD(COLONNES($A:A);3)=2;SI(NB.SI(TabFer;DATE(An;ENT(COLONNES($A:C)/3)+$E$2-1;LIGNES($1:1)));RECHERCHEV(DATE(An;ENT(COLONNES($A:C)/3)+$E$2-1;LIGNES($1:1));TabFer;2;0);DECALER(ref_fête;EQUIV(MOIS(B7);fête;0)+JOUR(B7));SI(MOD(COLONNES($A:A);3)=0;SI(NB.SI(INDIRECT(nom&"!$A$2:$J$400");A7);INDEX(INDIRECT(nom&"!A1:J1");;MIN(SI(INDIRECT(nom&"!$A$2:$J$400")=A7;COLONNE(INDIRECT(nom&"!$A$2:$J$400")))));SI(NB.SI(INDIRECT(nom&"!O2:O8");JOURSEM(A7));"repos";SI(NB.SI(INDIRECT(nom&"!M2:M8");JOURSEM(A7));"½ repos";"")));"")));"")

...Non ne me dit rien ...ou alors un indice et si t'as pas de réponses dans les 10 Minutes, qu'on appelle le SAVMDF (Service Après Vente Macro David Formule)

Malgré tout je suis penaud je comprends ta formule bout par bout mais je n'arrive pas à assimiler la globalité ...c'est les parenthèses qui ne font rien qu'a m'embêter.

Autre Broutille dans la macro

Sub CréerFeuille()

et dessous

If Nom Like "[!1-9]*" Then

mettre

Nom = Replace(Nom, " ", "_")

cela évitera les "Pourquoi les noms composés ne fonctionnent pas"

A ma prochaine bêtise David

Bonne Soirée

 

[david84]

Re : Suivi des jours d'absence

Re
tu tiens le bon bout, ta formule est correcte mais puisque tu as déjà des onglets de créés avant la modif du modèle, tu dois effectuer les mêmes changements que sur ta feuille modèle. Le plus simple quand tu veux modifier "Modèle" est de sélectionner en même temps les autres onglets créés à partir de l'ancien modèle (appui sur la touche Ctrl et clic sur les différents onglets de personnes) afin que tes modif soient répercutées sur toutes tes feuilles.

Suite à cela j'ai modifié le code d'incrémentation des jours fériés dans "Modèle" en utilisant Find pour aller chercher "férié" (code placé dans la feuille Calendrier) :

Private Sub Worksheet_Deactivate()
Dim CelMin, CelMax, a, c, Prem, DerLig&
Application.ScreenUpdating = False

CelMin = [MIN(IF(COUNTIF(calend,fer),ROW(fer)))]
CelMax = [MAX(IF(COUNTIF(calend,fer),ROW(fer)))]
With Sheets("Liste")
    a = .Range(.Cells(CelMin, 3), .Cells(CelMax, 3))
End With
With Worksheets("Modèle").Range("A1").CurrentRegion
    Set c = .Find("férié", LookIn:=xlValues)
        If Not c Is Nothing Then
            Prem = c.Address
        End If
    DerLig = .Range("A1").CurrentRegion.Rows.Count
    .Range(.Cells(c.Row + 1, c.Column), .Cells(DerLig, c.Column)).ClearContents
    .Cells(c.Row + 1, c.Column).Resize(UBound(a)) = a
End With
Application.ScreenUpdating = True
End Sub

Ci-joint le fichier corrigé.
Concernant les noms composés, le problème n'est pas tant dans la création du nom de l'onglet que dans l'activation du lien hypertexte créé dans "BD_Recap" : si le nom composé comporte autre chose qu'un "_", le lien est invalide.
Je regarde de mon côté ce que je peux proposer.
Si le fichier te convient, je ferai la modification sur celui du message 1
A+

 

[JCGL]

Re : Suivi des jours d'absence

Bonjour à tous,
Salut les amis,

Joli travail David !!!

Saches, David , que mon Citronnier t'attends de pied ferme ...Heu...C'estJCquifournitleliquideambré...

Pas de problème fournir le liquide ambré... J'ai des réserves...

A++
A + à tous

 

[david84]

Re : Suivi des jours d'absence

Bonjour,
@Jean-Claude :

Pas de problème fournir le liquide ambré... J'ai des réserves...

Ah ! Les réserves sont faites pour être consommées!

@Dull : les fichiers du message 1 ont été actualisés.
Concernant la saisie du nom de l'onglet, j'ai réglé cela (enfin à tester tout de même) à coup de RegExp : désormais, pas possible de saisir un nom d'onglet comportant autre chose que des lettres et le caractère "_" (pas même d'espace). Même que si tu rentres plusieurs "_" qui se suivent (tu t'endors sur la touche suite à une trop grande consommation de liquide ambré, tu n'en as qu'un seul de pris au final : aa___zz__rr devient aa_zz_rr).

Par contre, avec ton histoire de journée et demie-journée travaillée exceptionnelle, je pense que les formules en bas du calendrier devront être revues (imagine que tu travailles une demi-journée un jour de repos ou un jour férié, il faut non seulement comptabiliser la demi-journée travaillée en jour de travail mais ne pas oublier de comptabiliser la demi-journée de repos ou férié restante dans le temps de repos ou férié...).
Je regarderai pour trouver une solution.

Aux autres forumeurs qui passeront par là :
merci à ceux qui sont sur une version antérieure à 2007 de tester le fichier .xls pour me dire s'il fonctionne et s'il n'est pas trop lent.
A+

 

[JCGL]

Re : Suivi des jours d'absence

Bonjour à tous,

J'ai testé la version XL 2003 pas de soucis à te faire remonter mais une certaine lenteur au recalcul.

A++

 

[david84]

Re : Suivi des jours d'absence

Re
@Jean-Claude :

J'ai testé la version XL 2003 pas de soucis à te faire remonter mais une certaine lenteur au recalcul.

lenteur raisonnable ou non (autrement dit, je continue de faire évoluer la version .xls ou ça rame trop) ?

@Dull : suite à mon dernier message, actualisation du fichier .xlsm uniquement (pour le.xls, j'attends les retours pour savoir ce qu'on fait) :
- concernant les jours de repos :
* mise en place d'une MFC dans les onglets permettant d'alerter si l'utilisateur sélectionne plusieurs fois la même journée ou demi-journée
* création d'une fonction personnalisée permettant la comptabilisation des jours de repos en tenant compte des journées et demi-journées de travail exceptionnelles
*utilisation de cette fonction dans la feuille calendrier, couplée à un test logique ramenant "pb repos" si la sélection des journée et demi-journée pose problème (+MFC sur la formule).
- concernant les jours fériés : création d'une fonction personnalisée tenant compte des journée et demi-journée de travail posées lors de jours fériés.
Le tout bien entendu à tester de ton côté.
A+

 

[Dull]

Re : Suivi des jours d'absence

Salut David , JC , le Fil

Désolé de n'avoir pu te répondre plus tôt mais ne t’inquiètes pas je regardes dès que j'ai plus de 2 minutes de répits . en cette fin d'année ...etouidéjà... je suis au four et au moulin.

création d'une fonction personnalisée permettant la comptabilisation des jours de repos en tenant compte des journées et demi-journées de travail exceptionnelles

c'était ma prochaine question j'avais trouvé un truc à base de VBA & Formules & Colonnes supplémentaires: Style, pour les Congés Réguliers, enlever les jours de repos demi et entier... cela fonctionne mais je testerai plus en avant car je sens pointer l'UàG

Encore merci David de me prêter tes neurones et surtout pour ta réalisation qui, et je le répète, est inestimable

Bonne Journée

 

[david84]

Re : Suivi des jours d'absence

Bonjour,
fichier .xlsm actualisé (quelques bugs repérés et fonction "JourRepos" un peu épurée et optimisée).
A+

 

[david84]

Re : Suivi des jours d'absence

Bonjour Dull, Jean-Claude et ceux et celles qui suivent ce fil,
encore des modifications :
- modification de la fonction JourRepos avec utilisation d'un dictionnaire permettant de ne pas comptabiliser les jours en doublons entrés en L2:L8 et N2:N8 des onglets personnel. Comme les jours entiers sont prioritaires par rapport aux demi-journées, si 2 mercredis sont renseignés par erreur par exemple, seul celui de la journée complète est pris en compte (et si 2 mercredis sont rentrés au sein d'une même colonne, un seul est pris en compte) : code plus sûr, plus court et plus rapide.
Le test logique initialement placé pour alerter si ces jours et demi-journées sont mal renseignés devient donc obsolète (j'ai laissé la MFC placée dans les feuilles personnel pour information).
Je t'ai placé les Debug.Print en commentaire (plus simple pour suivre la comptabilisation des jours et demi-journées de repos).

Function JoursRepos(plage As Range)
Dim NomFeuille As String, i As Byte, j As Byte, k As Byte, _
Nb, Pl, c, d, cal, Mondico As Object, temp, Repos, Demi_repos, tabl
Application.Volatile
NomFeuille = [Nom]
With Sheets(NomFeuille)
    Set Pl = .Range(.Cells(1, 1), .Cells(1, .Cells.Find("*", , , , xlByColumns, xlPrevious).Column))
    c = Application.Match("repos", Pl, 0)
    d = Application.Match("1/2 j repos", Pl, 0)
    Repos = .Range(.Cells(2, c + 1), .Cells(8, c + 1))
    Demi_repos = .Range(.Cells(2, d + 1), .Cells(8, d + 1))
End With
    Set Mondico = CreateObject("scripting.dictionary")
    For Each c In Repos
        If c <> "" Then Mondico(c) = ""
    Next c
    temp = Mondico.Count
    For Each c In Demi_repos
        If c <> "" Then Mondico(c) = ""
    Next c
    
tabl = Mondico.keys
cal = [calend]

If Mondico.Count > 0 Then
    For i = LBound(tabl) To temp - 1
        For j = 1 To [calend].Columns.Count Step 3
            For k = 1 To [calend].Rows.Count
                If cal(k, j) <> "" Then
                    If Weekday(cal(k, j)) = tabl(i) And cal(k, j + 2) = "repos" Then
                        Nb = Nb + 1: k = k + 6
                    ElseIf Weekday(cal(k, j)) = tabl(i) And cal(k, j + 2) Like "[½*]*" Then
                        Nb = Nb + 0.5: k = k + 6
                    End If
                End If
            Next k
            'Debug.Print Nb
        Next j
    Next i
    
    For i = temp To UBound(tabl)
        For j = 1 To [calend].Columns.Count Step 3
            For k = 1 To [calend].Rows.Count
            If cal(k, j) <> "" Then
                If Weekday(cal(k, j)) = tabl(i) And cal(k, j + 2) = "½ repos" Then _
                Nb = Nb + 0.5: k = k + 6
            End If
            Next k
            'Debug.Print Nb
        Next j
    Next i
End If
JoursRepos = Nb
End Function

Modification de la formule du calendrier due à la modification de l'ordre des priorités :
- les jours, demi-journées et jours fériés en priorité 1 (dans leur ordre respectif)
- les jours et demi-journées de repos en priorité 2
- le reste en priorité 3 (dans leur ordre respectif).
Ceci permet par exemple l'affichage des jours de repos même si la plage de congés notifiée comporte ces jours (les jours et demi-journée de repos ne sont donc pas comptabilisés dans les congés).
Fichier actualisé (uniquement celui en .xlsm) au message 1.
A+