Public Marque


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 Long) As LongPtr

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


Sub AfficheEleves()
    Eleves.Show
End Sub

Private Sub Annuler_Click()
    Eleves.Hide
End Sub


Private Sub Nom_Change()
    RechResp
End Sub


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

    Worksheets("Tableau").Activate
    ' Utilisation de l'option AddItem
    Dim Arr() As String
    Dim i As Integer, NbSheets As Integer
    Dim Liste As Object
    Dim NbNoms
    Set Liste = Worksheets("Tableau").Cells(4, 2).Resize(Worksheets("Tableau").Cells(4, 2).CurrentRegion.Rows.Count - 1, 1)
    NbNoms = Application.CountA(Range("B4").EntireColumn)
    Eleves.Nom.Clear
    ReDim Arr(1 To NbNoms)
    For i = 1 To NbNoms
        Arr(i) = Liste(i).Value
        Eleves.Nom.AddItem Arr(i)
    Next
    Nom.ListIndex = 0
    RechResp
End Sub

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

Private Sub OK_Click()
    Worksheets("Tableau").Activate
    Eleves.Hide
    Application.ScreenUpdating = True
    ' Nom est le nom donne au control ComboBox
    Marque = Nom.Value
    'Stockage du choix effectue dans la liste deroulante en AA2
    Range("HH2").Value = Marque
    Range("HH3").Value = Nom.Value
    Range("B4:B400").Select
On Error GoTo Ajoute
    Selection.Find(What:=Marque, After:=ActiveCell, LookIn:=xlFormulas, Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
    Worksheets("Tableau").Range("B4").Select
    ElevesDonnees.Show
Exit Sub
Ajoute:
    ' Une nouvelle marque etant ajoutee ajout de celle-ci a la fin de la colonne B
    Worksheets("Tableau").Activate
    Worksheets("Tableau").Range("B4").End(xlDown).Offset(1, 0).Value = Marque
    ' Tri des marques en ordre croissant
    Worksheets("Tableau").Range("B4").Select
    ElevesDonnees.Show
End Sub

Private Sub RechResp()
'Rechercher le nom de l eleve
    Dim Cll
    Dim plg
    Dim notel As String
    Worksheets("Tableau").Activate
    Range("HH2").Value = Nom.Value
    If Range("HH2").Value = "" Then
    Marque = Range("HH3").Value
    Else: Marque = Range("HH2").Value
    End If
    Range("B4").Select
    For Each Cll In Worksheets("Tableau").Range("B4:B300")
        If Cll.Value = Marque 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 Responsable
    If ActiveCell.Offset(0, 44).Value = "Oui" Then
        Eleves.Responsable.Value = "Responsable"
    End If
    If ActiveCell.Offset(0, 44).Value = "" Then
        Eleves.Responsable.Value = ""
    End If
    If ActiveCell.Offset(0, 4).Value = "" Then
        Eleves.Tel.Value = ActiveCell.Offset(0, 4).Value
        Else: notel = ActiveCell.Offset(0, 4).Text
        Eleves.Tel.Value = "Telephone : " & notel
    End If
End Sub