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
| Private Sub btn_localisation_Click() 'Recherche par localisation
On Error GoTo 1
Sheets("Base").Activate
ActiveSheet.Range("$G$3:$G$1000").AutoFilter Field:=7, Criteria1:=Me.TextBox_localisation.Value
1
End Sub
Private Sub btn_Reset_Click() 'Reset Filtre
On Error GoTo 1
TextBox6.Value = ""
TextBox_localisation.Value = ""
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
TextBox5 = ""
TextBox8 = ""
TextBox9 = ""
TextBox10 = ""
Worksheets("Base").ShowAllData
1
End Sub
Private Sub btn_type_Click() 'Recherche par Type
On Error GoTo 1
Sheets("Base").Activate
ActiveSheet.Range("$D$3:$D$1000").AutoFilter Field:=4, Criteria1:=TextBox_type.Value
1
End Sub
Private Sub btnFermer_Click() 'Fermer programme
Unload Me
End Sub
Private Sub ComboBox1_Change()
End Sub
Private Sub CommandButton1_Click() 'Filtrer liste par DN
On Error GoTo 1
Sheets("Base").Activate
ActiveSheet.Range("$B$3:$B$1000").AutoFilter Field:=2, Criteria1:=Me.TextBox6.Value
'compte le nombre de DN filtré
Dim NoLigne(), i, j
For Each ligne In Sheets("Base").AutoFilter.Range.SpecialCells(xlCellTypeVisible).Rows
i = i + 1
ReDim Preserve NoLigne(i)
NoLigne(i) = ligne.Address(RowAbsolute:=False, ColumnAbsolute:=False)
Next
For j = 1 To i
TextBox11 = NoLigne(j)
Next
1
End Sub
Private Sub CommandButton3_Click()
'Gestion des erreurs
On Error GoTo 1
'test si le numéro de vanne existe
If WorksheetFunction.CountIf(Sheets("Base").Range("A:A"), Me.TextBox1.Value) = 0 Then
' affiche un message qui indique que la vanne n'a pas était trouvé
MsgBox "Cette vanne n'éxiste pas. Veuillez saisir un nouveau TAG", vbInformation + vbOKOnly, "Vanne non trouvé"
End If
With Me 'avec le forumlaire:
'Applique la fonction RechercheV (Vlookup) pour afficher les informations de la vanne
.TextBox2 = Application.WorksheetFunction.VLookup(CStr(Me.TextBox1), Sheets("Base").Range("source"), 2, 0) 'affiche DN
.TextBox3 = Application.WorksheetFunction.VLookup(CStr(Me.TextBox1), Sheets("Base").Range("source"), 3, 0) 'affiche Etage
.TextBox4 = Application.WorksheetFunction.VLookup(CStr(Me.TextBox1), Sheets("Base").Range("source"), 4, 0) 'affiche Type
.TextBox5 = Application.WorksheetFunction.VLookup(CStr(Me.TextBox1), Sheets("Base").Range("source"), 5, 0) 'affiche Périodicté Préventive
.TextBox8 = Application.WorksheetFunction.VLookup(CStr(Me.TextBox1), Sheets("Base").Range("source"), 7, 0) 'affiche la Localisation
.TextBox9 = Application.WorksheetFunction.VLookup(CStr(Me.TextBox1), Sheets("Base").Range("source"), 8, 0) 'affiche Code SAP
.TextBox10 = Application.WorksheetFunction.VLookup(CStr(Me.TextBox1), Sheets("Base").Range("source"), 6, 0) 'affiche Description Equipement
End With
1
End Sub
Private Sub CommandButton4_Click() 'Bouton active filtre
If CommandButton4.BackColor = &H8000000F Then CommandButton4.BackColor = RGB(0, 255, 0) Else If CommandButton5.BackColor = RGB(255, 0, 0) Then CommandButton5.BackColor = &H8000000F Else CommandButton5.BackColor = &H8000000F
ActiveSheet.Range("$A$3:$H$1000").AutoFilter Field:=2, Criteria1:=Me.TextBox6.Value
Sheets("Base").ShowAllData
End Sub
Private Sub CommandButton5_Click() 'bouton desactive filtre
If CommandButton5.BackColor = &H8000000F Then CommandButton5.BackColor = RGB(255, 0, 0) Else If CommandButton4.BackColor = RGB(0, 255, 0) Then CommandButton4.BackColor = &H8000000F Else CommandButton4.BackColor = &H8000000F
ActiveSheet.AutoFilterMode = False
TextBox6.Value = ""
End Sub |
