Bonjour,

J'ai un USF avec 2 Combox qui alimente chacun deux textbox.

Question 1 : le conbobox2 affiche bien la bonne colonne mais pas
le textbox3 et 4.

Question 2 : comment de ne pas afficher les lignes en blancs dans la combobox

Voici le code :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
58
59
60
61
62
63
64
65
66
Option Explicit
Private Sub ComboBox1_Change()
'TextBox1 = Sheets("DATA").Cells(ComboBox1.ListIndex + 2, 1)
Dim C As Range
Dim D As Range
 
With Sheets("DATA").Select
    Set C = Range("D1:D" & Range("D65536").End(xlUp).Row).Find(ComboBox1.Value)
        If Not C Is Nothing Then
        On Error Resume Next
        TextBox1.Value = C.Offset(0, -3).Value
        TextBox2.Value = C.Offset(0, -2).Value
          End If
          End With
    With Sheets("DATA").Select
        Set D = Range("F1:F" & Range("E65536").End(xlUp).Row).Find(ComboBox2.Value)
        If Not D Is Nothing Then
        On Error Resume Next
        TextBox3.Value = D.Offset(0, -1).Value
        TextBox4.Value = D.Offset(0, 1).Value
 
        End If
End With
 
End Sub
 
Private Sub Annuler_Click()
Dim C As Control
For Each C In Me.Controls
  On Error Resume Next
  C.Value = ""
Next C
'Unload Me
End Sub
 
Private Sub UserForm_Initialize()
Dim i As Integer, j As Integer, Tablo, Tablo1, temp, temp1
 
With Sheets("DATA")
Tablo = .Range("D2:D" & .Range("D65536").End(xlUp).Row)
End With
For i = 1 To UBound(Tablo, 1) - 1
    For j = i + 1 To UBound(Tablo, 1)
        If Tablo(j, 1) < Tablo(i, 1) Then
        temp = Tablo(i, 1)
        Tablo(i, 1) = Tablo(j, 1)
        Tablo(j, 1) = temp
        End If
    Next j
Next i
ComboBox1.List = Tablo
 
With Sheets("DATA")
Tablo1 = .Range("F2:F33")
End With
For i = 1 To UBound(Tablo1, 1) - 1
    For j = i + 1 To UBound(Tablo1, 1)
        If Tablo1(j, 1) < Tablo1(i, 1) Then
        temp1 = Tablo1(i, 1)
        Tablo1(i, 1) = Tablo1(j, 1)
        Tablo1(j, 1) = temp1
        End If
    Next j
Next i
ComboBox2.List = Tablo1
End Sub