Option Explicit
Private Sub ClrUF()
Dim i As Byte: Application.ScreenUpdating = 0
For i = 1 To 18
Controls("TextBox" & Format(i, "00")) = ""
Next i
For i = 2 To 7
Controls("ComboBox" & i) = ""
Next i
End Sub
Private Sub ComboBox1_Change() 'quand le Code client change
If ComboBox1 = "" Then ClrUF: Exit Sub
If ComboBox1.ListIndex = -1 Then Exit Sub
Dim d$, lig&, k As Byte, i As Byte
lig = ComboBox1.ListIndex + 5: Application.ScreenUpdating = 0
For i = 1 To 16
k = i + 2 - 3 * (i > 13)
Controls("TextBox" & Format(i, "00")) = Cells(lig, k)
Next i
d = Cells(lig, 23) * 100: k = Val(d): If k > 0 Then TextBox17 = k & "%"
TextBox18 = Cells(lig, 25)
For i = 2 To 7
k = i - 13 * (i > 2) - 3 * (i > 5) - (i = 7)
Controls("ComboBox" & i) = Cells(lig, k)
Next i
End Sub
Private Sub ComboBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii = 27 Then ClrUF
End Sub
Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim cel As Range, chn$: chn = ComboBox1: If chn = "" Then Exit Sub
Set cel = Columns(1).Find(chn, , -4163, 1, 1): If cel Is Nothing Then ClrUF
End Sub
Private Function GD(chn$) As Variant 'GetDate
If Not IsDate(chn) Then GD = "" Else GD = CDate(chn)
End Function
Private Function GP(chn$) As Variant 'GetPourcentage
If Not IsNumeric(chn) Then GP = "" _
Else GP = Int(Val(Replace$(chn, ",", "."))) / 100
End Function
Private Sub WriteContact(lig&)
With Cells(lig, 1)
.Value = ComboBox1 'Customer ID
.Offset(, 1) = ComboBox2 'Category
.Offset(, 2) = TextBox01 'Company
.Offset(, 3) = TextBox02 'First name/Prénom
.Offset(, 4) = TextBox03 'Last name/Nom
.Offset(, 5) = TextBox04 'Address
.Offset(, 6) = TextBox05 'Zip
.Offset(, 7) = TextBox06 'City
.Offset(, 8) = TextBox07 'State
.Offset(, 9) = TextBox08 'Country
.Offset(, 10) = TextBox09 'Phone
.Offset(, 11) = TextBox10 'Cell
.Offset(, 12) = TextBox11 'Website
.Offset(, 13) = TextBox12 'E-mail
.Offset(, 14) = GD(TextBox13) 'Contact Date
.Offset(, 15) = ComboBox3 'Origin
.Offset(, 16) = ComboBox4 'Product category
.Offset(, 17) = ComboBox5 'Action done
.Offset(, 18) = GD(TextBox14) 'When done
.Offset(, 19) = TextBox15 'Action to be done
.Offset(, 20) = GD(TextBox16) 'No later than
.Offset(, 21) = ComboBox6 'Type
.Offset(, 22) = GP(TextBox17) 'Discount
.Offset(, 23) = ComboBox7 'Vaigah/SFT
.Offset(, 24) = TextBox18 'Comments
End With
Application.ScreenUpdating = -1
End Sub
Private Sub cmdNew_Click() 'bouton Nouveau contact
If MsgBox("Confirmez-vous l'insertion de ce nouveau contact ?", vbYesNo, _
"Demande de confirmation d'ajout") <> vbYes Then Exit Sub
WriteContact Cells(Rows.Count, 1).End(3).Row + 1
End Sub
Private Sub cmdModif_Click() 'bouton Modifier
If MsgBox("Confirmez-vous la modification de ce contact ?", vbYesNo, _
"Demande de confirmation de modification") <> vbYes Then Exit Sub
If ComboBox1.ListIndex <> -1 Then WriteContact ComboBox1.ListIndex + 5
End Sub
Private Sub cmdQuit_Click() 'bouton Quitter
Unload Me
End Sub
Private Sub UserForm_Initialize() 'initialisation du formulaire
Dim T, n&
With ActiveSheet.ListObjects("Customers")
If Not .DataBodyRange Is Nothing Then
n = .ListRows.Count: T = [A5].Resize(n): ComboBox1.List = T 'Customer ID
End If
End With
ComboBox2.List = Array("Customer", "Prospect") 'Category
ComboBox3.List = Array("Google", "Website", "Advertising", "Friends", "Hacked") 'Origin
ComboBox4.List = Array("Moringa", "Soaps", "Honey", "Areca", "Bamboo") 'Product category
ComboBox5.List = Array("Quotation", "Samples", "Brochure", _
"Informations requested", "Mail", "Letter", "Phone call") 'Action done
ComboBox6.List = Array("Individual", "Profesional") 'Type
ComboBox7.List = Array("VAIGAH", "SFT") 'Vaigah / SFT
End Sub