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
| Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorEnd
Dim wksDb As Worksheet 'Feuille avec les données
Dim ListOfMonth As Variant 'Liste des mois
Dim Lett As Variant 'liste pour capter la lettre de la dernière colonne a cacher
Dim cmt As Integer 'compteur pour le module
Dim LC As String 'Lettre de la dernière colonne à masquer
If Target.Address = "$D$29" Then
Set wksDb = ThisWorkbook.Worksheets("DataBase") 'feuille avec les données reliées aux graphiques
wksDb.Range("B:M").EntireColumn.Hidden = False
ListOfMonth = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
Lett = Array("C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "ZZ")
LC = "ZZ"
For cmt = 0 To 11
If ListOfMonth(cmt) = Target.Value Then
LC = Lett(cmt)
Exit For
End If
Next cmt
If LC = "ZZ" Then GoTo ErrorEnd
wksDb.Range(LC & ":M").EntireColumn.Hidden = True
ElseIf Target.Address = "$D$30" Then
Set wksDb = ThisWorkbook.Worksheets("DataBase")
wksDb.Range("P:AA").EntireColumn.Hidden = False
If Target.Value = "Full Year" Then Exit Sub
ListOfMonth = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
Lett = Array("Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "ZZ")
LC = "ZZ"
For cmt = 0 To 11
If ListOfMonth(cmt) = Target.Offset(-1, 0).Value Then
LC = Lett(cmt)
Exit For
End If
Next cmt
If LC = "ZZ" Then GoTo ErrorEnd
wksDb.Range(LC & ":AA").EntireColumn.Hidden = True
End If
ErrorEnd:
End Sub |
Partager