
Public Nbre As Integer
Dim PrixActivites, PrixActivite(2 To 8) As Currency
Dim FedePrix As Double

Private Declare PtrSafe Function FindWindowA Lib "User32" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr

Private Declare PtrSafe Function EnableWindow Lib "User32" _
(ByVal hWnd As LongPtr, ByVal bEnable As LongPtr) As LongPtr

Private Declare PtrSafe Function GetWindowLongA Lib "User32" _
(ByVal hWnd As LongPtr, ByVal nIndex As LongPtr) As LongPtr

Private Declare PtrSafe Function SetWindowLongA Lib "User32" _
(ByVal hWnd As LongPtr, ByVal nIndex As LongPtr, _
ByVal dwNewLong As LongPtr) As LongPtr


Private Sub UserForm_Initalize()
Dim hWnd As Long
hWnd = FindWindowA(vbNullString, Me.Caption)
SetWindowLongA hWnd, -16, GetWindowLongA(hWnd, -16) Or &H20000

    With Sheets("Donnees").Range("A1")
    Me.ComboBox1.RowSource = Range(.Cells, .End(xlDown)(1, 1)).Address(External:=True)
    End With
    Me.MultiPage1.Page1.Value = True
'Formats
    Me.TextBox4.Value = Format(Me.TextBox4.Value, "00""00""00""00""00")
    Me.TextBox7.Value = Format(Me.TextBox4.Value, "00""00""00""00""00")
    Me.TextBox8.Value = Format(Me.TextBox4.Value, "00""00""00""00""00")
    Me.TextBox5.Value = Format(Me.TextBox5.Value, "dd/mm/yy")
    Me.ComboBox1.Value = Format(Me.ComboBox1.Value, "00000 @")
    Me.Controls("Montant").Value = FormatNumber(Me.Controls("Montant").Value, 2)
    For F = 1 To 21 Step 2
    Me.Controls("M" & F).Value = Format(Me.Controls("M" & F).Value, "0000000")
    Next F
    For G = 2 To 22 Step 2
    Me.Controls("M" & G).Value = Format(Me.Controls("M" & G).Value, "# ###.00")
    Next G
    For H = 1 To 32 Step 1
    Me.Controls("C" & H).Value = Format(Me.Controls("C" & H).Value, "# ###.00")
    Next H
    For J = 2 To 8 Step 2
    Me.Controls("Cout" & J).Value = Format(Me.Controls("Cout" & J).Value, "# ###.00")
    PrixActivites(J) = 0
    Next J
    
    
    Me.Controls("FedeCh").Value = Format(Me.Controls("FedeCh").Value, "0000000")
    Me.Controls("SommeFede").Value = Format(Me.Controls("SommeFede").Value, "# ###.00")

    Me.Controls("Total").Value = Format(Me.Controls("Total").Value, "# ###.00")
    PrixActivites = Nothing

End Sub



Private Sub Fede_Change()
'Fédération
    If Me.Controls("Fede").Value = True Then
        Me.Controls("SommeFede").Value = FormatNumber(FedePrix, 2)
        ElseIf Me.Controls("Fede").Value = False Then
        Me.Controls("SommeFede").Value = ""
    End If
End Sub



Private Sub Montant_Change()
TotalSomme
TotalSommeCours
End Sub



Private Sub Photo_Change()
'Photos
    If Me.Controls("Photo").Value = True Then
        Me.Controls("NoPhoto").Value = False
    End If
End Sub

Private Sub NoPhoto_Change()
'NoPhotos
    If Me.Controls("NoPhoto").Value = True Then
        Me.Controls("Photo").Value = False
    End If
End Sub


Private Sub SommeFede_Change()
'Fédération
    If Me.Controls("SommeFede").Value > 0 Then
        Me.Controls("Fede").Value = True
        ElseIf Me.Controls("SommeFede").Value = "" Then
        Me.Controls("Fede").Value = False
    End If
End Sub




Private Sub UserForm_Activate()
EnableWindow FindWindowA("XLMAIN", Application.Caption), 1

FedePrix = Range("AW1")

'Gerer le nom de l eleve
    Dim Marque2
    Dim Cll
    Dim plg
    Marque2 = Range("HH3").Value
    Range("B4").Select
    For Each Cll In Worksheets("Tableau").Range("B4:B300")
        If Cll.Value = Marque2 Then
            plg = plg & Cll.Address() & ","
            Exit For
        End If
    Next Cll
    If Len(plg) > 0 Then Range(Left(plg, Len(plg) - 1)).Select
    ActiveCell.Offset(0, -1).Select
    
'Remplir les textBoxs et ComboBoxs a partir des valeurs dans la Database


    'Remplir la Page 1
    Dim i

    For i = 1 To 2
        Me.Controls("TextBox" & i).Value = ActiveCell.Offset(0, i).Value
    Next i

        Me.Controls("TextBox4").Value = Format(ActiveCell.Offset(0, 4).Value, "00 00 00 00 00")
        Me.Controls("TextBox7").Value = Format(ActiveCell.Offset(0, 51).Value, "00 00 00 00 00")
        Me.Controls("TextBox8").Value = Format(ActiveCell.Offset(0, 52).Value, "00 00 00 00 00")
        Me.Controls("TextBox5").Value = Format(ActiveCell.Offset(0, 5).Value, "dd/mm/yy")
        
    Me.Controls("ComboBox1").Value = ActiveCell.Offset(0, 3).Value
    
    'Essai
    If ActiveCell.Offset(0, 93).Value = "X" Then
        Me.Controls("EssaiSlmnt").Value = True
        ElseIf ActiveCell.Offset(0, 93).Value = "" Then
        Me.Controls("EssaiSlmnt").Value = False
    End If
    'Responsable
    If ActiveCell.Offset(0, 44).Value = "Oui" Then
        Me.Controls("Resp").Value = True
        ElseIf ActiveCell.Offset(0, 44).Value = "" Then
        Me.Controls("Resp").Value = False
    End If
    'Stages
    If ActiveCell.Offset(0, 16).Value = "X" Then
        Me.Controls("Stag").Value = True
        ElseIf ActiveCell.Offset(0, 16).Value = "" Then
        Me.Controls("Stag").Value = False
    End If
    'Certificat médical
    Me.Controls("Certif").Value = False
    If ActiveCell.Offset(0, 46).Value = "Oui" Then
        Me.Controls("Certif").Value = True
        ElseIf ActiveCell.Offset(0, 46).Value = "" Then
        Me.Controls("Certif").Value = False
    End If
    'Charte
    Me.Controls("Charte").Value = False
    If ActiveCell.Offset(0, 47).Value = "Ok" Then
        Me.Controls("Charte").Value = True
        ElseIf ActiveCell.Offset(0, 47).Value = "" Then
        Me.Controls("Charte").Value = False
    End If
    'Fédération
    Me.Controls("Fede").Value = False
    If ActiveCell.Offset(0, 48).Value = "Ok" Then
        Me.Controls("Fede").Value = True
        ElseIf ActiveCell.Offset(0, 48).Value = "" Then
        Me.Controls("Fede").Value = False
    End If
    'Agrement Photo
    Me.Controls("Photo").Value = True
    If ActiveCell.Offset(0, 49).Value = "Ok" Then
        Me.Controls("Photo").Value = True
        ElseIf ActiveCell.Offset(0, 49).Value = "No" Then
        Me.Controls("NoPhoto").Value = True
    End If
    'Email
        Me.Controls("Email").Value = ActiveCell.Offset(0, 45).Value
    'Facebook
        Me.Controls("Facebook").Value = ActiveCell.Offset(0, 50).Value
        
    'choix multiples (Nouveau, Ancien, Suivi)
    If ActiveCell.Value = "Nouveau" Then
        OptionButton1.Value = True
        ElseIf ActiveCell.Value = "Ancien" Then
        OptionButton3.Value = True
        Else: OptionButton2.Value = True
    End If

    'Combobox Code ville
    Dim Arr() As String
    Dim C As Integer, NbSheets As Integer
    Dim Liste As Object
    Dim NbCodes
    Set Liste = Worksheets("Donnees").Cells(1, 1).Resize(Worksheets("Donnees").Cells(1, 1).CurrentRegion.Rows.Count - 1, 1)
    NbCodes = Application.CountA(Worksheets("Donnees").Range("A1").EntireColumn)
    ElevesDonnees.ComboBox1.Clear
    ReDim Arr(1 To NbCodes)
    For C = 1 To NbCodes
        Arr(C) = Liste(C).Value
        ElevesDonnees.ComboBox1.AddItem Arr(C)
    Next
    If ActiveCell.Offset(0, 3).Value = "" Then
    Me.Controls("ComboBox1").ListIndex = 49 'augmenter jusqu'à ce que Bures apparaisse
    Else
    Me.Controls("ComboBox1").Value = ActiveCell.Offset(0, 3).Value
    End If
    Me.Controls("Cas").Value = ActiveCell.Offset(0, 15).Value

'Remplir la Page2 avec les liste et la database

    'Remplit les cours avec les donnees de la database
    Dim NC As Integer
    Dim r
    
  PrixActivites = 0
    
    For NC = 2 To 8 Step 2
'        If ActiveCell.Offset(0, 4 + NC).Value = "" Then
'            ActiveCell.Offset(0, 4 + NC).Value = "_"
'        End If
    Me.Controls("ComboBox" & NC).Value = ActiveCell.Offset(0, 4 + NC).Value
    Me.Controls("Activite" & NC).Value = Me.Controls("ComboBox" & NC).Value
    Me.Controls("Horaire" & NC).Value = ActiveCell.Offset(0, 5 + NC).Value

    'Cree la liste de noms de cours pour les ComboBoxs

    'ElevesDonnees.Controls("ComboBox" & NC).AddItem ""
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "Afro traditionnel"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "Afro & CO 1"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "Afro & CO 2"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "Contemporaine"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "Eveil 4-5"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "Eveil 6-7"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "Initiation 8-10"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "HH 1"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "HH 2"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "HH Break"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "HH Break enfant"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "HH Dynamo 1"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "HH Dynamo 2"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "HH House"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "HH Newstyle interm"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "HH Newstyle ados deb"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "HH Newstyle adult deb"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "HH Newstyle adult interm"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "HH Video"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "Atelier SCENIK"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "Jazz Enfants"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "Jazz Ados"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "Jazz Adultes"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "Percus Adultes"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "Percus enfants"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "Stage"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "Artistik"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "Patchwork"
    'ElevesDonnees.Controls("ComboBox" & NC).AddItem "Workshop"
    
    Dim cNom As Range
        Dim ws As Worksheet
        Set ws = Worksheets("Donnees")
        
        For Each cNom In ws.Range("ActivitesNomList")
          With Me.Controls("ComboBox" & NC)
            .AddItem cNom.Value
            .List(.ListCount - 1, 1) = cNom.Offset(0, 1).Value
          End With
    Next cNom
     
    'Et mettre a jour les donnees dans les ComboBoxs et TextBoxs avec la Database
    
    Me.Controls("ComboBox" & NC).Value = ActiveCell.Offset(0, 4 + NC).Value
    Me.Controls("Horaire" & NC).Value = ActiveCell.Offset(0, 5 + NC).Value
    
    Set ws = Sheets("Donnees")
    Dim iFind As Range

    Set iFind = ws.Range("BD4:BD" & ws.Range("BD" & Rows.Count).End(xlUp).Row).Find(What:=Me.Controls("ComboBox" & NC).Value, LookIn:=xlValues, Lookat:=xlWhole)
    If Not iFind Is Nothing Then
        Me.Controls("Cout" & NC).Value = iFind.Offset(0, 1).Value
    Else
        Me.Controls("Cout" & NC).Value = 0
    End If
              
            If Me.Controls("Cout" & NC).Value > 0 Then
                PrixActivite(NC) = CCur(Me.Controls("Cout" & NC).Value)
            Else
                PrixActivite(NC) = 0
            End If
    PrixActivites = PrixActivites + PrixActivite(NC)
    Next NC
    'Et mettre a jour le nombre de cours pris avec la Database
    'Marque2 = Range("AA4").Value
    Nbre = ActiveCell.Offset(0, 14).Value
    
    'Appel de sub pour la mise a jour de la phrase dans page 2
    NombreCours

'Remplir la Page3 avec les liste et la database
    
    'Donnees montant, nbre de cheques et OK
    If IsNumeric(ActiveCell.Offset(0, 17).Value) Then
        If ActiveCell.Offset(0, 17).Value > 0 Then
        Me.Controls("Montant").Value = FormatNumber(ActiveCell.Offset(0, 17).Value, 2)
        Else
        Me.Controls("Montant").Value = FormatNumber(PrixActivites, 2)
        End If
    Else
        Me.Controls("Montant").Value = FormatNumber(PrixActivites, 2)
    End If
        
    Me.Controls("Nbrech").Value = ActiveCell.Offset(0, 18).Value
    
    If ActiveCell.Offset(0, 20).Value = "OK" Then
        Me.Controls("Paye").Value = True
    End If
    
    'Combobox Bank
    BankEntree
    Me.Controls("Bank").Value = ActiveCell.Offset(0, 19).Value

    
    'Donnees n° de cheques et somme
    Dim F
    Dim G
    For F = 1 To 21 Step 2
    Me.Controls("M" & F).Value = Format(ActiveCell.Offset(0, 20 + F).Value, "0000000")
    Next F
    
    For G = 2 To 22 Step 2
    Me.Controls("M" & G).Value = FormatNumber(ActiveCell.Offset(0, 20 + G).Value, 2)
    Next G

    'Fédération
    If ActiveCell.Offset(0, 48).Value = "Ok" Then
        If Me.Controls("SommeFede").Value = "" Then
            Me.Controls("SommeFede").Value = FormatNumber(FedePrix, 2)
            Else
            Me.Controls("SommeFede").Value = FormatNumber(ActiveCell.Offset(0, 56).Value, 2)
            Me.Controls("FedeCh").Value = Format(ActiveCell.Offset(0, 55).Value, "0000000")
        End If
        ElseIf ActiveCell.Offset(0, 48).Value = "" Then
        Me.Controls("SommeFede").Value = ""
        Me.Controls("FedeCh").Value = Format(ActiveCell.Offset(0, 55).Value, "0000000")
    End If
    
    
    'Appel pour mettre a jour la Somme Totale
    TotalSomme
    
    'Donnees répartition somme par cours
    
    For SC = 1 To 32
        Me.Controls("N" & SC).Caption = Worksheets("Donnees").Range("BD3").Offset(SC, 0).Value
        If IsNumeric(ActiveCell.Offset(0, 59 + SC).Value) Then
        Me.Controls("C" & SC).Value = FormatNumber(ActiveCell.Offset(0, 59 + SC).Value, 2)
        Else
        Me.Controls("C" & SC).Value = Format(ActiveCell.Offset(0, 59 + SC).Value, "#")
        End If
    Next SC

    'Appel pour mettre a jour la Somme Totale
    TotalSommeCours
    
End Sub

Private Sub OK_Click()
    Worksheets("Donnees").Activate
    Application.ScreenUpdating = True
    ElevesDonnees.Hide

     
'Page1 et Page3 vers Database

    'Gerer les ComboBox Modifiables

    'ComboBox1 est le nom donne au control ComboBox de Codes
    Dim Code
    Code = ComboBox1.Value
    'Stockage du choix effectue dans la liste deroulante en AA2
    Worksheets("Donnees").Range("AA2").Value = Code
    Worksheets("Donnees").Range("A1:A100").Select
    On Error GoTo Ajoute
    Selection.Find(What:=Code, After:=ActiveCell, LookIn:=xlFormulas, Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
    'Worksheets("Tableau").Activate
    'Worksheets("Tableau").Range("B4").Select
    
    
    'Bank est le nom donne au control ComboBox de Banques
    Dim Banque
    Banque = Bank.Value
    'Stockage du choix effectue dans la liste deroulante en AA5
    Worksheets("Donnees").Range("AA5").Value = Banque
    Worksheets("Donnees").Range("F1:F100").Select
    On Error GoTo Ajoute1
    Selection.Find(What:=Banque, After:=ActiveCell, LookIn:=xlFormulas, Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
    'Worksheets("Tableau").Activate
    'Worksheets("Tableau").Range("B4").Select

    
'Page1 vers Database

    'Reecrire les donnees entrees dans la database : Passage important pour la valeur de ActiveCell
    Dim Repere
    Dim Cel
    Dim plag
    Worksheets("Tableau").Activate
    Repere = Range("HH3").Value
    Range("B4").Select
    For Each Cel In ActiveCell.CurrentRegion
        If Cel.Value = Repere Then
            plag = plag & Cel.Address() & ","
            Exit For
        End If
    Next Cel
    If Len(plag) > 0 Then Range(Left(plag, Len(plag) - 1)).Select
    ActiveCell.Offset(0, -1).Select
    
    'choix multiples (Nouveau, Ancien, Suivi)
    If OptionButton1.Value = True Then
        ActiveCell.Value = "Nouveau"
        ElseIf OptionButton2.Value = True Then
            ActiveCell.Value = "Suivi"
        Else: ActiveCell.Value = "Ancien"
    End If
    
     'choix Essai (X, "")
    If Resp.Value = True Then
        ActiveCell.Offset(0, 93).Value = "X"
        ElseIf Resp.Value = False Then
        ActiveCell.Offset(0, 93).Value = ""
    End If
    
    'choix Responsable (Oui, "")
    If Resp.Value = True Then
        ActiveCell.Offset(0, 44).Value = "Oui"
        ElseIf Resp.Value = False Then
        ActiveCell.Offset(0, 44).Value = ""
    End If
    
    'choix Stages (X, "")
    If Stag.Value = True Then
        ActiveCell.Offset(0, 16).Value = "X"
        ElseIf Stag.Value = False Then
        ActiveCell.Offset(0, 16).Value = ""
    End If
    
    'choix Certificats (Oui, "")
    If Certif.Value = True Then
        ActiveCell.Offset(0, 46).Value = "Oui"
        ElseIf Certif.Value = False Then
        ActiveCell.Offset(0, 46).Value = ""
    End If
    
    'choix Charte (Ok, "")
    If Charte.Value = True Then
        ActiveCell.Offset(0, 47).Value = "Ok"
        ElseIf Charte.Value = False Then
        ActiveCell.Offset(0, 47).Value = ""
    End If
    
    'choix Fédération (Ok, "")
    If Fede.Value = True Then
        ActiveCell.Offset(0, 48).Value = "Ok"
        ElseIf Fede.Value = False Then
        ActiveCell.Offset(0, 48).Value = ""
    End If
    'choix Photo (Ok, No)
    If Photo.Value = True Then
        ActiveCell.Offset(0, 49).Value = "Ok"
        ElseIf NoPhoto.Value = True Then
        ActiveCell.Offset(0, 49).Value = "No"
    End If
    'Remplir la Database a partir des valeurs des textBoxs
    Dim d As Integer
    For d = 1 To 2
        ActiveCell.Offset(0, d).Value = Me.Controls("TextBox" & d).Value
    Next d
    ActiveCell.Offset(0, 3).Value = Me.Controls("ComboBox1").Value

    ActiveCell.Offset(0, 4).Value = Format(Me.Controls("TextBox4").Value, "00 00 00 00 00")
    ActiveCell.Offset(0, 51).Value = Format(Me.Controls("TextBox7").Value, "00 00 00 00 00")
    ActiveCell.Offset(0, 52).Value = Format(Me.Controls("TextBox8").Value, "00 00 00 00 00")
    ActiveCell.Offset(0, 5).Value = Format(Me.Controls("TextBox5").Value, "mm/dd/yy")

    ActiveCell.Offset(0, 15).Value = Me.Controls("Cas").Value
    ActiveCell.Offset(0, 45).Value = Me.Controls("Email").Value
    ActiveCell.Offset(0, 50).Value = Me.Controls("Facebook").Value
    
'Page2 vers Database

    'Remplir la Database a partir des valeurs de cours
    Dim NC
    For NC = 2 To 8 Step 2
    ActiveCell.Offset(0, 4 + NC).Value = Me.Controls("ComboBox" & NC).Value
    ActiveCell.Offset(0, 5 + NC).Value = Me.Controls("Horaire" & NC).Value
    Next NC
    NombreCours
    'Mettre a jour la Database avec le nombre de cours pris
    ActiveCell.Offset(0, 14).Value = Nbre
    
'Page3 vers Database
   
    'Donnees montant, nbre de cheques et OK
    ActiveCell.Offset(0, 17).Value = CDbl(Me.Controls("Montant").Value)
    ActiveCell.Offset(0, 18).Value = Me.Controls("Nbrech").Value
    If Me.Controls("Paye").Value = True Then
        ActiveCell.Offset(0, 20).Value = "OK"
    End If
    ActiveCell.Offset(0, 19).Value = Me.Controls("Bank").Value
    
    'Donnees n° de cheques et somme
    Dim L As Integer
    For L = 1 To 22
        If IsNumeric(Me.Controls("M" & L).Value) Then
        ActiveCell.Offset(0, 20 + L).Value = CDbl(Me.Controls("M" & L).Value)
        Else
        ActiveCell.Offset(0, 20 + L).Value = Me.Controls("M" & L).Value
        End If
        
    Next L

    'choix Fédération (Ok, "")
    If SommeFede.Value <> "" Then
        ActiveCell.Offset(0, 48).Value = "Ok"
        ActiveCell.Offset(0, 55).Value = Me.Controls("FedeCh").Value
        ActiveCell.Offset(0, 56).Value = CDbl(Me.Controls("SommeFede").Value)
        ElseIf SommeFede.Value = "" Then
        ActiveCell.Offset(0, 48).Value = ""
        ActiveCell.Offset(0, 55).Value = Me.Controls("FedeCh").Value
        ActiveCell.Offset(0, 56).Value = Me.Controls("SommeFede").Value
    End If

    'Donnees répartition somme par cours
    Dim SC As Integer
    Dim fieldSC As Double
    For SC = 1 To 32
         If IsNumeric(Me.Controls("C" & SC).Value) Then
            fieldSC = CDbl(Me.Controls("C" & SC).Value)
            ActiveCell.Offset(0, 59 + SC).Value = Format(fieldSC, "##0,00")
            Else
            ActiveCell.Offset(0, 59 + SC).Value = Me.Controls("C" & SC).Value
         End If
    Next SC

'Suite
    Worksheets("Tableau").Activate
    Application.ScreenUpdating = True
    Worksheets("Tableau").Range("A4", Range("DA4").End(xlDown)).Select
    Selection.Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    Columns("B").Columns.AutoFit
    Columns("R").Columns.AutoFit
    Columns("V:AQ").Columns.AutoFit

    Range("B4").Select

Exit Sub
Ajoute:
    ' Un nouveau Code etant ajouté, ajout de celui-ci a la fin de la colonne A
    Worksheets("Donnees").Activate
    Worksheets("Donnees").Range("A1").End(xlDown).Offset(1, 0).Value = Code
    ' Tri des Codes en ordre croissant
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Resume
Ajoute1:
    ' Une nouvelle Bank etant ajoutée, ajout de celle-ci a la fin de la colonne F
    Worksheets("Donnees").Activate
    Worksheets("Donnees").Range("F1:F60").End(xlDown).Offset(1, 0).Value = Banque
    ' Tri des Codes en ordre croissant
    'Selection.Sort Key1:=Range("F1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Resume
    
End Sub

Private Sub Suivant_Click()
    Worksheets("Donnees").Activate
    Application.ScreenUpdating = True
    ElevesDonnees.Hide
    
     
'Page1 et Page3 vers Database

    'Gerer les ComboBox Modifiables

    'ComboBox1 est le nom donne au control ComboBox de Codes
    Dim Code
    Code = ComboBox1.Value
    'Stockage du choix effectue dans la liste deroulante en AA2
    Worksheets("Donnees").Range("AA2").Value = Code
    Worksheets("Donnees").Range("A1:A100").Select
    On Error GoTo Ajoute
    Selection.Find(What:=Code, After:=ActiveCell, LookIn:=xlFormulas, Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
    'Worksheets("Tableau").Activate
    'Worksheets("Tableau").Range("B4").Select
    
    
    'Bank est le nom donne au control ComboBox de Banques
    Dim Banque
    Banque = Bank.Value
    'Stockage du choix effectue dans la liste deroulante en AA5
    Worksheets("Donnees").Range("AA5").Value = Banque
    Worksheets("Donnees").Range("F1:F100").Select
    On Error GoTo Ajoute1
    Selection.Find(What:=Banque, After:=ActiveCell, LookIn:=xlFormulas, Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
    'Worksheets("Tableau").Activate
    'Worksheets("Tableau").Range("B4").Select

    
'Page1 vers Database

    'Reecrire les donnees entrees dans la database : Passage important pour la valeur de ActiveCell
    Dim Repere
    Dim Cel
    Dim plag
    Worksheets("Tableau").Activate
    Repere = Range("HH3").Value
    Range("B4").Select
    For Each Cel In ActiveCell.CurrentRegion
        If Cel.Value = Repere Then
            plag = plag & Cel.Address() & ","
            Exit For
        End If
    Next Cel
    If Len(plag) > 0 Then Range(Left(plag, Len(plag) - 1)).Select
    ActiveCell.Offset(0, -1).Select
    
    'choix multiples (Nouveau, Ancien, Suivi)
    If OptionButton1.Value = True Then
        ActiveCell.Value = "Nouveau"
        ElseIf OptionButton2.Value = True Then
            ActiveCell.Value = "Suivi"
        Else: ActiveCell.Value = "Ancien"
    End If
    
     'choix Essai (X, "")
    If Resp.Value = True Then
        ActiveCell.Offset(0, 93).Value = "X"
        ElseIf Resp.Value = False Then
        ActiveCell.Offset(0, 93).Value = ""
    End If
    
    'choix Responsable (Oui, "")
    If Resp.Value = True Then
        ActiveCell.Offset(0, 44).Value = "Oui"
        ElseIf Resp.Value = False Then
        ActiveCell.Offset(0, 44).Value = ""
    End If
    
    'choix Stages (X, "")
    If Stag.Value = True Then
        ActiveCell.Offset(0, 16).Value = "X"
        ElseIf Stag.Value = False Then
        ActiveCell.Offset(0, 16).Value = ""
    End If
        
    'choix Certificats (Oui, "")
    If Certif.Value = True Then
        ActiveCell.Offset(0, 46).Value = "Oui"
        ElseIf Certif.Value = False Then
        ActiveCell.Offset(0, 46).Value = ""
    End If
    
    'choix Charte (Ok, "")
    If Charte.Value = True Then
        ActiveCell.Offset(0, 47).Value = "Ok"
        ElseIf Charte.Value = False Then
        ActiveCell.Offset(0, 47).Value = ""
    End If
    
    'choix Fédération (Ok, "")
    If Fede.Value = True Then
        ActiveCell.Offset(0, 48).Value = "Ok"
        ElseIf Fede.Value = False Then
        ActiveCell.Offset(0, 48).Value = ""
    End If
    'choix Photo (Ok, No)
    If Photo.Value = True Then
        ActiveCell.Offset(0, 49).Value = "Ok"
        ElseIf NoPhoto.Value = True Then
        ActiveCell.Offset(0, 49).Value = "No"
    End If
    'Remplir la Database a partir des valeurs des textBoxs
    Dim d As Integer
    For d = 1 To 2
        ActiveCell.Offset(0, d).Value = Me.Controls("TextBox" & d).Value
    Next d
    ActiveCell.Offset(0, 3).Value = Me.Controls("ComboBox1").Value
    
    ActiveCell.Offset(0, 4).Value = Format(Me.Controls("TextBox4").Value, "00 00 00 00 00")
    ActiveCell.Offset(0, 51).Value = Format(Me.Controls("TextBox7").Value, "00 00 00 00 00")
    ActiveCell.Offset(0, 52).Value = Format(Me.Controls("TextBox8").Value, "00 00 00 00 00")
    ActiveCell.Offset(0, 5).Value = Format(Me.Controls("TextBox5").Value, "mm/dd/yy")
        
    ActiveCell.Offset(0, 15).Value = Me.Controls("Cas").Value
    ActiveCell.Offset(0, 45).Value = Me.Controls("Email").Value
    ActiveCell.Offset(0, 50).Value = Me.Controls("Facebook").Value
    
'Page2 vers Database

    'Remplir la Database a partir des valeurs de cours
    Dim NC
    For NC = 2 To 8 Step 2
    ActiveCell.Offset(0, 4 + NC).Value = Me.Controls("ComboBox" & NC).Value
    ActiveCell.Offset(0, 5 + NC).Value = Me.Controls("Horaire" & NC).Value
    Next NC
    NombreCours
    'Mettre a jour la Database avec le nombre de cours pris
    ActiveCell.Offset(0, 14).Value = Nbre
    
'Page3 vers Database
    
    'Donnees montant, nbre de cheques et OK
    ActiveCell.Offset(0, 17).Value = CDbl(Me.Controls("Montant").Value)
    ActiveCell.Offset(0, 18).Value = Me.Controls("Nbrech").Value
    If Me.Controls("Paye").Value = True Then
        ActiveCell.Offset(0, 20).Value = "OK"
    End If
    ActiveCell.Offset(0, 19).Value = Me.Controls("Bank").Value
    
    'Donnees n° de cheques et somme
    Dim L As Integer
    For L = 1 To 22
        If IsNumeric(Me.Controls("M" & L).Value) Then
        ActiveCell.Offset(0, 20 + L).Value = CDbl(Me.Controls("M" & L).Value)
        Else
        ActiveCell.Offset(0, 20 + L).Value = Me.Controls("M" & L).Value
        End If
    Next L

    'choix Fédération (Ok, "")
    If SommeFede.Value <> "" Then
        ActiveCell.Offset(0, 48).Value = "Ok"
        ActiveCell.Offset(0, 55).Value = Me.Controls("FedeCh").Value
        ActiveCell.Offset(0, 56).Value = CDbl(Me.Controls("SommeFede").Value)
        ElseIf SommeFede.Value = "" Then
        ActiveCell.Offset(0, 48).Value = ""
        ActiveCell.Offset(0, 55).Value = Me.Controls("FedeCh").Value
        ActiveCell.Offset(0, 56).Value = Me.Controls("SommeFede").Value
    End If
    'choix Fédération (Ok, "")
    If SommeFede.Value = FedePrix Then
        ActiveCell.Offset(0, 48).Value = "Ok"
        ElseIf SommeFede.Value = "" Then
        ActiveCell.Offset(0, 48).Value = ""
    End If

    'Donnees répartition somme par cours
    Dim SC As Integer
    Dim fieldVal As Double
    For SC = 1 To 32
     If IsNumeric(Me.Controls("C" & SC).Value) Then
            fieldSC = CDbl(Me.Controls("C" & SC).Value)
            ActiveCell.Offset(0, 59 + SC).Value = Format(fieldSC, "##0,00")
            Else
            ActiveCell.Offset(0, 59 + SC).Value = Me.Controls("C" & SC).Value
         End If
    Next SC
    
    Set PrixActivites = Nothing

'Suite
    Worksheets("Tableau").Activate
    Application.ScreenUpdating = True
    Worksheets("Tableau").Range("A4", Range("DA4").End(xlDown)).Select
    Selection.Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    
    Columns("B").Columns.AutoFit
    Columns("R").Columns.AutoFit
    Columns("V:AQ").Columns.AutoFit
    
    Range("B4").Select

Eleves.Show

Exit Sub
Ajoute:
    ' Un nouveau Code etant ajouté, ajout de celui-ci a la fin de la colonne A
    Worksheets("Donnees").Activate
    Worksheets("Donnees").Range("A1").End(xlDown).Offset(1, 0).Value = Code
    ' Tri des Codes en ordre croissant
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Resume
Ajoute1:
    ' Une nouvelle Bank etant ajoutée, ajout de celle-ci a la fin de la colonne F
    Worksheets("Donnees").Activate
    Worksheets("Donnees").Range("F1:F60").End(xlDown).Offset(1, 0).Value = Banque
    ' Tri des Codes en ordre croissant
    'Selection.Sort Key1:=Range("F1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Resume
End Sub

Private Sub ComboBox2_Change()
    ' Mise à jour des items dans la ComboBox2
    Dim NC As Integer
    NC = 2
    Dim i As Integer
        i = ElevesDonnees.Controls("ComboBox" & NC).ListIndex
        ElevesDonnees.Controls("Horaire" & NC).Value = Worksheets("Donnees").Cells(i + 4, 55).Value
        ElevesDonnees.Controls("Cout" & NC).Value = Format$(Worksheets("Donnees").Cells(i + 4, 57).Value, "# ###.00")
           Me.Controls("Activite" & NC).Value = Me.Controls("ComboBox" & NC).Value
           
           If Worksheets("Donnees").Cells(i + 4, 57).Value >= 0 Then
           PrixActivite(NC) = Worksheets("Donnees").Cells(i + 4, 57).Value
           End If
           
           If Not PrixActivite(NC) > 0 Then
            PrixActivite(NC) = 0
            End If
           PrixActivites = PrixActivite(NC)
    'Appel de sub pour la mise a jour de la phrase dans page 2
    NombreCours

End Sub
Private Sub ComboBox4_Change()
    ' Mise à jour des items dans la ComboBox4
    Dim NC As Integer
    NC = 4
    Dim i As Integer
         i = ElevesDonnees.Controls("ComboBox" & NC).ListIndex
        ElevesDonnees.Controls("Horaire" & NC).Value = Worksheets("Donnees").Cells(i + 4, 55).Value
        ElevesDonnees.Controls("Cout" & NC).Value = Format$(Worksheets("Donnees").Cells(i + 4, 57).Value, "# ###.00")
           Me.Controls("Activite" & NC).Value = Me.Controls("ComboBox" & NC).Value
           
            If ElevesDonnees.Controls("ComboBox" & NC).ListIndex > -1 Then
                If PrixActivite(NC) >= 30 Then
                    PrixActivite(NC) = Format$(Worksheets("Donnees").Cells(i + 4, 57).Value, "# ###.00") - 10
                ElseIf PrixActivite(NC) > 0 Then
                    PrixActivite(NC) = Format$(Worksheets("Donnees").Cells(i + 4, 57).Value, "# ###.00")
                Else
                    PrixActivite(NC) = 0
                End If
            End If
            
           PrixActivites = PrixActivite(NC - 2) + PrixActivite(NC)

    'Appel de sub pour la mise a jour de la phrase dans page 2
    NombreCours

End Sub
Private Sub ComboBox6_Change()
    ' Mise à jour des items dans la ComboBox2
    Dim NC As Integer
    NC = 6
    Dim i As Integer
         i = ElevesDonnees.Controls("ComboBox" & NC).ListIndex
        ElevesDonnees.Controls("Horaire" & NC).Value = Worksheets("Donnees").Cells(i + 4, 55).Value
        ElevesDonnees.Controls("Cout" & NC).Value = Format$(Worksheets("Donnees").Cells(i + 4, 57).Value, "# ###.00")
           Me.Controls("Activite" & NC).Value = Me.Controls("ComboBox" & NC).Value
           
            If ElevesDonnees.Controls("ComboBox" & NC).ListIndex > -1 Then
                If PrixActivite(NC) >= 30 Then
                    PrixActivite(NC) = Format$(Worksheets("Donnees").Cells(i + 4, 57).Value, "# ###.00") - 10
                ElseIf PrixActivite(NC) > 0 Then
                    PrixActivite(NC) = Format$(Worksheets("Donnees").Cells(i + 4, 57).Value, "# ###.00")
                Else
                    PrixActivite(NC) = 0
                End If
            End If
            
           PrixActivites = PrixActivite(NC - 4) + PrixActivite(NC - 2) + PrixActivite(NC)
           
    'Appel de sub pour la mise a jour de la phrase dans page 2
    NombreCours

End Sub
Private Sub ComboBox8_Change()
    ' Mise à jour des items dans la ComboBox2
    Dim NC As Integer
    NC = 8
    Dim i As Integer
         i = ElevesDonnees.Controls("ComboBox" & NC).ListIndex
        ElevesDonnees.Controls("Horaire" & NC).Value = Worksheets("Donnees").Cells(i + 4, 55).Value
        ElevesDonnees.Controls("Cout" & NC).Value = Format$(Worksheets("Donnees").Cells(i + 4, 57).Value, "# ###.00")
           Me.Controls("Activite" & NC).Value = Me.Controls("ComboBox" & NC).Value
           
            If ElevesDonnees.Controls("ComboBox" & NC).ListIndex > -1 Then
                If PrixActivite(NC) >= 30 Then
                    PrixActivite(NC) = Format$(Worksheets("Donnees").Cells(i + 4, 57).Value, "# ###.00") - 10
                ElseIf PrixActivite(NC) > 0 Then
                    PrixActivite(NC) = Format$(Worksheets("Donnees").Cells(i + 4, 57).Value, "# ###.00")
                Else
                    PrixActivite(NC) = 0
                End If
            End If
           
           PrixActivites = PrixActivite(NC - 6) + PrixActivite(NC - 4) + PrixActivite(NC - 2) + PrixActivite(NC)

    'Appel de sub pour la mise a jour de la phrase dans page 2
    NombreCours

End Sub
Sub NombreCours()
    'Calcul du nombre de cours pour la phrase en page 2
    If ElevesDonnees.Controls("ComboBox2").ListIndex = 1 Then
        Nbre = 0
        ElseIf ElevesDonnees.Controls("ComboBox2").Value = "" Then
        Nbre = 0
            Else
            If ElevesDonnees.Controls("ComboBox4").ListIndex = 1 Then
                Nbre = 1
                ElseIf ElevesDonnees.Controls("ComboBox4").Value = "" Then
                Nbre = 1
                    Else
                    If ElevesDonnees.Controls("ComboBox6").ListIndex = 1 Then
                        Nbre = 2
                        ElseIf ElevesDonnees.Controls("ComboBox6").Value = "" Then
                        Nbre = 2
                            Else
                            If ElevesDonnees.Controls("ComboBox8").ListIndex = 1 Then
                                Nbre = 3
                                ElseIf ElevesDonnees.Controls("ComboBox8").Value = "" Then
                                Nbre = 3
                                    Else: Nbre = 4
                            End If
                    End If
            End If
    End If
    'PrixActivites = PrixActivite2 + PrixActivite4 + PrixActivite6 + PrixActivite8
    ElevesDonnees.Controls("Phrase").Value = "Cet élève s'est inscrit à " & Nbre & " cours pour " & PrixActivites & " €."
    ElevesDonnees.Controls("Montant").Value = CCur(PrixActivites)
End Sub
Private Sub BankEntree()
    'Combobox Bank
    Dim Arr() As String
    Dim B As Integer, NbSheets As Integer
    Dim Liste As Object
    Dim NbBanks
    Set Liste = Worksheets("Donnees").Cells(1, 6).Resize(Worksheets("Donnees").Cells(1, 6).CurrentRegion.Rows.Count - 1, 1)
    NbBanks = Application.CountA(Worksheets("Donnees").Range("F1").EntireColumn)
    ElevesDonnees.Bank.Clear
    ReDim Arr(1 To NbBanks)
    For B = 1 To NbBanks
        Arr(B) = Liste(B).Value
        ElevesDonnees.Bank.AddItem Arr(B)
    Next B

End Sub
Private Sub TotalSomme()
    Dim i As Integer
    Dim fieldVal, TSC As Double
    TSC = 0
    
    For i = 2 To 22 Step 2
        fieldVal = Me.Controls("M" & i).Text
        If IsNumeric(fieldVal) Then
        TSC = TSC + CDbl(fieldVal)
        End If
    Next i
    Me.Controls("Total").Value = FormatCurrency(TSC, 2)
    
    If Me.Controls("Montant").Value = "" Then
    Me.Controls("Montant").Value = 0
    End If
    
    If Me.Controls("Montant").Value = 0 And Me.Controls("Total").Value = 0 Then
        Me.Controls("DiffMontantCh").Value = "Pas de somme entrée"
        Else
        Me.Controls("DiffMontantCh").Value = FormatNumber((Me.Controls("Total").Value - Me.Controls("Montant").Value), 2) & " €"
            If Me.Controls("DiffMontantCh").Value = 0 Then
            Me.Controls("DiffMontantCh").Value = "Somme correcte"
            ElseIf Me.Controls("DiffMontantCh").Value < 0 Then
            Me.Controls("DiffMontantCh").Value = "Manque " & FormatNumber((Me.Controls("Total").Value - Me.Controls("Montant").Value), 2) & " à payer!"
            Else: Me.Controls("DiffMontantCh").Value = "Trop payé : " & FormatNumber((Me.Controls("Total").Value - Me.Controls("Montant").Value), 2)
            End If
    End If
    

End Sub
'Private Sub TotalSomme()
 '   Dim X As Integer
 '   Dim Z As Integer
 '   For X = 2 To 22 Step 2
  '      Z = Z + 1
  '      Worksheets("Donnees").Cells(Z, 8).Value = Me.Controls("M" & X).Value
   '     Me.Controls("M" & X).Text = Worksheets("Donnees").Cells(Z, 8).Text
  '  Next X
  '  Me.Controls("Total").Value = Worksheets("Donnees").Cells(12, 8).Value & " €"
'End Sub

Private Sub TotalSommeCours()
    Dim i As Integer
    Dim fieldVal, TSC As Double
    
    For i = 1 To 32 Step 1
    fieldVal = Me.Controls("C" & i).Text
        If IsNumeric(fieldVal) Then
        TSC = TSC + CDbl(fieldVal)
        Me.Controls("C" & i).Value = CDbl(fieldVal)
        End If
    Next i
    Me.Controls("TotalSC").Value = FormatNumber(TSC, 2) & " €"
    Me.Controls("DiffChSC").Value = FormatNumber((Me.Controls("TotalSC").Value - Me.Controls("Total").Value), 2) & " €"
        If Me.Controls("DiffChSC").Value = 0 Then
                If Me.Controls("TotalSC").Value = 0 Then
                Me.Controls("DiffChSC").Value = "Somme non répartie!"
                Else:
                Me.Controls("DiffChSC").Value = "Somme bien répartie!"
                End If
            ElseIf Me.Controls("DiffChSC").Value < 0 Then
            Me.Controls("DiffChSC").Value = "Manque " & FormatNumber((Me.Controls("TotalSC").Value - Me.Controls("Total").Value), 2) & " à répartir!"
            Else:
            Me.Controls("DiffChSC").Value = "Trop réparti : " & FormatNumber((Me.Controls("TotalSC").Value - Me.Controls("Total").Value), 2)
        End If

End Sub

Private Sub Finir_Click()
ElevesDonnees.Hide
End Sub

Private Sub Euros_Click()
TotalSomme
End Sub

Private Sub Francs_Click()
TotalSomme
End Sub

Private Sub M10_Change()
TotalSomme
End Sub

Private Sub M12_Change()
TotalSomme
End Sub

Private Sub M14_Change()
TotalSomme
End Sub


Private Sub M16_Change()
TotalSomme
End Sub

Private Sub M18_Change()
TotalSomme
End Sub

Private Sub M2_Change()
TotalSomme
End Sub

Private Sub M20_Change()
TotalSomme
End Sub

Private Sub M22_Change()
TotalSomme
End Sub

Private Sub M4_Change()
TotalSomme
End Sub

Private Sub M6_Change()
TotalSomme
End Sub

Private Sub M8_Change()
TotalSomme
End Sub


Private Sub C1_Change()
 TotalSommeCours
 
End Sub
Private Sub C2_Change()
 TotalSommeCours
 
End Sub
Private Sub C3_Change()
 TotalSommeCours

End Sub
Private Sub C4_Change()
 TotalSommeCours
End Sub
Private Sub C5_Change()
 TotalSommeCours
 
End Sub
Private Sub C6_Change()
 TotalSommeCours
End Sub
Private Sub C7_Change()
 TotalSommeCours
End Sub
Private Sub C8_Change()
 TotalSommeCours
End Sub
Private Sub C9_Change()
 TotalSommeCours
End Sub
Private Sub C10_Change()
 TotalSommeCours
End Sub
Private Sub C11_Change()
 TotalSommeCours
End Sub
Private Sub C12_Change()
 TotalSommeCours
End Sub
Private Sub C13_Change()
 TotalSommeCours
End Sub
Private Sub C14_Change()
 TotalSommeCours
End Sub
Private Sub C15_Change()
 TotalSommeCours
End Sub
Private Sub C16_Change()
 TotalSommeCours
End Sub
Private Sub C17_Change()
 TotalSommeCours
End Sub
Private Sub C18_Change()
 TotalSommeCours
End Sub
Private Sub C19_Change()
 TotalSommeCours
End Sub
Private Sub C20_Change()
 TotalSommeCours
End Sub
Private Sub C21_Change()
 TotalSommeCours
End Sub
Private Sub C22_Change()
 TotalSommeCours
End Sub
Private Sub C23_Change()
 TotalSommeCours
End Sub
Private Sub C24_Change()
 TotalSommeCours
End Sub
Private Sub C25_Change()
 TotalSommeCours
End Sub
Private Sub C26_Change()
 TotalSommeCours
End Sub
Private Sub C27_Change()
 TotalSommeCours
End Sub
Private Sub C28_Change()
 TotalSommeCours
End Sub
Private Sub C29_Change()
 TotalSommeCours
End Sub
Private Sub C30_Change()
 TotalSommeCours
End Sub
Private Sub C31_Change()
 TotalSommeCours
End Sub
Private Sub C32_Change()
 TotalSommeCours
End Sub

