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
| Option Explicit
Dim Init As Boolean
Private Sub ComboBox1_Change()
' Type structure
If Init = True Then Exit Sub
Cl(1) = 11
Ch(1) = Me.ComboBox1
Recherche
End Sub
Private Sub ComboBox2_Change()
'ville
If Init = True Then Exit Sub
Cl(2) = IIf(Me.ComboBox2.ListIndex = -1, 255, 15 + Me.ComboBox2.ListIndex)
Ch(2) = IIf(Me.ComboBox2.ListIndex = -1, "", "X")
Recherche
End Sub
Private Sub ComboBox3_Change()
'Fonction
If Init = True Then Exit Sub
Cl(3) = IIf(Me.ComboBox3.ListIndex = -1, 255, 20 + Me.ComboBox3.ListIndex)
Ch(3) = IIf(Me.ComboBox3.ListIndex = -1, "", "X")
Recherche
End Sub
Private Sub ComboBox4_Change()
'Service
If Init = True Then Exit Sub
Cl(4) = IIf(Me.ComboBox3.ListIndex = -1, 255, 20 + Me.ComboBox3.ListIndex)
Ch(4) = IIf(Me.ComboBox3.ListIndex = -1, "", "X")
Recherche
End Sub
Private Sub ComboBox5_Change()
'Reseaux
If Init = True Then Exit Sub
Cl(5) = IIf(Me.ComboBox3.ListIndex = -1, 255, 20 + Me.ComboBox3.ListIndex)
Ch(5) = IIf(Me.ComboBox3.ListIndex = -1, "", "X")
Recherche
End Sub
Private Sub CommandButton1_Click()
End
End Sub
Private Sub UserForm_Initialize()
Dim I As Integer
Dim Lg As Long
Dim Ws As Worksheet
Set Ws = Sheets("Listes")
Init = True
For I = 1 To 12
Me.Controls("Label" & I) = Ws.Cells(1, I)
With Me.Controls("ComboBox" & I)
.Clear
For Lg = 2 To Ws.Cells(Rows.Count, I).End(xlUp).Row
.Value = Ws.Cells(Lg, I)
If .ListIndex = -1 Then
.AddItem Ws.Cells(Lg, I)
End If
Next Lg
.ListIndex = -1
End With
Next I
Set Ws = Sheets("BDD")
With Me.ListView1
'Titres des colonnes
With .ColumnHeaders
.Clear
'Ajout des colonnes
.Add , , "Nom", 95
.Add , , "Prenom", 55
.Add , , "Genre", 40
.Add , , "Fonction", 95
.Add , , "Adresse", 95
.Add , , "Code postal", 55
.Add , , "Ville", 75
.Add , , "Telephone perso", 75
.Add , , "Email_perso", 100
.Add , , "Nom Structure", 75
.Add , , "Type structure", 75
.Add , , "Adresse pro", 75
.Add , , "Dep", 30
.Add , , "Code postal", 55
.Add , , "Ville pro", 75
.Add , , "Num pro", 75
.Add , , "Num fax", 75
.Add , , "Email pro", 100
.Add , , "Diffusion catalogue", 75
.Add , , "DSI", 75
.Add , , "DSI thematique", 75
.Add , , "Dispositif", 75
.Add , , "Statut", 75
.Add , , "Participation formation", 75
.Add , , "Usager CDR", 75
.Add , , "Professionnel", 75
.Add , , "Particulier", 75
.Add , , "Adherent", 75
.Add , , "Actualiser", 75
.Add , , "Service", 75
.Add , , "Réseau", 75
End With
.View = lvwReport ' Affichage en mode Rapport
.Gridlines = True ' Affichage d'un quadrillage
.FullRowSelect = True ' Sélection des lignes complètes
.LabelEdit = lvwManual ' Sélection des lignes complètes
End With
For I = 1 To 12
Cl(I) = 255
Ch(I) = ""
Next I
Recherche
Init = False
End Sub |
Partager