Private Sub UserForm_Initialize()
Dim derlig As Integer, x As Integer, ws As Worksheet
Set ws = Sheets("Feuil1")
With ws
For x = 2 To .Range("a65536").End(xlUp).Row
ComboBox1 = .Range("a" & x)
If ComboBox1.ListIndex = -1 Then ComboBox1.AddItem .Range("a" & x)
Next x
ComboBox1 = ""
End With
End Sub
Private Sub UserForm_Activate()
Me.Top = 100
Me.Left = Me.Left + 288
ComboBox1.Text = ""
TextBox2.SetFocus
End Sub
Private Sub TextBox3_Change() ' Affiche Nom et Prénom
If TextBox3 <> "" Then _
TextBox1 = TextBox2 & " " & TextBox3
End Sub
Private Sub TextBox3_AfterUpdate()
Dim ctrl As Control, i%, j%, col As Range, ws As Worksheet
On Error GoTo fin
Set ws = Sheets("Feuil1")
Set col = ws.Range("A:A")
If TextBox1 <> "" Then
With ws
i = Application.Match(TextBox1.Text, col, 0)
If .Cells(i, 1).Value = TextBox1.Value Then
MsgBox "Le contact est déjà dans la liste.", , "CONTACTS"
ComboBox1 = ""
For i = 1 To 9
Me.Controls("TextBox" & i) = ""
Next i
Else
Exit Sub
TextBox4.SetFocus
End If
End With
End If
fin:
Exit Sub
End Sub
Private Sub CmdValider_Click()
Dim ctrl As Control, ws As Worksheet, cel As Range
Dim derlig As Integer, col As Integer, i As Long
Set ws = Sheets("Feuil1")
With ws
derlig = .Range("a65536").End(xlUp).Row + 1
For Each ctrl In Me.Controls
col = Val(ctrl.Tag)
If col > 0 Then
If Not IsNumeric(ctrl) Then
.Cells(derlig, col) = ctrl
Else
.Cells(derlig, col) = CDbl(ctrl) 'format numérique simple ex: 3124
End If
End If
Next ctrl
.Range("G2:H65000").NumberFormat = "000 000 00 00" 'Numéro téléphone et fax
.Range("A:I").Columns.AutoFit
.Range("A2:I65000").Sort [A2], xlAscending
End With
For i = 1 To 9
Me.Controls("TextBox" & i) = ""
Next
TextBox2.SetFocus
End Sub
Private Sub CmdRechercher_Click()
Dim ctrl As Control, i%, j%, col As Range, ws As Worksheet
Set ws = Sheets("Feuil1")
Set col = ws.Range("A:A")
With ws
i = Application.Match(Me.ComboBox1.Text, col, 0)
For Each ctrl In Controls
If ctrl.Tag <> "" Then
j = Val(ctrl.Tag)
ctrl.Value = .Cells(i, j).Value
End If
Next ctrl
End With
TextBox7 = Format(TextBox7, "000 000 00 00")
TextBox8 = Format(TextBox8, "000 000 00 00")
End Sub
Private Sub CmdModifier_Click()
Dim ctrl As Control, i%, j%, reponse, col As Range, ws As Worksheet
reponse = MsgBox("Voulez-vous vraiment procéder aux modifications ?", vbYesNo, "CONTACTS")
Set ws = Sheets("Feuil1")
Set col = ws.Range("A:A")
If reponse = vbYes Then
With ws
i = Application.Match(Me.ComboBox1.Text, col, 0)
For Each ctrl In Controls
If ctrl.Tag <> "" Then
j = Val(ctrl.Tag)
.Cells(i, j).Value = ctrl.Value
End If
Next ctrl
End With
Else
ComboBox1 = ""
For i = 1 To 9
Me.Controls("TextBox" & i) = ""
Next i
End If
TextBox2.SetFocus
End Sub
Private Sub CmdQuitter_Click()
ComboBox1 = ""
For i = 1 To 9
Me.Controls("TextBox" & i) = ""
Next i
Unload Me
End Sub