Dim f, g, TblBd(), choix1(), NbCol, ligneEnreg, ColClé
Private Sub UserForm_Initialize()
Set f = Sheets("DI")
TblBd = f.[A1].CurrentRegion.Value
NbCol = UBound(TblBd, 2)
'For Z = 1 To NbCol: Me("label" & Z).Caption = f.Cells(1, Z): Next Z
'For Z = NbCol + 1 To 20: Me("label" & Z).Visible = False: Me("textbox" & Z).Visible = False: Next Z
Me.OptionButton1.Value = True
Set g = Sheets("DATA")
Set mondico = CreateObject("Scripting.Dictionary")
For Each c In Range(g.[A2], g.[A65000].End(xlUp))
mondico(c.Value) = ""
Next c
Me.ComboBox1.List = mondico.keys
Set mondico = CreateObject("Scripting.Dictionary")
For Each c In Range(g.[E2], g.[E65000].End(xlUp))
mondico(c.Value) = ""
Next c
Me.ComboBox2.List = mondico.keys
Set mondico = CreateObject("Scripting.Dictionary")
For Each c In Range(g.[G2], g.[G65000].End(xlUp))
mondico(c.Value) = ""
Next c
Me.ComboBox3.List = mondico.keys
Set mondico = CreateObject("Scripting.Dictionary")
For Each c In Range(g.[F2], g.[F65000].End(xlUp))
mondico(c.Value) = ""
Next c
Me.ComboBox4.List = mondico.keys
End Sub
Private Sub CommandButton3_Click()
'On Error Resume Next
If TextBox1 = "" Then
'Message à l'utilisateur
MsgBox "Saisie d'un numéro de référence obligatoire.", vbInformation
'sortie de la procédure
Exit Sub
End If
If TextBox1 <> "" Then
Sheets("DI").Select
nb_contact = 0
For i = 2 To 5000
If IsEmpty(Cells(i, 1)) Then
nb_contact = i - 2
Exit For
End If
Next
Sheets("DI").Cells(nb_contact + 2, 1) = TextBox1
Sheets("DI").Cells(nb_contact + 2, 2) = ComboBox1.Value
Sheets("DI").Cells(nb_contact + 2, 3) = ComboBox2.Value
Sheets("DI").Cells(nb_contact + 2, 4) = ComboBox5.Value
Sheets("DI").Cells(nb_contact + 2, 5) = ComboBox4.Value
Sheets("DI").Cells(nb_contact + 2, 6) = TextBox2
Sheets("DI").Cells(nb_contact + 2, 7) = ComboBox3.Value
Sheets("DI").Cells(nb_contact + 2, 8) = TextBox3
Sheets("DI").Cells(nb_contact + 2, 9) = TextBox4
Sheets("DI").Cells(nb_contact + 2, 10) = TextBox5
End If
Sheets("Base").Select
nb_contact = 0
For i = 2 To 5000
If IsEmpty(Cells(i, 1)) Then
nb_contact = i - 2
Exit For
End If
Next
Sheets("BASE").Cells(nb_contact + 2, 1) = TextBox1
Sheets("BASE").Cells(nb_contact + 2, 2) = ComboBox1.Value
Sheets("BASE").Cells(nb_contact + 2, 3) = ComboBox2.Value
Sheets("BASE").Cells(nb_contact + 2, 4) = ComboBox5.Value
Sheets("BASE").Cells(nb_contact + 2, 5) = ComboBox4.Value
Sheets("BASE").Cells(nb_contact + 2, 6) = TextBox2
Sheets("BASE").Cells(nb_contact + 2, 7) = ComboBox3.Value
Sheets("BASE").Cells(nb_contact + 2, 8) = TextBox3
Sheets("BASE").Cells(nb_contact + 2, 9) = TextBox4
Sheets("BASE").Cells(nb_contact + 2, 10) = TextBox5
Sheets("BASE").Cells(nb_contact + 2, 11) = TextBox32
Sheets("BASE").Cells(nb_contact + 2, 12) = TextBox33
' Sheets("Liste").Cells(nb_contact + 2, 17) = ComboBox5.Value
'End If
End Sub
Private Sub OptionButton1_Click()
ColClé = 1
ListeChoix
'Me.choixitem = "Choix du nom(frapper les premières lettres)"
End Sub
Private Sub OptionButton2_Click()
ColClé = 9
ListeChoix
'Me.choixitem = "Choix raison sociale"
End Sub
Sub ListeChoix()
Set Rng = f.Range("A2:A" & f.[A65000].End(xlUp).Row).Offset(, ColClé - 1)
choix1 = Application.Transpose(Rng)
Tri choix1, 1, UBound(choix1)
Me.ComboBox6.List = choix1
End Sub
Private Sub ComboBox6_Change()
Me.ComboBox6.List = Filter(choix1, Me.ComboBox6.Text, True, vbTextCompare)
Me.ComboBox6.DropDown
End Sub
'Private Sub ComboBox6_click()
' For i = 1 To UBound(TblBd)
'If TblBd(i, ColClé) = Me.ComboBox1 Then
' For k = 1 To NbCol
' Me("textbox" & k) = TblBd(i, k)
'Next k
' End If
'Next i
'End Sub
Sub Tri(a(), gauc, droi) ' Quick sort
ref = a((gauc + droi) \ 2)
g = gauc: d = droi
Do
Do While a(g) < ref: g = g + 1: Loop
Do While ref < a(d): d = d - 1: Loop
If g <= d Then
temp = a(g): a(g) = a(d): a(d) = temp
g = g + 1: d = d - 1
End If
Loop While g <= d
If g < droi Then Call Tri(a, g, droi)
If gauc < d Then Call Tri(a, gauc, d)
End Sub
Private Sub ComboBox1_click()
Set f = Sheets("DATA")
Set mondico = CreateObject("Scripting.Dictionary")
For Each c In Range(f.[A2], f.[A65000].End(xlUp))
If c = Me.ComboBox1 Then mondico(c.Offset(, 1).Value) = ""
Next c
Me.ComboBox5.List = mondico.keys
Me.ComboBox5.ListIndex = -1
End Sub