XL 2013 Masquer/Afficher colonne selon valeur cellule

[CGU2022.]

Bonjour à toutes et a tous.

j'ai une macro qui affiche les colonnes ("c6:bz6") en fonction d'une valeur de la cellule A2.
Si cette valeur est trouvée dans("c6:bz6") seul les colonnes avec la valeur de la cellule A2 sont affichées.

la valeur de la cellule A2 dépend de la comobox2.

Tous marche bien mais
Je souhaiterai par exemple si la valeur de la cellule A2="Toto" aucune colonne ne soit masquée.


Private Sub ComboBox2_Change()
Application.ScreenUpdating = 0

Columns("c:zz").EntireColumn.Hidden = False

For Each cell In ActiveSheet.Range("c6:bz6"):
If cell.Value <> Range("A2").Value Then
Columns(cell.Column).Select

Selection.EntireColumn.Hidden = True
End If
Next cell

End Sub


merci

 

[mapomme]

Bonsoir @CGU2022.
Pas certain d'avoir tout compris:

Private Sub ComboBox2_Change()
Dim xref, cell
   Application.ScreenUpdating = 0
   With ActiveSheet
      .Range("c6:bz6").EntireColumn.Hidden = False
      xref = .Range("A2")
      If xref = "toto" Then Exit Sub
      For Each cell In .Range("c6:bz6"): cell.EntireColumn.Hidden = cell <> xref: Next
   End With
End Sub

 

[CGU2022.]

Bonsoir @CGU2022.
Pas certain d'avoir tout compris:

Private Sub ComboBox2_Change()
Dim xref, cell
   Application.ScreenUpdating = 0
   With ActiveSheet
      .Range("c6:bz6").EntireColumn.Hidden = False
      xref = .Range("A2")
      If xref = "toto" Then Exit Sub
      For Each cell In .Range("c6:bz6"): cell.EntireColumn.Hidden = cell <> xref: Next
   End With
End Sub

Bonjour mapomme.
Super....

 

[CGU2022.]

Comment peut on faire pour articuler ensemble ces 2 macros?
sensiblement identiques.

il faudrait compiler ces deux lignes qu'elles soient dépendantes l'une de l'autre
With ActiveSheet
.Range("c7:bz7").EntireColumn.Hidden = False
xref = .Range("A1")
.Range("c6:bz6").EntireColumn.Hidden = False
xref = .Range("A2")

et idem je pense pour celles ci



Private Sub ComboBox1_Change()
Dim xref, cell
Application.ScreenUpdating = 0

With ActiveSheet
.Range("c7:bz7").EntireColumn.Hidden = False
xref = .Range("A1")
If xref = "Toutes les communes" Then Exit Sub
For Each cell In .Range("c7:bz7"): cell.EntireColumn.Hidden = cell <> xref: Next
End With

End Sub

Private Sub ComboBox2_Change()
Dim xref, cell
Application.ScreenUpdating = 0

With ActiveSheet
.Range("c6:bz6").EntireColumn.Hidden = False
xref = .Range("A2")
If xref = "Toutes" Then Exit Sub
For Each cell In .Range("c6:bz6"): cell.EntireColumn.Hidden = cell <> xref: Next
End With

End Sub

 

[mapomme]

Re,

Essayez ce code:

Option Explicit

Private Sub ComboBox1_Change()
   afficherMasquer
End Sub

Private Sub ComboBox2_Change()
   afficherMasquer
End Sub

Sub afficherMasquer()
Dim refA1, refA2, ref, x, cell
   Application.ScreenUpdating = False
   With ActiveSheet
      .Range("c7:bz7").EntireColumn.Hidden = False
      refA1 = .Range("a1"): If refA1 = "Toutes les communes" Then refA1 = "*"
      refA2 = .Range("a2"): If refA2 = "Toutes" Then refA2 = "*"
      If refA1 = "*" And refA2 = "*" Then Exit Sub
      ref = refA1 & Chr(1) & refA2
      For Each cell In .Range("c7:bz7")
         x = cell & Chr(1) & cell.Offset(-1)
         cell.EntireColumn.Hidden = Not (x Like ref)
      Next cell
   End With
End Sub

 

[CGU2022.]

Bonjour
Ci joint un fichier test

 

[mapomme]

Re ,

La feuille "Op" était protégée! Encore eût-il fallu que je l’eusse su
Voir le code dans le module de la Feuille "Op":

Option Explicit

Private Sub ComboBox1_Change()
   afficherMasquer
End Sub

Private Sub ComboBox2_Change()
   afficherMasquer
End Sub

Sub afficherMasquer()
Dim refA1, refA2, ref, x, cell
   Application.ScreenUpdating = False
   With ActiveSheet
      .Unprotect
      .Range("c7:bz7").EntireColumn.Hidden = False
      refA1 = .Range("a1"): If refA1 = "Toutes les communes" Then refA1 = "*"
      refA2 = .Range("a2"): If refA2 = "Toutes" Then refA2 = "*"
      If refA1 = "*" And refA2 = "*" Then .Protect: Exit Sub
      ref = refA1 & Chr(1) & refA2
      For Each cell In .Range("c7:bz7")
         x = cell & Chr(1) & cell.Offset(-1)
         cell.EntireColumn.Hidden = Not (x Like ref)
      Next cell
      .Protect
   End With
End Sub

 

[CGU2022.]

Re ,

La feuille "Op" était protégée! Encore eût-il fallu que je l’eusse su
Voir le code dans le module de la Feuille "Op":

Option Explicit

Private Sub ComboBox1_Change()
   afficherMasquer
End Sub

Private Sub ComboBox2_Change()
   afficherMasquer
End Sub

Sub afficherMasquer()
Dim refA1, refA2, ref, x, cell
   Application.ScreenUpdating = False
   With ActiveSheet
      .Unprotect
      .Range("c7:bz7").EntireColumn.Hidden = False
      refA1 = .Range("a1"): If refA1 = "Toutes les communes" Then refA1 = "*"
      refA2 = .Range("a2"): If refA2 = "Toutes" Then refA2 = "*"
      If refA1 = "*" And refA2 = "*" Then .Protect: Exit Sub
      ref = refA1 & Chr(1) & refA2
      For Each cell In .Range("c7:bz7")
         x = cell & Chr(1) & cell.Offset(-1)
         cell.EntireColumn.Hidden = Not (x Like ref)
      Next cell
      .Protect
   End With
End Sub

Excellent Mapomme...
Un grand merci...