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
|
Dim f, choix(), Rng, Ncol, Nlig, TC()
Private Sub UserForm_Initialize()
Set f = Sheets("recapitulatif")
Set Rng = f.Range("A3:R" & f.[a65000].End(xlUp).Row)
TblTmp = Rng.Value
For i = LBound(TblTmp) To UBound(TblTmp)
TblTmp(i, 9) = TblTmp(i, 9) & ""
Next i
TC = TblTmp
Ncol = Rng.Columns.Count
Nlig = Rng.Rows.Count
For i = LBound(TblTmp) To UBound(TblTmp)
ReDim Preserve choix(1 To i)
For k = LBound(TblTmp) To UBound(TblTmp, 2)
choix(i) = choix(i) & TblTmp(i, k) & " * "
Next k
Next i
Me.ListBox1.List = TblTmp
End Sub
Private Sub TextBox1_Change()
If Me.TextBox1 <> "" Then
mots = Split(Trim(Me.TextBox1), " ")
Tbl = choix
For i = LBound(mots) To UBound(mots)
Tbl = Filter(Tbl, mots(i), True, vbTextCompare)
Next i
n = 0: Dim b()
For i = LBound(Tbl) To UBound(Tbl)
a = Split(Tbl(i), "*")
n = n + 1: ReDim Preserve b(1 To Ncol, 1 To n)
For k = 1 To Ncol
b(k, i + 1) = a(k - 1)
If k = 9 Then b(k, i + 1) = b(k, i + 1) & " "
Next k
Next i
If n > 0 Then
ReDim Preserve b(1 To Ncol, 1 To n + 1)
Me.ListBox1.List = Application.Transpose(b)
Me.ListBox1.RemoveItem n
End If
Me.Label1.Caption = UBound(Tbl) + 1
Else
UserForm_Initialize
End If
End Sub
Private Sub CommandButton1_Click()
Set O = Sheets("recapitulatif")
For i = 0 To Me.ListBox1.ListCount - 1 'Boucle 1 : sur toutes les lignes de la ListBox1
If Me.ListBox1.Selected(i) = True Then 'condition 1 : si la ligne est sélectionnée
For J = 1 To Nlig 'boucle 2 : sur toutes les lignes du tabelau TC
If TC(J, 2) = Val(Me.ListBox1.Column(1, i)) Then 'condition 2 : si les numéros de HYD sont égaux
O.Rows(J + 2).Select 'sélecionne la ligne J de l'onglet O
LeParcours = Trim(Me.ListBox1.Column(1, i))
Chemin = "C:/Users/emmanuel-baigne/dropbox/HYD/HYD" & LeParcours & "\"
Application.DisplayFullScreen = False
ThisWorkbook.FollowHyperlink Chemin
Unload Me
Exit Sub 'sort de la procédure
End If 'fin de la condition 2
Next J 'prochaine ligne de la boucle 2
End If 'fin de la condition 1
Next i 'prochaine ligne de la boucle 1
End Sub |
Partager