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 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129
| Option Explicit
Private IndexSelected As Integer
Private Tbl As ListObject
Private Sub UserForm_Initialize()
Set Tbl = Worksheets("Feuil1").ListObjects("Tableau1")
Call InitListView
Call FilterListView
Call LoadCombos
End Sub
Private Sub InitListView()
Dim rngCell As Range
With Me.ListView1
.Gridlines = True
.HideColumnHeaders = False
.View = lvwReport
.FullRowSelect = True
'Ajouter les en-têtes de colonnes
For Each rngCell In Tbl.HeaderRowRange.Cells
.ColumnHeaders.Add Text:=rngCell.Value, Width:=90
Next rngCell
.ColumnHeaders.Add Text:="row", Width:=20
.ColumnHeaders(1).Width = 40: .ColumnHeaders(1).Alignment = lvwColumnLeft
.ColumnHeaders(2).Width = 40: .ColumnHeaders(2).Alignment = lvwColumnCenter
.ColumnHeaders(3).Width = 40: .ColumnHeaders(3).Alignment = lvwColumnCenter
.ColumnHeaders(4).Width = 100: .ColumnHeaders(4).Alignment = lvwColumnCenter
.ColumnHeaders(5).Width = 100: .ColumnHeaders(5).Alignment = lvwColumnCenter
.ColumnHeaders(6).Width = 50: .ColumnHeaders(6).Alignment = lvwColumnCenter
.ListItems.Clear
End With
End Sub
Private Sub FilterListView()
Dim rngCell As Range
Dim LstItem As ListItem
Dim RowCount As Long, ColCount As Long
Dim i As Long, j As Long
With Tbl.Range
.AutoFilter Field:=1, Criteria1:=Me.ComboBox1
.AutoFilter Field:=2, Criteria1:=Me.ComboBox2
End With
Me.ListView1.ListItems.Clear
RowCount = Tbl.DataBodyRange.Rows.Count
ColCount = Tbl.DataBodyRange.Columns.Count
With Tbl.Range ' application d'un filtre auto
.AutoFilter Field:=1, Criteria1:=Me.ComboBox1
.AutoFilter Field:=2, Criteria1:=Me.ComboBox2
End With
With Tbl.DataBodyRange
'Remplir la Listview
For i = 1 To RowCount
If Not .Cells(i, 1).EntireRow.Hidden Then
Set LstItem = Me.ListView1.ListItems.Add(Text:=.Cells(i, 1).Value)
For j = 2 To ColCount
LstItem.ListSubItems.Add Text:=.Cells(i, j).Value
Next j
LstItem.ListSubItems.Add Text:=CStr(i)
End If
Next i
End With
Tbl.AutoFilter.ShowAllData ' suppression du filtre auto
End Sub
Private Sub ComboBox1_Change()
Call FilterListView
End Sub
Private Sub ComboBox2_Change()
Call FilterListView
End Sub
Private Sub CommandButton1_Click()
Tbl.DataBodyRange(IndexSelected, 4).Value = Me.TextBox1
Tbl.DataBodyRange(IndexSelected, 5).Value = Me.TextBox2
Call FilterListView
End Sub
Private Sub CommandButton2_Click()
UserForm1.Hide
End Sub
Private Sub ListView1_ItemClick(ByVal Item As MSComctlLib.ListItem)
Me.TextBox1.Value = Me.ListView1.ListItems(Item.Index).SubItems(3)
Me.TextBox2.Value = Me.ListView1.ListItems(Item.Index).SubItems(4)
IndexSelected = CInt(Me.ListView1.ListItems(Item.Index).SubItems(5))
End Sub
Private Sub LoadCombos()
Dim rngData As Range, rngCell As Range
Dim RowCount As Long, i As Long
Dim oDictionary1 As Object, oDictionary2 As Object
Set rngData = Tbl.DataBodyRange
Set oDictionary1 = CreateObject("Scripting.Dictionary")
Set oDictionary2 = CreateObject("Scripting.Dictionary")
RowCount = rngData.Rows.Count
For i = 1 To RowCount
If Not oDictionary1.exists(rngData(i, 1).Value) Then
oDictionary1.Add rngData(i, 1).Value, 0
Me.ComboBox1.AddItem rngData(i, 1).Value
End If
If Not oDictionary2.exists(rngData(i, 2).Value) Then
oDictionary2.Add rngData(i, 2).Value, 0
Me.ComboBox2.AddItem rngData(i, 2).Value
End If
Next i
End Sub |
Partager