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 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110
| '''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''Ranger les résultats par ordre alphabétique'''''
Sub TrierListMultiColonne(LstBx As ListBox)
Dim i As Integer, B As Boolean, e As Integer
Dim TB
Reco:
With LstBx
For i = 0 To LstBx.ListCount - 2
If .List(i) > .List(i + 1) Then
For e = 0 To .ColumnCount - 1
TB = .List(i, e)
.List(i, e) = .List(i + 1, e)
.List(i + 1, e) = TB
Next e
B = True
End If
Next i
If B Then B = False: GoTo Reco
End With
End Sub
Private Sub CommandButton1_Click()
Dim TB
Dim lig As Long, derlig As Long, i As Integer, j As Integer
Label10.Visible = False 'cacher le commentaire
TB = Array(CB_Groupe, CB_Style, CB_region, CB_Dep, CB_Ville, CB_Pop) 'créer une liste d'élément
Lt_result.Clear 'vider la listbox de résultat
With Sheets("Feuil1")
derlig = .Cells(65536, 1).End(xlUp).Row 'créer une collection de cellules
For lig = 5 To derlig 'on boucle de la ligne 3 à la fin
For i = 0 To UBound(TB)
If TB(i) <> "Tous" Then
If TB(i) <> .Cells(lig, i + 1) Then Exit For
End If
Next i
If i > UBound(TB) Then
'Mettre la ligne trouvée dans la listBox
Lt_result.AddItem
For i = 0 To 5:
Lt_result.List(Lt_result.ListCount - 1, i) = .Cells(lig, i + 1)
Next i
End If
Next lig
End With
'TrierListMultiColonne Lt_result
If Lt_result.ListCount = 0 Then Label10.Visible = True
End Sub
Private Sub CommandButton2_Click()
uf_saisie.Show
End Sub
Private Sub userform_initialize()
Dim i As Integer, B As Boolean, Lg As String, L As Integer
Dim lig As Long, derlig As Long
'Positionne le titre
lbl_titre.Move 0, 0, Me.Width
'Largeur colonne Lt_result
L = (Lt_result.Width / 6) - 2
For i = 0 To 6: Lg = Lg & CStr(L) & ";": Next i
Lt_result.ColumnWidths = Lg
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''Remplir les combobox'''''''''''''''''''''
With Sheets("Feuil1")
derlig = .Cells(65536, 1).End(xlUp).Row
For lig = 3 To derlig
CB_Groupe.AddItem .Cells(lig, 1)
If .Cells(lig, 3) <> "" Then
CB_region.AddItem .Cells(lig, 3)
End If
If .Cells(lig, 4) <> "" Then
CB_Dep.AddItem .Cells(lig, 4)
End If
If .Cells(lig, 5) <> "" Then
CB_Ville.AddItem .Cells(lig, 5)
End If
Next lig
End With
With Sheets("style")
i = 4
While .Cells(i, 2) <> ""
CB_Style.AddItem .Cells(i, 2)
i = i + 1
Wend
i = 4
While .Cells(i, 4) <> ""
CB_Pop.AddItem .Cells(i, 4)
i = i + 1
Wend
End With
''''''''''''''''''''''''''''''''''''''''''''''''''''''' |
Partager