Private Sub Workbook_Open() Set bouton = Sheets(1).CommandButton1 etat = False For i = 2 To 7 voyons Sheets(i).Range("K4:K100"), "ici", bouton, False '====>>> remplkace ici par ta chaîne à toi Next If etat Then bouton.Caption "montrer" Else bouton.Caption "cacher" End Sub
Public atraiter As Range, bouton As Object, etat As Boolean Public Sub voyons(plage As Range, quoi As String, bouton As Object, how As Boolean) Dim i As Long, tablo Set atraiter = Nothing If WorksheetFunction.CountIf(plage, quoi) > 0 Then tablo = plage For i = 1 To UBound(tablo) If tablo(i, 1) = quoi Then If plage(i).EntireRow.Hidden True Then etat True If atraiter Is Nothing Then Set atraiter = plage(i) Else Set atraiter = Union(atraiter, plage(i)) End If End If Next End If If etat Then bouton.Caption "cacher" Else bouton.Caption "montrer" If Not how Then Exit Sub If Not atraiter Is Nothing Then atraiter.EntireRow.Hidden = Not etat End If End Sub
Private Sub CommandButton1_Click() etat = False For i = 2 To 7 voyons Sheets(i).Range("K4:K100"), "ici", bouton, True Next End Sub
If ubound(tablosupp) > 0 then For a = 1 To UBound(tablosupp) bla bla bla next a end if
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionPrivate atraiter As Range Private Sub CommandButton1_Click() If Not atraiter Is Nothing Then atraiter.EntireRow.Hidden = Not atraiter.EntireRow.Hidden CommandButton1.Caption = IIf(atraiter.EntireRow.Hidden, "montrer", "masquer") End If End Sub Private Sub Worksheet_Activate() voyons If Not atraiter Is Nothing Then CommandButton1.Caption = IIf(atraiter.EntireRow.Hidden, "montrer", "masquer") Else CommandButton1.Caption = "masquer" End If End Sub Private Sub voyons() Dim quoi As String, plage As Range, i As Long, tablo quoi = "ici" '=======>>> mets-y ce que toi, tu veux comme critère Set plage = Range("K4:K100") If WorksheetFunction.CountIf(plage, quoi) > 0 Then tablo = plage For i = 1 To UBound(tablo) If tablo(i, 1) = "ici" Then If atraiter Is Nothing Then Set atraiter = plage(i) Else Set atraiter = Union(atraiter, plage(i)) End If End If Next End If End Sub
Private atraiter As Range Private Sub voyons() Dim quoi As String, plage As Range, i As Long, tablo quoi = "---" '=======>>> mets-y ce que toi, tu veux comme critère Set plage = Range("K4:K100") If WorksheetFunction.CountIf(plage, quoi) > 0 Then tablo = plage For i = 1 To UBound(tablo) If tablo(i, 1) = "ici" Then If atraiter Is Nothing Then Set atraiter = plage(i) Else Set atraiter = Union(atraiter, plage(i)) End If End If Next End If
Private Sub Workbook_Open() voyons If Not atraiter Is Nothing Then CommandButton1.Caption = IIf(atraiter.EntireRow.Hidden, "montrer", "masquer") Else CommandButton1.Caption = "masquer" End If End sub
Private Sub CommandButton1_Click() If Not atraiter Is Nothing Then atraiter.EntireRow.Hidden = Not atraiter.EntireRow.Hidden CommandButton1.Caption = IIf(atraiter.EntireRow.Hidden, "montrer", "masquer") End If End Sub
Private Sub CommandButton1_Click() If Not atraiter Is Nothing Then atraiter.EntireRow.Hidden = Not atraiter.EntireRow.Hidden CommandButton1.Caption = IIf(atraiter.EntireRow.Hidden, "montrer", "masquer") End If End Sub
Private Sub Workbook_Open() a = 1 voyons If Not atraiter Is Nothing Then CommandButton1.Caption = IIf(atraiter.EntireRow.Hidden, "montrer", "masquer") Else CommandButton1.Caption = "masquer" End If With Worksheets(1) .EnableAutoFilter = True .EnableOutlining = True .Protect Contents:=True, Password:="Indiko", UserInterfaceOnly:=True End With a = 0 End Sub
Public atraiter As Range Public a As Integer Public Sub voyons() Dim quoi As String, plage As Range, i As Long, j As Long, tablo Application.ScreenUpdating = False Application.Calculation = xlCalculationManual If a = 0 Then USFwait.Show 0 USFwait.Repaint End If For j = 2 To 7 e = 0 Sheets(j).Activate Sheets(j).Unprotect Password:="Toto" For Each s In Sheets(j).Shapes s.Placement = xlMoveAndSize Next quoi = "---" Set plage = Sheets(j).Range("K14:K100") If WorksheetFunction.CountIf(plage, quoi) > 0 Then tablo = plage For i = 1 To UBound(tablo) If tablo(i, 1) = quoi Then If atraiter Is Nothing Then Set atraiter = plage(i) Else Set atraiter = Union(atraiter, plage(i)) End If End If Next End If Sheets(j).Protect Password:="Toto" Next Worksheets(1).Activate Application.Calculation = xlAutomatic Application.ScreenUpdating = True If a = 0 Then Unload USFwait End If End Sub
- on ne peut accéder à un un contrôle "distant" qu'en le faisant précéder de son container (B.A.BA également)
CommandButton1.Caption = IIf(atraiter.EntireRow.Hidden, "montrer", "masquer")
Private Sub Workbook_Open() a = 1 voyons If Not atraiter Is Nothing Then Feuil1.CommandButton1.Caption = IIf(atraiter.EntireRow.Hidden, "montrer", "masquer") Else Feuil1.CommandButton1.Caption = "masquer" End If With Worksheets("Total") .EnableAutoFilter = True .EnableOutlining = True .Protect Contents:=True, Password:="Toto", UserInterfaceOnly:=True End With a = 0 End Sub