1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
|
Option Compare Text
Dim f, ligneEnreg, choix1()
Private Sub UserForm_Initialize()
Set f = Sheets("BD")
choix1 = Application.Transpose(f.Range("A2:A" & f.[a65000].End(xlUp).Row).Value)
Me.ChoixSociete.List = SansDoublons(choix1)
ligneEnreg = f.[a65000].End(xlUp).Row + 1
Me.ChoixSociete.SetFocus
End Sub
Private Sub ChoixSociete_Change()
If Me.ChoixSociete.ListIndex = -1 And IsError(Application.Match(Me.ChoixSociete, choix1, 0)) Then
Me.ChoixSociete.List = Filter(SansDoublons(choix1), Me.ChoixSociete.Text, True, vbTextCompare)
Me.ChoixSociete.DropDown
Else
choixSociete_click
End If
End Sub
Private Sub choixSociete_click()
a = f.Range("A2:B" & f.[B65000].End(xlUp).Row).Value
Dim b(): ReDim b(1 To UBound(a))
j = 0
For i = 1 To UBound(a)
If a(i, 1) = Me.ChoixSociete Then j = j + 1: b(j) = a(i, 2)
Next i
ReDim Preserve b(1 To j)
Me.ChoixGerant.List = b
Me.ChoixGerant.SetFocus
If Val(Application.Version) > 10 Then SendKeys "{f4}"
End Sub
Private Sub ChoixGerant_click()
For i = 2 To f.[B65000].End(xlUp).Row
If f.Cells(i, "a") = Me.ChoixSociete And f.Cells(i, "b") = Me.ChoixGerant Then
ligneEnreg = i
For Each c In Me.Frame_Civilite.Controls
If f.Cells(ligneEnreg, "c") = c.Caption Then c.Value = True
Next c
Me.Controls("TextBox1") = f.Cells(ligneEnreg, 1)
Me.Controls("TextBox2") = f.Cells(ligneEnreg, 2)
For k = 3 To 6
Me.Controls("TextBox" & k) = f.Cells(ligneEnreg, k + 1)
Next k
End If
Next i
End Sub
Function SansDoublons(a())
Set d = CreateObject("Scripting.Dictionary")
For Each c In a
d(c) = ""
Next c
b = d.keys
SansDoublons = b
End Function |
Partager