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 |
Partager