Combox.change

BOISGONTIER à dit:

Voir PJ

Code:

Dim f, Liste()
Private Sub UserForm_Initialize()
  Set f = Sheets("bd")
  Liste = f.Range("a2:d" & f.[A65000].End(xlUp).Row).Value
  Me.ComboBox1.List = Liste
End Sub

Private Sub Combobox1_Change()
If Me.ComboBox1.ListIndex = -1 And _
     IsError(Application.Match(Me.ComboBox1, Application.Index(Liste, , 2), 0)) Then
     ncol = UBound(Liste, 2)
     Dim b()
     clé = UCase(Me.ComboBox1) & "*"
     n = 0
     For i = LBound(Liste) To UBound(Liste)
       If UCase(Liste(i, 2)) Like clé Then
         n = n + 1: ReDim Preserve b(1 To ncol, 1 To n)
         For k = 1 To ncol:  b(k, n) = Liste(i, k): Next k
       End If
      Next i
      If n > 0 Then
        ReDim Preserve b(1 To ncol, 1 To n + 1)
        Me.ComboBox1.List = Application.Transpose(b)
        Me.ComboBox1.RemoveItem n
      End If
      Me.ComboBox1.DropDown
  Else
     Me.TextBox1 = Me.ComboBox1.Column(1)
     Me.TextBox2 = Me.ComboBox1.Column(2)
  End If
End Sub

JB

Cliquez pour agrandir...