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 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217
| Option Explicit
Private ListeDesPages As Variant
Private I As Long
Private PremiereLigneK As Long
Private DerniereLigneK As Long
Private ColonneK As Long
Private Cellule As Range
Private Sh As Worksheet
Private Continuer As Boolean
Private MotDePasseFeuilles As String
Sub CacherDeCacherLesLignes(NatureOperation As String, PremiereLigneACacher As Long, DerniereLigneACacher As Long, MotDePasse As String)
ColonneK = 11
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
ListeDesPages = Array("PageDeGarde", "Total", "Réactifs", "Calibrateurs", "Contrôles")
For I = 1 To 5
Continuer = False
For Each Sh In Sheets
Select Case Sh.Name
Case ListeDesPages(I - 1)
Continuer = True
End Select
Next Sh
If Continuer = True And NatureOperation = "Décacher" Then
With Sheets(ListeDesPages(I - 1))
.Activate
.Unprotect Password:=MotDePasse
.Range(Cells(PremiereLigneACacher, ColonneK), Cells(DerniereLigneACacher, ColonneK)).EntireRow.Hidden = False
.Protect Password:=MotDePasse
End With
End If
If Continuer = True And NatureOperation = "Cacher" Then
With Sheets(ListeDesPages(I - 1))
.Activate
.Unprotect Password:=MotDePasse
End With
DerniereLigneK = Cells(ActiveSheet.Rows.Count, ColonneK).End(xlUp).Row
Range(Cells(PremiereLigneACacher, ColonneK), Cells(DerniereLigneACacher, ColonneK)).Select
For Each Cellule In Selection
Select Case Cellule
Case "---"
Cellule.EntireRow.Hidden = True
End Select
Next Cellule
ActiveSheet.Protect Password:=MotDePasse
End If
Next I
Sheets("PageDeGarde").Activate
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub
Sub TestCacherLesLignes()
PremiereLigneK = 12
DerniereLigneK = 100
MotDePasseFeuilles = "Toto"
Call CacherDeCacherLesLignes("Cacher", PremiereLigneK, DerniereLigneK, MotDePasseFeuilles)
MsgBox ("Fin de programme !")
End Sub
Sub TestDeCacherLesLignes()
PremiereLigneK = 12
DerniereLigneK = 100
MotDePasseFeuilles = "Toto"
Call CacherDeCacherLesLignes("Décacher", PremiereLigneK, DerniereLigneK, MotDePasseFeuilles)
MsgBox ("Fin de programme !")
End Sub
Sub GenererLesFeuilles()
ListeDesPages = Array("PageDeGarde", "Total", "Réactifs", "Calibrateurs", "Contrôles")
For I = 1 To 5
Continuer = True
For Each Sh In Sheets
Select Case Sh.Name
Case ListeDesPages(I - 1)
MsgBox (Sh.Name)
Continuer = False
End Select
Next Sh
If Continuer = True Then
Sheets.Add
ActiveSheet.Name = ListeDesPages(I - 1)
End If
Next I
End Sub
Sub DeprotegerLesFeuilles()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
ListeDesPages = Array("PageDeGarde", "Total", "Réactifs", "Calibrateurs", "Contrôles")
MotDePasseFeuilles = "Toto"
For I = 1 To 5
Continuer = True
For Each Sh In Sheets
Select Case Sh.Name
Case ListeDesPages(I - 1)
Sh.Activate
Sh.Unprotect Password:=MotDePasseFeuilles
Continuer = False
End Select
Next Sh
Next I
Sheets("PageDeGarde").Activate
ActiveSheet.Shapes.Range(Array("BoutonVerrouiller", "BoutonDecacher", _
"BoutonCacherLesLignes", "BoutonDeverrouiller")).Visible = True
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub
Sub ProtegerLesFeuilles()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
ListeDesPages = Array("PageDeGarde", "Total", "Réactifs", "Calibrateurs", "Contrôles")
MotDePasseFeuilles = "Toto"
For I = 1 To 5
Continuer = True
For Each Sh In Sheets
Select Case Sh.Name
Case ListeDesPages(I - 1)
Sh.Activate
If Sh.Name = "PageDeGarde" Then
ActiveSheet.Shapes.Range(Array("BoutonVerrouiller", "BoutonDecacher", _
"BoutonCacherLesLignes", "BoutonDeverrouiller")).Visible = False
End If
Sh.Protect Password:=MotDePasseFeuilles
End Select
Next Sh
Next I
Sheets("PageDeGarde").Activate
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub |
Partager