XL 2013 Masquer/Afficher colonne selon valeur cellule

CGU2022.

XLDnaute Junior
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
 
Solution
Bonsoir @CGU2022. :)
Pas certain d'avoir tout compris:
VB:
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

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonsoir @CGU2022. :)
Pas certain d'avoir tout compris:
VB:
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.

XLDnaute Junior
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

XLDnaute Barbatruc
Supporter XLD
Re,

Essayez ce code:
VB:
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
 

mapomme

XLDnaute Barbatruc
Supporter XLD
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":
VB:
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
 

Pièces jointes

  • CGU2022.- Masquer Afficher colonnes- v1.xlsm
    65.1 KB · Affichages: 1

CGU2022.

XLDnaute Junior
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":
VB:
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...
👍
 

Discussions similaires

Statistiques des forums

Discussions
311 720
Messages
2 081 889
Membres
101 831
dernier inscrit
gillec