Private Sub CmdAnnuler_Click()
With Sheets("Feuil1")
lig = Application.Match(ComboBox1, Columns(1), 0)
Msg = "Voulez vous effacer le Client?"
Style = vbYesNo + vbCritical + vbDefaultButton2
Title = "Confirmation Effacement "
Réponse = MsgBox(Msg, Style, Title)
If Réponse = 7 Then Exit Sub
.Cells(lig, 1).EntireRow.Delete
End With
End Sub
Private Sub CmdNouveau_Click()
ComboBox1 = ""
For i = 1 To 12
Me.Controls("TextBox" & i) = ""
Next i
Exit Sub
TextBox3.SetFocus
End Sub
Private Sub TextBox1_Change()
TextBox1.Value = UCase(TextBox1)
End Sub
Private Sub TextBox10_Change()
KeyAscii = Asc(LCase(Chr(KeyAscii)))
End Sub
Private Sub TextBox11_Change()
KeyAscii = Asc(LCase(Chr(KeyAscii)))
End Sub
Private Sub TextBox3_Change()
TextBox3.Value = WorksheetFunction.Proper(TextBox3.Value)
End Sub
Private Sub TextBox5_Change()
KeyAscii = Asc(LCase(Chr(KeyAscii)))
End Sub
Private Sub TextBox7_Change()
TextBox7.Value = UCase(TextBox7)
End Sub
Private Sub UserForm_Initialize()
Dim derlig As Integer, x As Integer, ws As Worksheet
Set ws = Sheets("Feuil1")
With ws
For x = 3 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
TextBox3.SetFocus
End Sub
Private Sub UserForm_Activate()
ComboBox1.Text = ""
TextBox3.SetFocus
End Sub
Private Sub TextBox4_Change() ' Affiche Nom et Prénom
If TextBox4 <> "" Then _
TextBox1 = TextBox3 & " " & TextBox4
TextBox4.Value = UCase(TextBox4)
End Sub
Private Sub TextBox4_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.", , "Contact"
ComboBox1 = ""
For i = 1 To 12
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("H3:I65000").NumberFormat = "00 00 00 00 00" 'Numéro téléphone et fax
'.Range("A:L").Columns.AutoFit
'.Range("A3:L65000").Sort [A3], xlAscending
End With
For i = 1 To 12
Me.Controls("TextBox" & i) = ""
Next
TextBox3.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
TextBox8 = Format(TextBox8, "00 00 00 00 00")
TextBox9 = Format(TextBox9, "00 00 00 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 12
Me.Controls("TextBox" & i) = ""
Next i
End If
TextBox3.SetFocus
End Sub
Private Sub CmdQuitter_Click()
ComboBox1 = ""
For i = 1 To 12
Me.Controls("TextBox" & i) = ""
Next i
Unload Me
End Sub