Option Compare Text
Dim f, ligneEnreg
Private Sub UserForm_Initialize()
Dim a()
Set f = Sheets("BD")
Clé = Application.Transpose(f.Range("A2:A" & f.[a65000].End(xlUp).Row).Value)
Call Tri(Clé, LBound(Clé), UBound(Clé))
Me.NomCherche.List = Clé
ligneEnreg = f.[a65000].End(xlUp).Row + 1
Me.NomCherche.SetFocus
End Sub
Private Sub NomCherche_click()
ligneEnreg = Sheets("BD").[A:A].Find(NomCherche, LookIn:=xlValues).Row
For Each c In Me.Controls
nom_control = c.Name
If nom_control <> "NomCherche" Then
col = Application.Match(nom_control, [titre], 0)
Select Case TypeName(c)
Case "TextBox"
Me(nom_control) = f.Cells(ligneEnreg, col)
Case "Frame"
For Each opt In c.Controls
If f.Cells(ligneEnreg, col) = opt.Caption Then opt.Value = True
Next opt
Case "ListBox"
temp = f.Cells(ligneEnreg, col)
a = Split(temp, ";")
For j = 0 To Me(nom_control).ListCount - 1: Me(nom_control).Selected(j) = False: Next j
If UBound(a) >= 0 Then
For j = 0 To Me(nom_control).ListCount - 1
If Not IsError(Application.Match(Me(nom_control).List(j), a, 0)) Then
Me(nom_control).Selected(j) = True
Else
Me(nom_control).Selected(j) = False
End If
Next j
End If
End Select
End If
Next c
Me.NomPrenom.SetFocus
End Sub
Private Sub bt_valider_Click()
If Me.NomPrenom = "" Or ligneEnreg = 0 Then Exit Sub
If MsgBox("Etes-vous certain de vouloir modifier ce contact ?", vbYesNo, "Demande de confirmation") = vbYes And ligneEnreg > 1 Then
For Each c In Me.Controls
nom_control = c.Name
If nom_control <> "NomCherche" Then
col = Application.Match(nom_control, [titre], 0)
Select Case TypeName(c)
Case "TextBox", "ComboBox"
tmp = Me(nom_control)
If IsNumeric(tmp) Then tmp = Val(tmp)
If IsDate(tmp) Then tmp = CDate(tmp)
f.Cells(ligneEnreg, col) = tmp
Case "Frame"
For Each opt In c.Controls
If opt.Value = True Then f.Cells(ligneEnreg, col) = opt.Caption
Next opt
Case "ListBox"
For i = 0 To Me(nom_control).ListCount - 1
If Me(nom_control).Selected(i) = True Then
temp = temp & Me(nom_control).List(i) & ";"
End If
Next i
f.Cells(ligneEnreg, col) = temp
End Select
End If
Next c
raz
UserForm_Initialize
ligneEnreg = f.[a65000].End(xlUp).Row + 1
End If
End Sub
Private Sub B_création_Click()
raz
ligneEnreg = f.[a65000].End(xlUp).Row + 1
Me.NomPrenom.SetFocus
End Sub
Sub raz()
Dim c As Control
For Each c In Me.Controls
Select Case TypeName(c)
Case "TextBox"
c.Value = ""
Case "CheckBox"
c.Value = False
Case "ComboBox"
c.ListIndex = -1
Case "Frame"
For Each b In c.Controls
If TypeName(b) = "OptionButton" Then b.Value = False
Next b
Case "ListBox"
nom_control = c.Name
For j = 0 To Me(nom_control).ListCount - 1: Me(nom_control).Selected(j) = False: Next j
End Select
Next c
End Sub
Private Sub B_suivant_Click()
If Me.NomCherche.ListIndex < Me.NomCherche.ListCount - 1 Then
Me.NomCherche.ListIndex = Me.NomCherche.ListIndex + 1
End If
End Sub
Private Sub b_précédent_Click()
If Me.NomCherche.ListIndex > 0 Then
Me.NomCherche.ListIndex = Me.NomCherche.ListIndex - 1
End If
End Sub