Private Sub UserForm_Initialize()
Dim derlig As Integer, x As Integer
With Sheets("Feuil1")
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.Text = ""
End With
End Sub
Private Sub UserForm_Activate()
Me.Top = Me.Top + 5
Me.Left = Me.Left + 288
ComboBox1.Text = ""
TextBox2.SetFocus
End Sub
Private Sub TextBox3_Change() ' Affiche Nom et Prénom
If TextBox3.Text <> "" Then _
TextBox1.Text = TextBox2.Text & " " & TextBox3.Text
End Sub
Private Sub TextBox4_Enter()
Dim cel As Range, i As Long
TextBox4.SetFocus
If TextBox4 = "" Then
With Sheets("Feuil1").Range("a2:i65000")
Set cel = .Find(TextBox1, , xlValues, xlWhole)
If Not cel Is Nothing Then
MsgBox "Le fournisseur est déjà dans la liste.", , "Fournisseurs"
For i = 1 To 9
Me.Controls("TextBox" & i).Text = ""
Next
End If
End With
Else
Exit Sub
End If
End Sub
Private Sub CommandButton1_Click() 'Bouton Enregistrements
Dim ctrl As Control
Dim col As Integer
Dim derlig As Integer
Dim cel As Range
Dim i As Long
With Sheets("Feuil1")
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).Text = ""
Next
TextBox2.SetFocus
End Sub
Private Sub CommandButton2_Click() 'Bouton Recherche
Dim cel As Range, i As Long
If ComboBox1 <> "" Then
With Sheets("Feuil1").Range("a2:i65000")
Set cel = .Find(ComboBox1, , xlValues, xlWhole)
If Not cel Is Nothing Then
For i = 1 To 9
Me.Controls("TextBox" & i).Text = cel.Offset(0, i - 1)
Next i
TextBox7 = Format(TextBox7, "000 000 00 00")
TextBox8 = Format(TextBox8, "000 000 00 00")
Else
MsgBox "Pas de correspondant en cours.", , "Fornisseurs"
ComboBox1 = ""
For i = 1 To 9
Me.Controls("TextBox" & i).Text = ""
Next i
End If
End With
End If
TextBox2.SetFocus
End Sub
Private Sub CommandButton3_Click() 'Bouton Modification
Dim cel As Range, i As Long
If ComboBox1 <> "" Then
With Sheets("Feuil1").Range("a2:i65000")
Set cel = .Find(ComboBox1, , xlValues, xlWhole)
If Not cel Is Nothing Then
For i = 1 To 9
cel.Offset(0, i - 1) = Me.Controls("TextBox" & i).Text
Next i
End If
End With
End If
For i = 1 To 9
Me.Controls("TextBox" & i).Text = ""
Next i
TextBox2.SetFocus
End Sub
Private Sub CommandButton4_Click() 'Bouton Annuler
Dim i As Long
If MsgBox("Voulez-vous vraiment annuler les modifications ?", vbYesNo, "Fournisseur") = vbYes Then
ComboBox1 = ""
For i = 1 To 9
Me.Controls("TextBox" & i).Text = ""
Next
Else
Exit Sub
End If
TextBox2.SetFocus
End Sub
Private Sub CommandButton5_Click() 'Bouton Fermeture Formulaire
Unload Me
End Sub