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
| Option Explicit
'Définition des variables
Dim TV As Variant 'déclare la variable TV (Tableau de Variables)
Dim VT As Variant
Dim COL As Byte 'déclare la variable COL (COLonne)
Dim Ws As Worksheet
Dim x As Byte
Dim Nof As String
Dim valeurR As Single
Option Base 1
Const F = "F"
Const Fs = "Fs"
Const En = "En"
Const Co = "Co"
Private Sub Frame2_Click()
End Sub
Private Sub ListBox1_Click()
End Sub
Sub largeur_colonnes()
End Sub
Private Sub TextBox2_Change()
End Sub
Private Sub UserForm_Initialize()
ListBox1.ColumnCount = 9
End Sub
Private Sub CommandButton1_Click()
tri F
End Sub
Private Sub CommandButton2_Click()
tri Fs
End Sub
Private Sub CommandButton3_Click()
tri En
End Sub
Private Sub CommandButton4_Click()
tri Co
End Sub
Private Sub CommandButton5_Click()
tri
End Sub
Sub tri(Optional Mode = "tous")
'Gestion de la taille des colonnes
Set Ws = Worksheets("Feuil3")
Dim param As String
param = Columns(3).Width
For x = 2 To 9
param = param & ";" & Columns(x).Width
Next
ListBox1.ColumnWidths = param
'Fonction Tri
Dim tablo, tabfinal(), inc As Single, fk As Single, z As Single, a As Single
tablo = Ws.Range("a2:i" & Ws.Cells(Rows.Count, 1).End(xlUp).Row) 'on met aussi en mémoire un tableau a 5 colonne (avec tout)
ReDim tabfinal(UBound(tablo), 9)
For inc = 1 To UBound(tablo)
fk = plusgrand(tablo)
Select Case Mode
Case "F": If tablo(fk, 1) = "Ver" Then a = a + 1: For z = 1 To 9: tabfinal(a, z) = Format(tablo(fk, z)): Next
Case "Fs": If tablo(fk, 1) = "Ens" Then a = a + 1: For z = 1 To 9: tabfinal(a, z) = Format(tablo(fk, z)): Next
Case "En": If tablo(fk, 1) = "Sec" Then a = a + 1: For z = 1 To 9: tabfinal(a, z) = Format(tablo(fk, z)): Next
Case "Co": If tablo(fk, 1) = "Conce" Then a = a + 1: For z = 1 To 9: tabfinal(a, z) = Format(tablo(fk, z)): Next
Case "tous": a = a + 1: For z = 1 To 9: tabfinal(a, z) = Format(tablo(fk, z)): Next
End Select
tablo(fk, 9) = 0
If a = 10 Then Exit For
Next
ListBox1.List = tabfinal
End Sub
Function plusgrand(tabl) As Long
Dim indexo As String, a As Long, lig As Long
indexo = 0
For a = 1 To UBound(tabl)
If tabl(a, 9) > indexo Then
indexo = tabl(a, 9)
lig = a
End If
Next
plusgrand = lig
End Function
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim Nm As String 'déclare la variable Nom
Dim rngData As Range, rngLabelRow As Range, rngLabelColumn As Range, fn As WorksheetFunction
With Me.ListBox1 'prend en compte la ListBox1
Nm = .Column(1, .ListIndex) 'définit la variable Nom
End With 'fin de la prise en compte de la ListBox1
Set fn = Application.WorksheetFunction
With ThisWorkbook1.Worksheets("Feuil3")
Set rngData = .Range("B2:N73")
Set rngLabelRow = .Range("B2:B73")
Set rngLabelColumn = .Range("B1:N1")
End With
With fn
valeurR = .Index(rngData, .Match(Nm, rngLabelRow, 0), .Match("Crit4", rngLabelColumn, 0))
TextBox1.Text = valeurR
End With
End Sub |
Partager