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
|
Dim Plage As Range
Private Sub TextBox1_GotFocus()
'défini la plage
DefPlage
End Sub
Private Sub TextBox1_Change()
Dim Cel As Range
'charge la ListBox en fonction des lettres entrées
ListBox1.Clear
If TextBox1.Text = "" Then Exit Sub
For Each Cel In Plage
If UCase(Cel.Value) Like UCase(TextBox1.Text) & "*" Then
ListBox1.AddItem Cel.Value
End If
Next Cel
End Sub
Private Sub CommandButton1_Click()
Nouveau_candidat.Show
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim Cel As Range
'défini la plage
DefPlage
Set Cel = Plage.Find(ListBox1.Text, , xlValues, xlWhole)
If Not Cel Is Nothing Then
'charge la Form sans l'afficher
Load Nouveau_candidat
'rempli les champs
With Nouveau_candidat
.NOMTB = Cel.Value
.PRENOMTB = Cel.Offset(, 1).Value
.VILLETB = Cel.Offset(, 3).Value
.TELTB = Cel.Offset(, 4).Value
.QUALIF1 = Cel.Offset(, 5).Value
.QUALIF2 = Cel.Offset(, 6).Value
.QUALIF3 = Cel.Offset(, 7).Value
.CommentsTB = Cel.Offset(, 9).Value
'affiche
.Show
End With
End If
End Sub
Sub DefPlage()
'défini la plage sur la colonne A
With Worksheets("BDD")
Set Plage = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With
End Sub |
Partager