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
| 'Private Sub BDD_BeforeLabelEdit(Cancel As Integer)
Private Sub UserForm_Initialize()
With GENERAL
.Top = 0
.Left = 0
.Height = Application.UsableHeight
.Width = Application.UsableWidth
End With
Set F = Sheets("BASE EMPLOI")
With Me.BDD
Entetes = Array("B", "C", "D", "E", "G", "H", "I", "J", "K", "L", "N", "O", "P", "Q", "R", "T", "U", "V", "W", "X", "Y", "AA", "AB", "AC", "AD", "AF", "AG", "AH", "AI", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB")
largeur = Array(80, 80, 80, 70, 70, 70, 80, 80, 80, 70, 70, 70, 80, 80, 80, 70, 70, 70, 80, 80, 80, 70, 70, 70, 80, 80, 80, 70, 70, 70, 80, 80, 80, 70, 70, 70, 80, 80, 80, 70, 70, 70, 80, 80, 80, 70, 70, 70)
With .ColumnHeaders
.Clear
nbr = -1
For nbr = 0 To 47
.Add , , F.Cells(1, Entetes(nbr)), largeur(nbr) '80
Next
End With
.View = 3 ' type Report
.Gridlines = True ' affichage de lignes
.FullRowSelect = True ' sélection complète de la ligne
.HideColumnHeaders = False ' afficher les en-têtes de colonnes
.LabelEdit = 0 ' Autoriser la saisie
'.LabelEdit = 1 ' N'autoriser la saisie
End With
Call LISTING
End Sub
Sub LISTING()
'Remplit la Listview avec les données d'Excel
BDD.ListItems.Clear
Set F = Sheets("BASE EMPLOI")
Entetes = Array("B", "C", "D", "E", "G", "H", "I", "J", "K", "L", "N", "O", "P", "Q", "R", "T", "U", "V", "W", "X", "Y", "AA", "AB", "AC", "AD", "AF", "AG", "AH", "AI", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB")
Set Plage = F.Range("b2:b" & F.Range("b65000").End(xlUp).Row)
For Each Cel In Plage
With BDD
.ListItems.Add , , Cel
For nbr = 1 To 47
.ListItems(.ListItems.Count).ListSubItems.Add , , F.Cells(Cel.Row, Entetes(nbr)) 'Cel.Offset(0, 1)
Next
End With
Next
End Sub
Private Sub BDD_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
'Permet le classement par clic sur le titre de la colonne
BDD.Sorted = False
BDD.SortKey = ColumnHeader.Index - 1
If BDD.SortOrder = lvwAscending Then
BDD.SortOrder = lvwDescending
Else
BDD.SortOrder = lvwAscending
End If
BDD.Sorted = True
End Sub
Private Sub MODIFICATIONS_Click()
Dim i As Integer, j As Integer
'Boucle sur toutes les lignes
For i = 1 To BDD.ListItems.Count
Cells(i, 1) = BDD.ListItems(i).Text
'Boucle sur les colonnes
For j = 1 To BDD.ColumnHeaders.Count - 1
Cells(i, j + 1) = BDD.ListItems(i).ListSubItems(j).Text
Next j
Next i
End Sub |
