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
| Private Sub ComboBox1_Change()
'Filtre selon le choix de la comboBox1
With Worksheets(1)
.Range("F1:F500").AutoFilter Field:=6, Criteria1:=ComboBox1.Value
End With
End Sub
Private Sub ComboBox1_DropButtonClick()
'Retire les doublons de la comboBox1
Dim f As Worksheet
Dim c As Range
Set f = Sheets("Reperes")
Set dico = CreateObject("Scripting.Dictionary")
For Each c In f.Range("f3:f" & f.[f65000].End(xlUp).Row)
If Rows(c.Row).Hidden = False Then
dico(c.Value) = IIf(dico.exists(c.Value), dico(c.Value) & "*" & c.Offset(, 1), c.Offset(, 1))
End If
Next c
Me.ComboBox1.List = dico.keys
'Tri le contenu du ComboBox par ordre alphabétique
With Me.ComboBox1
For i = 0 To .ListCount - 1
For j = 0 To .ListCount - 1
If .List(i) < .List(j) Then
strTemp = .List(i)
.List(i) = .List(j)
.List(j) = strTemp
End If
Next j
Next i
End With
End Sub
Private Sub CommandButton1_Click()
'Filtre colonne repere
With Worksheets(1)
.Range("E1:E500").AutoFilter Field:=5, Criteria1:=TextBoxRep.Value, Operator:=xlOr, _
Criteria2:="=*" & TextBoxRep.Value & "*"
End With
'Filtre colonne designation
With Worksheets(1)
.Range("F1:F500").AutoFilter Field:=6, Criteria1:=TextBoxDesg.Value, Operator:=xlOr, _
Criteria2:="=*" & TextBoxDesg.Value & "*"
End With
'Rempli la combobox suivant le filtre
Set Rng = [_FilterDataBase]
Dim Tmp(): ReDim Tmp(1 To [_FilterDataBase].Resize(, 1).SpecialCells(xlCellTypeVisible).Count)
For Each c In [_FilterDataBase].Resize(, 1).Offset(1).SpecialCells(xlCellTypeVisible)
i = i + 1: Tmp(i) = c.Row - Rng.Row + 1
Next c
Me.ComboBox1.List = Application.Index(Rng, Application.Transpose(Tmp), Application.Transpose(Evaluate("Row(6:" & Rng.Columns.Count & ")")))
'L'index 0 correspond à la première donnée contenue dans le ComboBox
'ComboBox1.ListIndex = 0
End Sub
Private Sub CommandButtonRaz_Click()
'Vide les textbox Reperes et Designation
TextBoxRep.Value = ""
TextBoxDesg.Value = ""
ComboBox1.Clear
'Filtre_raz Macro
With Worksheets(1)
.AutoFilterMode = False
Columns("A:N").Select
Selection.AutoFilter
End With
End Sub
Private Sub Frame_agents_Click()
'Creer autant de checkbox que de cellules non vides colonne C
nb_agents = Application.WorksheetFunction.CountA(Feuil1.Range("$C:$C").AutoFilter(Field:=6, Criteria1:=ComboBox1.Value))
For i = 1 To nb_agents
Set ajoutagent = Frame_agents.Controls.Add("forms.checkbox.1", Name:="ajoutagent" & i) ' partie la plus importante
Frame_agents.Controls("ajoutagent" & i).Top = 15 * i
Frame_agents.Controls("ajoutagent" & i).Caption = Feuil1.Range("c" & i)
Next i
If nb_agents > 8 Then
Frame_agents.Height = 135
Frame_agents.Width = 155
Frame_agents.ScrollBars = fmScrollBarsVertical
Frame_agents.ScrollHeight = (16 * nb_agents) + 20
Else
Frame_agents.Height = 135
Frame_agents.Width = 150
Frame_agents.ScrollBars = fmScrollBarsNone
End If
End Sub
Private Sub UserForm_Initialize()
With Worksheets(1)
.AutoFilterMode = False
Columns("A:N").Select
Selection.AutoFilter
ComboBox1.Clear
End With
End Sub |
Partager