Microsoft 365 add.range plante Excel

eric72 · 16 Novembre 2023

Bonjour à tous,
je rencontre un problème pour ajouter une ligne à un tableau à partir de Userform avec ce code:

VB:

With Sheets("Id").Range("TbId").ListObject
            .ListRows.Add.Range.Value = Array(TxtInit, TxtMdp, Abs(ChbEffectif), Abs(ChbAbsence), Abs(ChbSemType), Abs(ChbAmplitude), Abs(ChbNbparTranche), Abs(ChbPosteService), _
             Abs(ChbCptHres), Abs(ChbPoste), Abs(ChbService), Abs(ChbJrOuv), Abs(ChbPlanning), Abs(ChbSoldeCompteurhr), Abs(ChbPersPres), Abs(ChbPrepaSalaire))

la macro échoue de manière aléatoire et en plus Excel se ferme tout seul, ç'est pourtant une méthode relativement simple et pourtant!!!
Avez-vous une idée du pourquoi?
Merci beaucoup pour votre aide
Eric

eric72 · 17 Novembre 2023

Et là j'i eu une lumière (si,si ça peut arriver!!!), à l'ouverture de mon fichier j'ai un Usf d'identification avec mot de passe avec le code suivant:

VB:

Private Sub BtValider_Click()
Dim tableau As Range
Dim MDP As String
Dim ID As Variant
Set tableau = ThisWorkbook.Worksheets("Id").ListObjects("TbId").DataBodyRange
ID = Application.Match(CbNom, Range("Tbid[Utilisateur]"), 0)
If CbNom = "" Then MsgBox "Vous devez saisir un nom d'utilisateur": Exit Sub
If TxtMdp = "" Then MsgBox "Vous devez saisir un Mot de Passe": Exit Sub
    MDP = WorksheetFunction.VLookup(CbNom, tableau, 2, False)
    
    If IsError(MDP) Then MsgBox "Mot de Passe inconnu": TxtMdp = "": Exit Sub
    If MDP <> TxtMdp Then MsgBox "Mot de Passe inconnu": TxtMdp = "": Exit Sub...

Dranreb · 16 Novembre 2023

Je veux dire Array(TxtInit.Text, etc.

eric72 · 16 Novembre 2023

TooFatBoy à dit:
Es-tu vraiment sûr que c'est sur cette instruction que ça t'éjecte ???

oui car avant de m'ejecter il met en jaune avec le message la méthode a échoué, je le vois rapidement et après il me jette, je n'ai pas le temps de regarder les valeurs.

eric72 · 16 Novembre 2023

Dranreb à dit:
Je veux dire Array(TxtInit.Text, etc.

Si je comprends bien , j'ai juste à mettre .text aux 2 textbox (même si le mot de passe peut-être des chiffres), pour les checkbox, que dois je mettre?
Merci

Dranreb · 16 Novembre 2023

.Value
Quoique avec Abs il est bien obligé de considérer la propriété par défaut, il ne peut pas mettre l'objet dans l'Array, alors ce n'est pas nécessaire, à moins qu'il en ait avec tripleState alors ça peut être Null

TooFatBoy · 16 Novembre 2023

eric72 à dit:
oui car avant de m'ejecter il met en jaune avec le message la méthode a échoué, je le vois rapidement et après il me jette, je n'ai pas le temps de regarder les valeurs.

Il reste une possibilité : ton TS est moisi... 🙁
Perso, si le conseil de Danreb ne suffit pas à corriger le souci, je commencerais par le supprimer (le TS) et le recréer.

Mais franchement, quand ça commence à fermer Excel de façon plus ou moins aléatoire, c'est mal barré pour la santé du classeur.

eric72 · 16 Novembre 2023

Dranreb à dit:
.Value
Quoique avec Abs il est bien obligé de considérer la propriété par défaut, il ne peut pas mettre l'objet dans l'Array, alors ce n'est pas nécessaire, à moins qu'il en ait avec tripleState alors ça peut être Null

J'ai modifié selon votre solution et je vais tester, avez-vous déjà rencontré ce genre de beug aléatoire?

eric72 · 16 Novembre 2023

TooFatBoy à dit:
Il reste une possibilité : ton TS est moisi... 🙁
Perso, je commencerais par le supprimer et le recréer.

Mais franchement, quand ça commence à fermer Excel de façon plus ou moins aléatoire, c'est mal barré pour la santé du classeur.

Mon TS?

Dranreb · 16 Novembre 2023

J'ai l'habitude de préciser les propriété d'objets que je veux utiliser, alors ça ne m'arrive guère.

TooFatBoy · 16 Novembre 2023

Tu es en Excel 64 bits, eric72 ?

eric72 · 16 Novembre 2023

TooFatBoy à dit:
Tu es en Excel 64 bits, eric72 ?

Oups je n'en sais rien, je sais juste que c'est le pack office 365

eric72 · 16 Novembre 2023

Dranreb à dit:
J'ai l'habitude de préciser les propriété d'objets que je veux utiliser, alors ça ne m'arrive guère.

J'ai testé cela a fonctionné 5 fois et ca plante la 6ème fois

Dranreb · 16 Novembre 2023

Mettez un point d'arrêt devant, puis pendant l'arrêt mettez un espion sur votre expression Array(etc.
Vérifiez aussi si vous n'avez pas une Sub Worksheet_Change dans le module de la feuille, auquel cas il serait utile de mettre une instruction ApplicaTion.EnableEvents = False devant.

eric72 · 16 Novembre 2023

Dranreb à dit:
Mettez un point d'arrêt devant, puis pendant l'arrêt mettez un espion sur votre expression Array(etc.

Le point d'arrêt je sais faire, mais l'espion comment cela fonctionne t il?
Merci

Dranreb · 16 Novembre 2023

Le plus simple c'est un clic droit sur l'expression à auditer, commande Ajouter un espion…

eric72 · 16 Novembre 2023

Dranreb à dit:
Mettez un point d'arrêt devant, puis pendant l'arrêt mettez un espion sur votre expression Array(etc.
Vérifiez aussi si vous n'avez pas une Sub Worksheet_Change dans le module de la feuille, auquel cas il serait util de mettre une instruction ApplicaTion.EnableEvnts = False devant.

voila le code de ma feuille

VB:

Private Sub BtnSupprimer_Click()
'*************************************************
'event bouton supprimer COLLABORATEUR
'*************************************************
Application.ScreenUpdating = False

Dim reponse, LigCible As Long
If Sheets("Données").Range("ag2") < 3 Then MsgBox "Le nombre de salarié doit être au minimum de 2": Exit Sub

    reponse = MsgBox(" Confirmer la Modification de ce Collaborateur(rice)", vbYesNo + vbCritical)
    If reponse = vbYes Then
    Range("TbEffectif").ListObject.ListRows(CbRecherche.ListIndex + 1).Delete
    
'*********************************************
'SUPPRIME LE SALARIE DE TbAbsence
'*********************************************
Dim PL As Range 'déclare la variable PL
Dim I As Integer 'déclare la variable I

Set PL = Range("TbAbsence[Nom]") 'définit la plage PL
For I = PL.Rows.Count To 1 Step -1 'boucle de la dernière a la première ligne de PL
    If PL(I).Value = UsfEffectif.TxtInit Then PL.ListObject.ListRows(I).Delete 'si la valeur vaut A supprime la ligne
Next I 'procaine ligne de la boucle

'*********************************************
'SUPPRIME LE SALARIE DE TbId
'*********************************************
Dim PL2 As Range 'déclare la variable PL
Dim J As Integer 'déclare la variable I

Set PL2 = Range("TbId[Utilisateur]") 'définit la plage PL
For J = PL2.Rows.Count To 1 Step -1 'boucle de la dernière a la première ligne de PL
    If PL2(J).Value = UsfEffectif.TxtInit Then PL2.ListObject.ListRows(J).Delete 'si la valeur vaut A supprime la ligne
Next J 'procaine ligne de la boucle

With Sheets("Planning Réel")
    For Each CELLULE In Range("3:3,11:11,19:19,27:27,35:35,43:43,51:51,59:59,67:67,75:75,83:83,91:91,99:99,107:107,115:115,123:123,131:131,139:139,147:147,155:155")
        If CELLULE.Value = UsfEffectif.TxtInit Then Rows(CELLULE.Row).ClearContents: Rows(CELLULE.Row + 1).ClearContents: Rows(CELLULE.Row + 2).ClearContents: Rows(CELLULE.Row + 3).ClearContents _
        : Rows(CELLULE.Row + 4).ClearContents: Rows(CELLULE.Row + 5).ClearContents: Rows(CELLULE.Row + 6).ClearContents: Rows(CELLULE.Row + 7).ClearContents
    Next
End With

 With Sheets("Calcul Hrs supp")
    For Each CELLULE In Range("c3:c22")
        If CELLULE.Value = UsfEffectif.TxtInit.Value And CELLULE.Value <> "" Then Rows(CELLULE.Row).ClearContents
    Next
End With

    Dim Initiale As String, dln%, M%
    Initiale = UsfEffectif.TxtInit
    With Worksheets("Banque d'Heures")
        dln = .Range("b" & .Rows.Count).End(xlUp).Row
        Application.ScreenUpdating = False
        For M = 7 To 26
            If .Cells(M, 2) = Initiale Then
                .Cells(M, 1).ClearContents
                .Cells(M, 2).ClearContents
              
            End If
        Next M
    End With

 Dim Initiale2 As String, dln2%, K%
    Initiale2 = UsfEffectif.TxtInit
    With Worksheets("recap absences")
        dln2 = .Range("b" & .Rows.Count).End(xlUp).Row
        Application.ScreenUpdating = False
       For K = 3 To 22
            If .Cells(K, 2) = Initiale2 Then
                .Cells(K, 2).Value = "0"
                .Cells(K, 1).ClearContents
                .Cells(K, 3).Resize(, 397).ClearContents

            End If
        Next K
    End With

 Dim Initiale3 As String, dln3%, L%
    Initiale3 = UsfEffectif.TxtInit
    With Worksheets("Prépa Salaire")
        dln3 = .Range("b" & .Rows.Count).End(xlUp).Row
        Application.ScreenUpdating = False
        For L = 8 To 27
            If .Cells(L, 2) = Initiale3 Then
                .Cells(L, 2).ClearContents
                .Cells(L, 1).ClearContents

            End If
        Next L
    End With
   vidange
    RemplitlesListes
    End If
Application.ScreenUpdating = True

End Sub

Private Sub TxtDateFixe_Change()
Dim madate As Date
If TxtDateFixe.Value <> "" Then
    madate = CDate(TxtDateFixe)
        If madate < Date Then
            MsgBox "Vous ne pouvez pas modifier les données antérieures à aujourd'hui "
            TxtDateFixe = ""
        End If
End If
End Sub

Private Sub TxtDateRotation_Change()
Dim madate As Date
If TxtDateRotation.Value <> "" Then
    madate = CDate(TxtDateRotation)
        If madate < Date Then
            MsgBox "Vous ne pouvez pas modifier les données antérieures à aujourd'hui "
            TxtDateRotation = ""
        End If
End If

End Sub

Private Sub UserForm_Initialize()
RemplitlesListes

Label8.Visible = False: TxtTaux.Visible = False
Label12.Visible = False: Label13.Visible = False: CbNbSemRotation.Visible = False
Label16.Visible = False: CbSemTypeFixe.Visible = False
Label14.Visible = False: Label15.Visible = False: Label17.Visible = False: Label18.Visible = False: Label19.Visible = False: Label20.Visible = False:
CbSemType1.Visible = False: CbSemType2.Visible = False: CbSemType3.Visible = False: CbSemType4.Visible = False: CbSemType5.Visible = False: _
CbSemType6.Visible = False: TxtDateRotation.Visible = False: Label21.Visible = False: Datepicker4.Visible = False: Datepicker5.Visible = False: Label22.Visible = False: TxtDateFixe.Visible = False
 Label190.BackColor = RGB(39, 43, 73)
 Label230.BackColor = RGB(39, 43, 73)
End Sub
Private Sub UserForm_Activate()
   Dim ctl As Control, ratioW As String, ratioH As String
If Sheets("Données").Range("ge2") <> 1 Then

   ratioW = Application.Width / Me.Width
   ratioH = Application.Height / Me.Height
   Me.Left = 0
   Me.Top = 0
   Me.Width = Application.Width
   Me.Height = Application.Height
  For Each ctl In Me.Controls
      ctl.Left = ctl.Left * ratioW
      ctl.Top = ctl.Top * ratioH
      ctl.Width = ctl.Width * ratioW
      ctl.Height = ctl.Height * ratioH
      If TypeOf ctl Is MSForms.ScrollBar Then GoTo suite
      If TypeOf ctl Is MSForms.SpinButton Then GoTo suite
      If TypeOf ctl Is MSForms.Image Then GoTo suite
      ctl.Font.Size = ctl.Font.Size * ratioH
suite:
   Next
  
End If
End Sub

Private Sub BtnValider_Click()
Dim X&, I&
Dim NbrRotation As Byte
UsfWait.Show 0
UsfWait.Repaint

 DateDebut = IIf(TxtDateRotation.Value = "", 0, TxtDateRotation.Value)
 datenaissance = IIf(TxtDateNaissance.Value = "", 0, TxtDateNaissance.Value)
 DatedebutFixe = IIf(TxtDateFixe.Value = "", 0, TxtDateFixe.Value)
 NbSemRot = IIf(CbNbSemRotation.Value = "", 0, CbNbSemRotation.Value)
    If TxtDateRotation = "" And ObRotation = True Then MsgBox "Vous devez Sélectionner une date de début ": Exit Sub
    If TxtDateNaissance = "" Then MsgBox "Vous devez Indiquer une date de Naissance ": Exit Sub
    If TxtMdp = "" Then MsgBox "Vous devez Saisir un mot de passe (ATTENTION AUX MAJUSCULES) ": Exit Sub
    If TxtDateFixe = "" And ObFixe = True Then MsgBox "Vous devez Sélectionner une date de début ": Exit Sub
    'If ChbMajoration = True And TxtTaux = "" Then MsgBox "Vous devez saisir un taux de Majoration": TxtTaux.SetFocus: Exit Sub
    If ObFixe.Value = False And ObRotation.Value = False Then MsgBox "Vous devez choisir si le planning est fixe ou tournant": Exit Sub
    If ObFixe.Value = True And CbSemTypeFixe = "" Then MsgBox "Vous avez choisi un Planning fixe, vous devez sélectionner la Semaine Type attribuée": CbSemTypeFixe.SetFocus: Exit Sub
    If ObRotation.Value = True And CbNbSemRotation.Value = "" Then MsgBox "Vous avez choisi un Planning à rotation, vous devez sélectionner le nombre de semaines de rotation": CbNbSemRotation.SetFocus: Exit Sub

    For I = 1 To NbrRotation
        If Me.Controls("CbSemType" & I).Text = Empty Then MsgBox "Vous devez sélectionner " & NbrRotation & " Semaines type": Exit Sub
    Next

With [TbEffectif].ListObject
            X = Application.IfError(Application.Match(TxtNom, .Range.Columns(1), 0), 0) And Application.IfError(Application.Match(TxtPrenom, .Range.Columns(2), 0), 0)
            If X <> 0 Then MsgBox "Ce Collaborateur existe déjà" & vbCrLf: Exit Sub ' Teste si n'existe pas déjà avec le nom et prénom
End With
            
With Range("TbEffectif").ListObject 'ajoute une ligne à TbId

            .ListRows.Add.Range.Value = Array(TxtNom, TxtPrenom, CbPoste, CDate(datenaissance), CDbl(TxtNbHeure), ChbOui, TxtTaux, TxtInit, Abs(ObFixe), Abs(ObRotation), CDbl(NbSemRot), _
            CbSemTypeFixe, CbSemType1, CbSemType2, CbSemType3, CbSemType4, CbSemType5, CbSemType6, CDate(DateDebut), CDate(DatedebutFixe)) 'on ajoute une ligne au tableau
End With

With Sheets("Id").Range("TbId").ListObject

            .ListRows.Add.Range.Value = Array(TxtInit.Text, TxtMdp.Text, Abs(ChbEffectif.Value), Abs(ChbAbsence.Value), Abs(ChbSemType.Value), Abs(ChbAmplitude.Value), Abs(ChbNbparTranche.Value), Abs(ChbPosteService.Value), _
             Abs(ChbCptHres.Value), Abs(ChbPoste.Value), Abs(ChbService.Value), Abs(ChbJrOuv.Value), Abs(ChbPlanning.Value), Abs(ChbSoldeCompteurhr.Value), Abs(ChbPersPres.Value), Abs(ChbPrepaSalaire.Value)) '
End With

    If ObFixe = True Then
        ArchiverPlanningFixe
        xderligne
        Duplique_Planning
    ElseIf ObRotation = True Then
        ArchiverPlanningRotation
        xderligne
        Duplique_Planning
    End If
  Unload UsfWait
vidange
RemplitlesListes
suppr
End Sub

Private Sub BtnModifier_Click()
        Application.ScreenUpdating = False
 DateDebut = IIf(TxtDateRotation.Value = "", 0, TxtDateRotation.Value)
 datenaissance = IIf(TxtDateNaissance.Value = "", 0, TxtDateNaissance.Value)
 DatedebutFixe = IIf(TxtDateFixe.Value = "", 0, TxtDateFixe.Value)
 NbSemRot = IIf(CbNbSemRotation.Value = "", 0, CbNbSemRotation.Value)
    Dim V, w
        V = Array(TxtNom, TxtPrenom, CbPoste, CDate(datenaissance), CDbl(TxtNbHeure), ChbOui, TxtTaux, TxtInit, Abs(ObFixe), Abs(ObRotation), CDbl(NbSemRot), _
            CbSemTypeFixe, CbSemType1, CbSemType2, CbSemType3, CbSemType4, CbSemType5, CbSemType6, CDate(DateDebut), CDate(DatedebutFixe))
        For I = 0 To UBound(V)
            If IsNumeric(V(I)) Then V(I) = CDbl(V(I))
        Next
        With [TbEffectif].ListObject
            .ListRows(CbRecherche.ListIndex + 1).Range.Value = V
        End With
        
        w = Array(TxtInit, TxtMdp, Abs(ChbEffectif), Abs(ChbAbsence), Abs(ChbSemType), Abs(ChbAmplitude), Abs(ChbNbparTranche), Abs(ChbPosteService) _
            , Abs(ChbCptHres), Abs(ChbPoste), Abs(ChbService), Abs(ChbJrOuv), Abs(ChbPlanning), Abs(ChbSoldeCompteurhr), Abs(ChbPersPres))
        For I = 0 To UBound(w)
            If IsNumeric(w(I)) Then w(I) = CDbl(w(I))
        Next
        With Sheets("Id").[TbId].ListObject
            .ListRows(CbRecherche.ListIndex + 1).Range.Value = w
        End With

    If ObFixe = True Then
        ModifierToutFixe
    End If
    If ObRotation = True Then
        ModifierToutRotation
    End If
        Duplique_Planning
        RemplitlesListes
        With CbRecherche: I = .ListIndex: .ListIndex = -1: .ListIndex = I: End With    'on remet le produit a l'initial
        vidange
        CbRecherche.ListIndex = -1
        Application.ScreenUpdating = True
End Sub
Private Sub CbSemTypeFixe_AfterUpdate()
AbsLuAmF = Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Lundi AM]"))
DLuAmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi AM Début]")), "hh:mm")
FLuAmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi AM Fin]")), "hh:mm")
AbsLuPmF = Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Lundi PM]"))
DLuPmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi PM Début]")), "hh:mm")
FLuPmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi PM Fin]")), "hh:mm")

AbsMaAmF = Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mardi AM]"))
DMaAmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi AM Début]")), "hh:mm")
FMaAmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi AM Fin]")), "hh:mm")
AbsMaPmF = Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mardi PM]"))
DMaPmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi PM Début]")), "hh:mm")
FMaPmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi PM Fin]")), "hh:mm")

AbsMeAmF = Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mercredi AM]"))
DMeAmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi AM Début]")), "hh:mm")
FMeAmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi AM Fin]")), "hh:mm")
AbsMePmF = Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mercredi PM]"))
DMePmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi PM Début]")), "hh:mm")
FMePmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi PM Fin]")), "hh:mm")

AbsJeAmF = Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Jeudi AM]"))
DJeAmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi AM Début]")), "hh:mm")
FJeAmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi AM Fin]")), "hh:mm")
AbsJePmF = Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Jeudi PM]"))
DJePmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi PM Début]")), "hh:mm")
FJePmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi PM Fin]")), "hh:mm")

AbsVeAmF = Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Vendredi AM]"))
DVeAmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi AM Début]")), "hh:mm")
FVeAmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi AM Fin]")), "hh:mm")
AbsVePmF = Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Vendredi PM]"))
DVePmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi PM Début]")), "hh:mm")
FVePmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi PM Fin]")), "hh:mm")

AbsSaAmF = Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Samedi AM]"))
DSaAmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi AM Début]")), "hh:mm")
FSaAmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi AM Fin]")), "hh:mm")
AbsSaPmF = Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Samedi PM]"))
DSaPmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi PM Début]")), "hh:mm")
FSaPmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi PM Fin]")), "hh:mm")

AbsDiAmF = Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Dimanche AM]"))
DDiAmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche AM Début]")), "hh:mm")
FDiAmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche AM Fin]")), "hh:mm")
AbsDiPmF = Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Dimanche PM]"))
DDiPmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche PM Début]")), "hh:mm")
FDiPmF = Format(Application.WorksheetFunction.XLookup(CbSemTypeFixe, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche PM Fin]")), "hh:mm")
End Sub
Private Sub CbSemType1_AfterUpdate()
AbsLuAmR1 = Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Lundi AM]"))
DLuAmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi AM Début]")), "hh:mm")
FLuAmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi AM Fin]")), "hh:mm")
AbsLuPmR1 = Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Lundi PM]"))
DLuPmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi PM Début]")), "hh:mm")
FLuPmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi PM Fin]")), "hh:mm")

AbsMaAmR1 = Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mardi AM]"))
DMaAmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi AM Début]")), "hh:mm")
FMaAmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi AM Fin]")), "hh:mm")
AbsMaPmR1 = Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mardi PM]"))
DMaPmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi PM Début]")), "hh:mm")
FMaPmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi PM Fin]")), "hh:mm")

AbsMeAmR1 = Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mercredi AM]"))
DMeAmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi AM Début]")), "hh:mm")
FMeAmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi AM Fin]")), "hh:mm")
AbsMePmR1 = Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mercredi PM]"))
DMePmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi PM Début]")), "hh:mm")
FMePmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi PM Fin]")), "hh:mm")

AbsJeAmR1 = Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Jeudi AM]"))
DJeAmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi AM Début]")), "hh:mm")
FJeAmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi AM Fin]")), "hh:mm")
AbsJePmR1 = Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Jeudi PM]"))
DJePmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi PM Début]")), "hh:mm")
FJePmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi PM Fin]")), "hh:mm")

AbsVeAmR1 = Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Vendredi AM]"))
DVeAmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi AM Début]")), "hh:mm")
FVeAmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi AM Fin]")), "hh:mm")
AbsVePmR1 = Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Vendredi PM]"))
DVePmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi PM Début]")), "hh:mm")
FVePmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi PM Fin]")), "hh:mm")

AbsSaAmR1 = Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Samedi AM]"))
DSaAmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi AM Début]")), "hh:mm")
FSaAmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi AM Fin]")), "hh:mm")
AbsSaPmR1 = Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Samedi PM]"))
DSaPmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi PM Début]")), "hh:mm")
FSaPmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi PM Fin]")), "hh:mm")

AbsDiAmR1 = Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Dimanche AM]"))
DDiAmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche AM Début]")), "hh:mm")
FDiAmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche AM Fin]")), "hh:mm")
AbsDiPmR1 = Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Dimanche PM]"))
DDiPmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche PM Début]")), "hh:mm")
FDiPmR1 = Format(Application.WorksheetFunction.XLookup(CbSemType1, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche PM Fin]")), "hh:mm")

End Sub
Private Sub CbSemType2_AfterUpdate()
AbsLuAmR2 = Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Lundi AM]"))
DLuAmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi AM Début]")), "hh:mm")
FLuAmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi AM Fin]")), "hh:mm")
AbsLuPmR2 = Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Lundi PM]"))
DLuPmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi PM Début]")), "hh:mm")
FLuPmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi PM Fin]")), "hh:mm")

AbsMaAmR2 = Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mardi AM]"))
DMaAmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi AM Début]")), "hh:mm")
FMaAmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi AM Fin]")), "hh:mm")
AbsMaPmR2 = Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mardi PM]"))
DMaPmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi PM Début]")), "hh:mm")
FMaPmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi PM Fin]")), "hh:mm")

AbsMeAmR2 = Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mercredi AM]"))
DMeAmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi AM Début]")), "hh:mm")
FMeAmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi AM Fin]")), "hh:mm")
AbsMePmR2 = Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mercredi PM]"))
DMePmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi PM Début]")), "hh:mm")
FMePmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi PM Fin]")), "hh:mm")

AbsJeAmR2 = Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Jeudi AM]"))
DJeAmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi AM Début]")), "hh:mm")
FJeAmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi AM Fin]")), "hh:mm")
AbsJePmR2 = Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Jeudi PM]"))
DJePmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi PM Début]")), "hh:mm")
FJePmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi PM Fin]")), "hh:mm")

AbsVeAmR2 = Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Vendredi AM]"))
DVeAmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi AM Début]")), "hh:mm")
FVeAmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi AM Fin]")), "hh:mm")
AbsVePmR2 = Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Vendredi PM]"))
DVePmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi PM Début]")), "hh:mm")
FVePmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi PM Fin]")), "hh:mm")

AbsSaAmR2 = Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Samedi AM]"))
DSaAmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi AM Début]")), "hh:mm")
FSaAmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi AM Fin]")), "hh:mm")
AbsSaPmR2 = Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Samedi PM]"))
DSaPmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi PM Début]")), "hh:mm")
FSaPmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi PM Fin]")), "hh:mm")

AbsDiAmR2 = Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Dimanche AM]"))
DDiAmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche AM Début]")), "hh:mm")
FDiAmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche AM Fin]")), "hh:mm")
AbsDiPmR2 = Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Dimanche PM]"))
DDiPmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche PM Début]")), "hh:mm")
FDiPmR2 = Format(Application.WorksheetFunction.XLookup(CbSemType2, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche PM Fin]")), "hh:mm")

End Sub
Private Sub CbSemType3_AfterUpdate()
AbsLuAmR3 = Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Lundi AM]"))
DLuAmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi AM Début]")), "hh:mm")
FLuAmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi AM Fin]")), "hh:mm")
AbsLuPmR3 = Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Lundi PM]"))
DLuPmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi PM Début]")), "hh:mm")
FLuPmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi PM Fin]")), "hh:mm")

AbsMaAmR3 = Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mardi AM]"))
DMaAmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi AM Début]")), "hh:mm")
FMaAmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi AM Fin]")), "hh:mm")
AbsMaPmR3 = Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mardi PM]"))
DMaPmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi PM Début]")), "hh:mm")
FMaPmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi PM Fin]")), "hh:mm")

AbsMeAmR3 = Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mercredi AM]"))
DMeAmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi AM Début]")), "hh:mm")
FMeAmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi AM Fin]")), "hh:mm")
AbsMePmR3 = Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mercredi PM]"))
DMePmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi PM Début]")), "hh:mm")
FMePmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi PM Fin]")), "hh:mm")

AbsJeAmR3 = Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Jeudi AM]"))
DJeAmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi AM Début]")), "hh:mm")
FJeAmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi AM Fin]")), "hh:mm")
AbsJePmR3 = Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Jeudi PM]"))
DJePmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi PM Début]")), "hh:mm")
FJePmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi PM Fin]")), "hh:mm")

AbsVeAmR3 = Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Vendredi AM]"))
DVeAmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi AM Début]")), "hh:mm")
FVeAmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi AM Fin]")), "hh:mm")
AbsVePmR3 = Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Vendredi PM]"))
DVePmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi PM Début]")), "hh:mm")
FVePmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi PM Fin]")), "hh:mm")

AbsSaAmR3 = Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Samedi AM]"))
DSaAmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi AM Début]")), "hh:mm")
FSaAmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi AM Fin]")), "hh:mm")
AbsSaPmR3 = Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Samedi PM]"))
DSaPmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi PM Début]")), "hh:mm")
FSaPmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi PM Fin]")), "hh:mm")

AbsDiAmR3 = Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Dimanche AM]"))
DDiAmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche AM Début]")), "hh:mm")
FDiAmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche AM Fin]")), "hh:mm")
AbsDiPmR3 = Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Dimanche PM]"))
DDiPmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche PM Début]")), "hh:mm")
FDiPmR3 = Format(Application.WorksheetFunction.XLookup(CbSemType3, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche PM Fin]")), "hh:mm")

End Sub
Private Sub CbSemType4_AfterUpdate()
AbsLuAmR4 = Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Lundi AM]"))
DLuAmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi AM Début]")), "hh:mm")
FLuAmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi AM Fin]")), "hh:mm")
AbsLuPmR4 = Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Lundi PM]"))
DLuPmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi PM Début]")), "hh:mm")
FLuPmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi PM Fin]")), "hh:mm")

AbsMaAmR4 = Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mardi AM]"))
DMaAmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi AM Début]")), "hh:mm")
FMaAmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi AM Fin]")), "hh:mm")
AbsMaPmR4 = Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mardi PM]"))
DMaPmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi PM Début]")), "hh:mm")
FMaPmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi PM Fin]")), "hh:mm")

AbsMeAmR4 = Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mercredi AM]"))
DMeAmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi AM Début]")), "hh:mm")
FMeAmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi AM Fin]")), "hh:mm")
AbsMePmR4 = Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mercredi PM]"))
DMePmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi PM Début]")), "hh:mm")
FMePmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi PM Fin]")), "hh:mm")

AbsJeAmR4 = Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Jeudi AM]"))
DJeAmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi AM Début]")), "hh:mm")
FJeAmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi AM Fin]")), "hh:mm")
AbsJePmR4 = Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Jeudi PM]"))
DJePmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi PM Début]")), "hh:mm")
FJePmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi PM Fin]")), "hh:mm")

AbsVeAmR4 = Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Vendredi AM]"))
DVeAmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi AM Début]")), "hh:mm")
FVeAmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi AM Fin]")), "hh:mm")
AbsVePmR4 = Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Vendredi PM]"))
DVePmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi PM Début]")), "hh:mm")
FVePmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi PM Fin]")), "hh:mm")

AbsSaAmR4 = Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Samedi AM]"))
DSaAmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi AM Début]")), "hh:mm")
FSaAmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi AM Fin]")), "hh:mm")
AbsSaPmR4 = Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Samedi PM]"))
DSaPmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi PM Début]")), "hh:mm")
FSaPmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi PM Fin]")), "hh:mm")

AbsDiAmR4 = Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Dimanche AM]"))
DDiAmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche AM Début]")), "hh:mm")
FDiAmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche AM Fin]")), "hh:mm")
AbsDiPmR4 = Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Dimanche PM]"))
DDiPmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche PM Début]")), "hh:mm")
FDiPmR4 = Format(Application.WorksheetFunction.XLookup(CbSemType4, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche PM Fin]")), "hh:mm")

End Sub
Private Sub cbsemtype5_AfterUpdate()
AbsLuAmR5 = Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Lundi AM]"))
DLuAmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi AM Début]")), "hh:mm")
FLuAmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi AM Fin]")), "hh:mm")
AbsLuPmR5 = Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Lundi PM]"))
DLuPmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi PM Début]")), "hh:mm")
FLuPmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi PM Fin]")), "hh:mm")

AbsMaAmR5 = Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mardi AM]"))
DMaAmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi AM Début]")), "hh:mm")
FMaAmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi AM Fin]")), "hh:mm")
AbsMaPmR5 = Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mardi PM]"))
DMaPmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi PM Début]")), "hh:mm")
FMaPmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi PM Fin]")), "hh:mm")

AbsMeAmR5 = Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mercredi AM]"))
DMeAmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi AM Début]")), "hh:mm")
FMeAmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi AM Fin]")), "hh:mm")
AbsMePmR5 = Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mercredi PM]"))
DMePmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi PM Début]")), "hh:mm")
FMePmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi PM Fin]")), "hh:mm")

AbsJeAmR5 = Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Jeudi AM]"))
DJeAmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi AM Début]")), "hh:mm")
FJeAmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi AM Fin]")), "hh:mm")
AbsJePmR5 = Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Jeudi PM]"))
DJePmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi PM Début]")), "hh:mm")
FJePmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi PM Fin]")), "hh:mm")

AbsVeAmR5 = Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Vendredi AM]"))
DVeAmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi AM Début]")), "hh:mm")
FVeAmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi AM Fin]")), "hh:mm")
AbsVePmR5 = Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Vendredi PM]"))
DVePmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi PM Début]")), "hh:mm")
FVePmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi PM Fin]")), "hh:mm")

AbsSaAmR5 = Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Samedi AM]"))
DSaAmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi AM Début]")), "hh:mm")
FSaAmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi AM Fin]")), "hh:mm")
AbsSaPmR5 = Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Samedi PM]"))
DSaPmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi PM Début]")), "hh:mm")
FSaPmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi PM Fin]")), "hh:mm")

AbsDiAmR5 = Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Dimanche AM]"))
DDiAmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche AM Début]")), "hh:mm")
FDiAmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche AM Fin]")), "hh:mm")
AbsDiPmR5 = Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Dimanche PM]"))
DDiPmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche PM Début]")), "hh:mm")
FDiPmR5 = Format(Application.WorksheetFunction.XLookup(CbSemType5, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche PM Fin]")), "hh:mm")

End Sub
Private Sub cbsemtype6_AfterUpdate()
AbsLuAmR6 = Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Lundi AM]"))
DLuAmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi AM Début]")), "hh:mm")
FLuAmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi AM Fin]")), "hh:mm")
AbsLuPmR6 = Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Lundi PM]"))
DLuPmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi PM Début]")), "hh:mm")
FLuPmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi PM Fin]")), "hh:mm")

AbsMaAmR6 = Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mardi AM]"))
DMaAmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi AM Début]")), "hh:mm")
FMaAmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi AM Fin]")), "hh:mm")
AbsMaPmR6 = Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mardi PM]"))
DMaPmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi PM Début]")), "hh:mm")
FMaPmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi PM Fin]")), "hh:mm")

AbsMeAmR6 = Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mercredi AM]"))
DMeAmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi AM Début]")), "hh:mm")
FMeAmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi AM Fin]")), "hh:mm")
AbsMePmR6 = Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mercredi PM]"))
DMePmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi PM Début]")), "hh:mm")
FMePmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi PM Fin]")), "hh:mm")

AbsJeAmR6 = Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Jeudi AM]"))
DJeAmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi AM Début]")), "hh:mm")
FJeAmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi AM Fin]")), "hh:mm")
AbsJePmR6 = Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Jeudi PM]"))
DJePmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi PM Début]")), "hh:mm")
FJePmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi PM Fin]")), "hh:mm")

AbsVeAmR6 = Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Vendredi AM]"))
DVeAmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi AM Début]")), "hh:mm")
FVeAmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi AM Fin]")), "hh:mm")
AbsVePmR6 = Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Vendredi PM]"))
DVePmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi PM Début]")), "hh:mm")
FVePmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi PM Fin]")), "hh:mm")

AbsSaAmR6 = Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Samedi AM]"))
DSaAmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi AM Début]")), "hh:mm")
FSaAmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi AM Fin]")), "hh:mm")
AbsSaPmR6 = Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Samedi PM]"))
DSaPmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi PM Début]")), "hh:mm")
FSaPmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi PM Fin]")), "hh:mm")

AbsDiAmR6 = Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Dimanche AM]"))
DDiAmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche AM Début]")), "hh:mm")
FDiAmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche AM Fin]")), "hh:mm")
AbsDiPmR6 = Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Dimanche PM]"))
DDiPmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche PM Début]")), "hh:mm")
FDiPmR6 = Format(Application.WorksheetFunction.XLookup(CbSemType6, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Dimanche PM Fin]")), "hh:mm")

End Sub

Private Sub ChbMajoration_Click()
If ChbMajoration = True Then ChbMajoration.Caption = "Majoré": ChbMajoration.BackColor = RGB(0, 255, 0): Label8.Visible = True: TxtTaux.Visible = True
If ChbMajoration = False Then ChbMajoration.Caption = "Non Majoré": ChbMajoration.BackColor = RGB(255, 0, 0): Label8.Visible = False: TxtTaux.Visible = False
End Sub

Private Sub ObFixe_Click()
If ObRotation = False Then Label12.Visible = False: Label13.Visible = False: CbNbSemRotation.Visible = False: Label21.Visible = False: Datepicker4.Visible = False: _
TxtDateRotation.Visible = False: Label16.Visible = True: CbSemTypeFixe.Visible = True:: Datepicker5.Visible = True: Label22.Visible = True: TxtDateFixe.Visible = True
CbNbSemRotation = "": CbSemType1 = "": CbSemType2 = "": CbSemType3 = "": CbSemType4 = "": CbSemType5 = "": CbSemType6 = "":
CbNbSemRotation.Visible = False: CbSemType1.Visible = False: CbSemType2.Visible = False: CbSemType3.Visible = False: CbSemType4.Visible = False: CbSemType5.Visible = False: CbSemType6.Visible = False:
TxtDateRotation = ""
End Sub

Private Sub ObRotation_Click()
If ObRotation = True Then Label12.Visible = True: Label13.Visible = True: CbNbSemRotation.Visible = True: Datepicker4.Visible = True: Label21.Visible = True: _
TxtDateRotation.Visible = True: Label16.Visible = False: CbSemTypeFixe.Visible = False: CbSemTypeFixe = "": Datepicker5.Visible = False: Label22.Visible = False: _
TxtDateFixe.Visible = False: TxtDateFixe = ""
End Sub

Private Sub CbNbsemRotation_afterupdate()
If CbNbSemRotation.Value = "1" Then MsgBox "Le nombre de semaines doit être au minimum de 2": TxtNbsemRotation = "": CbNbSemRotation.SetFocus: Exit Sub
If CbNbSemRotation.Value = "2" Then Label14.Visible = True: CbSemType1.Visible = True: Label15.Visible = True: CbSemType2.Visible = True: _
    Label17.Visible = False: CbSemType3.Visible = False: Label18.Visible = False: CbSemType4.Visible = False: Label19.Visible = False: CbSemType5.Visible = False: _
    Label20.Visible = False: CbSemType6.Visible = False:
If CbNbSemRotation.Value = "3" Then Label14.Visible = True: CbSemType1.Visible = True: Label15.Visible = True: CbSemType2.Visible = True: Label17.Visible = True: _
    CbSemType3.Visible = True: Label18.Visible = False: CbSemType4.Visible = False: Label19.Visible = False: CbSemType5.Visible = False: Label20.Visible = False: _
    CbSemType6.Visible = False:
If CbNbSemRotation.Value = "4" Then Label14.Visible = True: CbSemType1.Visible = True: Label15.Visible = True: CbSemType2.Visible = True: Label17.Visible = True: _
    CbSemType3.Visible = True: Label18.Visible = True: CbSemType4.Visible = True: Label19.Visible = False: CbSemType5.Visible = False: Label20.Visible = False: _
    CbSemType6.Visible = False:
If CbNbSemRotation.Value = "5" Then Label14.Visible = True: CbSemType1.Visible = True: Label15.Visible = True: CbSemType2.Visible = True: Label17.Visible = True: _
    CbSemType3.Visible = True: Label18.Visible = True: CbSemType4.Visible = True: Label19.Visible = True: CbSemType5.Visible = True: Label20.Visible = False: _
    CbSemType6.Visible = False:
If CbNbSemRotation.Value = "6" Then Label14.Visible = True: CbSemType1.Visible = True: Label15.Visible = True: CbSemType2.Visible = True: Label17.Visible = True: _
    CbSemType3.Visible = True: Label18.Visible = True: CbSemType4.Visible = True: Label19.Visible = True: CbSemType5.Visible = True: Label20.Visible = True: _
    CbSemType6.Visible = True:
End Sub

Private Sub TxtNom_Change()
If Sheets("Données").Range("ag2") = 20 Then MsgBox "Vous avez atteint le nombre maximum de Collaborateurs (20)": Exit Sub
TxtNom.Text = UCase(TxtNom.Text) 'tout en majuscule
If CbRecherche = "" Then BtnModifier.Enabled = False
End Sub
Private Sub TxtPrenom_AfterUpdate()
    TxtInit = Left(TxtPrenom, 1) & "." & TxtNom
End Sub
Private Sub TxtPrenom_Change()
TxtPrenom.Value = WorksheetFunction.Proper(TxtPrenom.Value) '1ere lettre en majuscule
End Sub
Private Sub TxtTaux_afterupdate()
TxtTaux.Value = Format(CDbl(TxtTaux.Value / 100), "0.00%")
End Sub
Sub RemplitlesListes()
CbRecherche.List = [TbEffectif].Value
CbPoste.List = [TbPoste].Value
CbSemTypeFixe.List = [TbSemType].Value
CbSemType1.List = [TbSemType].Value: CbSemType2.List = [TbSemType].Value: CbSemType3.List = [TbSemType].Value: CbSemType4.List = [TbSemType].Value: CbSemType5.List = [TbSemType].Value: CbSemType6.List = [TbSemType].Value
CbNbSemRotation.List = [TbNbSemRotation].Value
End Sub
'*******************************************************************
'event CbRecherche
Private Sub CbRecherche_Click()
'...........................................................................
'ALIMENTER LE USERFORM
'...........................................................................
    With CbRecherche
    If .Column(0) = "" Then vidange: Exit Sub
    BtnValider.Enabled = False
        Me.TxtNom = .Column(0): Me.TxtPrenom = .Column(1): Me.CbPoste = .Column(2): Me.TxtDateNaissance = .Column(3): Me.TxtNbHeure = .Column(4): Me.ChbMajoration = .Column(5): _
        Me.TxtTaux = .Column(6): TxtInit = .Column(7): ObFixe.Value = .Column(8): ObRotation = .Column(9): CbNbSemRotation = .Column(10): _
        CbSemTypeFixe = .Column(11): CbSemType1 = .Column(12): CbSemType2 = .Column(13): CbSemType3 = .Column(14): _
        CbSemType4 = .Column(15): CbSemType5 = .Column(16): CbSemType6 = .Column(17): TxtDateRotation = .Column(18): TxtDateFixe = .Column(19)
        TxtMdp = Application.WorksheetFunction.XLookup(TxtInit, Range("tbid[utilisateur]"), Range("tbid[mdp]"))
        ChbEffectif = Application.WorksheetFunction.XLookup(TxtInit, Range("tbid[utilisateur]"), Range("tbid[usfeffectif]"))
        ChbAbsence = Application.WorksheetFunction.XLookup(TxtInit, Range("tbid[utilisateur]"), Range("tbid[usfajoutabsence]"))
        ChbSemType = Application.WorksheetFunction.XLookup(TxtInit, Range("tbid[utilisateur]"), Range("tbid[usfsemtype]"))
        ChbAmplitude = Application.WorksheetFunction.XLookup(TxtInit, Range("tbid[utilisateur]"), Range("tbid[Amplitude Horaire]"))
        ChbNbparTranche = Application.WorksheetFunction.XLookup(TxtInit, Range("tbid[utilisateur]"), Range("tbid[Nb Pers par Plage]"))
        ChbPosteService = Application.WorksheetFunction.XLookup(TxtInit, Range("tbid[utilisateur]"), Range("tbid[Poste/Service]"))
        ChbCptHres = Application.WorksheetFunction.XLookup(TxtInit, Range("tbid[utilisateur]"), Range("tbid[Compteur Heures]"))
        ChbPoste = Application.WorksheetFunction.XLookup(TxtInit, Range("tbid[utilisateur]"), Range("tbid[Poste]"))
        ChbService = Application.WorksheetFunction.XLookup(TxtInit, Range("tbid[utilisateur]"), Range("tbid[Service]"))
        ChbJrOuv = Application.WorksheetFunction.XLookup(TxtInit, Range("tbid[utilisateur]"), Range("tbid[Jours Ouverture]"))
        ChbPlanning = Application.WorksheetFunction.XLookup(TxtInit, Range("tbid[utilisateur]"), Range("tbid[Planning]"))
        ChbSoldeCompteurhr = Application.WorksheetFunction.XLookup(TxtInit, Range("tbid[utilisateur]"), Range("tbid[Solde Compteur Heures]"))
        If CbNbSemRotation = "2" Then CbSemType1.Visible = True: CbSemType2.Visible = True
        If CbNbSemRotation = "3" Then CbSemType1.Visible = True: CbSemType2.Visible = True: CbSemType3.Visible = True
        If CbNbSemRotation = "4" Then CbSemType1.Visible = True: CbSemType2.Visible = True: CbSemType3.Visible = True: CbSemType4.Visible = True
        If CbNbSemRotation = "5" Then CbSemType1.Visible = True: CbSemType2.Visible = True: CbSemType3.Visible = True: CbSemType4.Visible = True: CbSemType5.Visible = True
        If CbNbSemRotation = "6" Then CbSemType1.Visible = True: CbSemType2.Visible = True: CbSemType3.Visible = True: CbSemType4.Visible = True: CbSemType5.Visible = True: CbSemType6.Visible = True
    End With
    TxtNom.Enabled = False: TxtPrenom.Enabled = False: BtnModifier.Visible = True: BtnValider.Visible = False: BtnSupprimer.Visible = True
End Sub

Sub vidange()
Dim Ctrl As Control
 
For Each Ctrl In Me.Controls
     If TypeOf Ctrl Is MSForms.CheckBox Then Ctrl.Object.Value = False
Next Ctrl

  '  Dim Ctrl
    For Each Ctrl In Me.Controls
        Select Case TypeName(Ctrl): Case "TextBox", "ComboBox", "Checkbox": Ctrl.Value = "": End Select
    Next
ChbMajoration = False: ObFixe = False: ObRotation = False
Label8.Visible = False: TxtTaux.Visible = False
Label12.Visible = False: Label13.Visible = False: CbNbSemRotation.Visible = False
Label14.Visible = False: Label15.Visible = False: CbSemType1.Visible = False: CbSemType2.Visible = False
Label16.Visible = False: CbSemTypeFixe.Visible = False
Label14.Visible = False: Label15.Visible = False: Label17.Visible = False: Label18.Visible = False: Label19.Visible = False: Label20.Visible = False:
CbSemType1.Visible = False: CbSemType2.Visible = False: CbSemType3.Visible = False: CbSemType4.Visible = False: CbSemType5.Visible = False: _
CbSemType6.Visible = False: TxtDateRotation.Visible = False: Label21.Visible = False: Datepicker4.Visible = False: Datepicker5.Visible = False: Label22.Visible = False: TxtDateFixe.Visible = False
BtnValider.Enabled = True: BtnModifier.Enabled = True: CbSemTypeFixe.Enabled = True: TxtDateFixe.Enabled = True: ObFixe.Locked = False: ObRotation.Locked = False
TxtNom.Enabled = True: TxtPrenom.Enabled = True: BtnModifier.Visible = False: BtnValider.Visible = True: BtnSupprimer.Visible = False

End Sub
Private Sub BtnRAZ_Click()
vidange
End Sub
Private Sub Datepicker4_Click(): Call affichage_calendrier2(TxtDateRotation): End Sub
Private Sub Datepicker5_Click(): Call affichage_calendrier2(TxtDateFixe): End Sub
Private Sub Datepicker6_Click(): Call affichage_calendrier(TxtDateNaissance): End Sub
Private Sub BtnQuitter_Click()
Unload Me
End Sub

Dranreb à dit:
Le plus simple c'est un clic droit sur l'expression à auditer, commande Ajouter un espion…

voila le resultat de mon espion

Code:

Watch :   : monespion : <Expression non définie dans le contexte> : Empty : UsfEffectif.BtnValider_Click

Microsoft 365 add.range plante Excel

eric72

XLDnaute Accro

Pièces jointes

eric72

Dranreb

XLDnaute Barbatruc

eric72

XLDnaute Accro

eric72

XLDnaute Accro

Dranreb

XLDnaute Barbatruc

TooFatBoy

XLDnaute Barbatruc

eric72

XLDnaute Accro

eric72

XLDnaute Accro

Dranreb

XLDnaute Barbatruc

TooFatBoy

XLDnaute Barbatruc

eric72

XLDnaute Accro

eric72

XLDnaute Accro

Dranreb

XLDnaute Barbatruc

eric72

XLDnaute Accro

Dranreb

XLDnaute Barbatruc

eric72

XLDnaute Accro

Microsoft 365 add.range plante Excel

XLDnaute Accro

Pièces jointes

XLDnaute Barbatruc

XLDnaute Accro

XLDnaute Accro

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Accro

XLDnaute Accro

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Accro

XLDnaute Accro

XLDnaute Barbatruc

XLDnaute Accro

XLDnaute Barbatruc

XLDnaute Accro

Privacy & Transparency

Privacy & Transparency