Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B1").Value = "start" Then
Sheets("B").Visible = True
Sheets("C").Visible = True
Sheets("D").Visible = True
Else
Sheets("B").Visible = False
Sheets("C").Visible = False
Sheets("D").Visible = False
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim a, e
Application.ScreenUpdating = False
a = Array("B", "C", "D")
For Each e In a
Sheets(e).Visible = xlSheetVisible
Next
If LCase([B1]) <> "start" Then Sheets(a).Visible = xlSheetHidden 'xlSheetVeryHidden
End Sub
Bonjour achraf,
voir fichier joint
à+
Philippe
Bonjour Job,Bonsoir achraf26, Philippe, TBS,
Une autre solution :
A+Code:Private Sub Worksheet_Change(ByVal Target As Range) Dim a, e Application.ScreenUpdating = False a = Array("B", "C", "D") For Each e In a Sheets(e).Visible = xlSheetVisible Next If LCase([B1]) <> "start" Then Sheets(a).Visible = xlSheetHidden 'xlSheetVeryHidden End Sub
Merci Philipe, Fonctionne parfaitement, j'ai su mettre 2 Options Start1 pour feuille B-C et Start2 pour feuille D-EBonjour achraf,
voir fichier joint
à+
Philippe
Dim S(4) As Worksheet, n As Byte
Private Sub Worksheet_Change(ByVal R As Range)
For n = 1 To 4
Set S(n) = Sheets(Chr(65 + n)): S(n).Visible = 2
Next
Application.ScreenUpdating = 0
Select Case [B1]
Case "Start1": S(1).Visible = 1: S(2).Visible = 1
Case "Start2": S(3).Visible = 1: S(4).Visible = 1
Case "Start3": S(1).Visible = 1: S(2).Visible = 1: S(3).Visible = 1: S(4).Visible = 1
End Select
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim a, b, e
Application.ScreenUpdating = False
a = Array("B", "C")
b = Array("D", "E")
For Each e In Split(Join(a, Chr(1)) & Chr(1) & Join(b, Chr(1)), Chr(1))
Sheets(e).Visible = xlSheetVisible
Next
If LCase([B1]) = "start1" Then Exit Sub
If LCase([B1]) <> "start2" Then Sheets(a).Visible = xlSheetHidden 'xlSheetVeryHidden
If LCase([B1]) <> "start3" Then Sheets(b).Visible = xlSheetHidden 'xlSheetVeryHidden
Me.Select
End Sub
Bonjour à tous,
Au post #8 il est dit "j'ai essayé de mettre Start3 pour feuille B-C-D-E"
Mais dans le fichier il est dit "Start1 normalement deverait ouvrir tous les feuilles".
Si c'est cette dernière proposition que l'on retient :
Bonne journée.Code:Private Sub Worksheet_Change(ByVal Target As Range) Dim a, b, e Application.ScreenUpdating = False a = Array("B", "C") b = Array("D", "E") For Each e In Split(Join(a, Chr(1)) & Chr(1) & Join(b, Chr(1)), Chr(1)) Sheets(e).Visible = xlSheetVisible Next If LCase([B1]) = "start1" Then Exit Sub If LCase([B1]) <> "start2" Then Sheets(a).Visible = xlSheetHidden 'xlSheetVeryHidden If LCase([B1]) <> "start3" Then Sheets(b).Visible = xlSheetHidden 'xlSheetVeryHidden Me.Select End Sub
Bonjour
autre exemple pour 3 cas
Code:Dim S(4) As Worksheet, n As Byte Private Sub Worksheet_Change(ByVal R As Range) For n = 1 To 4 Set S(n) = Sheets(Chr(65 + n)): S(n).Visible = 2 Next Application.ScreenUpdating = 0 Select Case [B1] Case "Start1": S(1).Visible = 1: S(2).Visible = 1 Case "Start2": S(3).Visible = 1: S(4).Visible = 1 Case "Start3": S(1).Visible = 1: S(2).Visible = 1: S(3).Visible = 1: S(4).Visible = 1 End Select End Sub
Merci ça fonctionne correctement, j'ai même réussi à rajouter une feuille nommé F j'ai mis S(5) ça fonctionnait, ma question est si j'ai plusieurs feuilles comment je vais savoir le numéro que je dois attribué à une feuille pour la controler ?
Merci pour votre aide
Bonjour
autre exemple pour 3 cas
Code:Dim S(4) As Worksheet, n As Byte Private Sub Worksheet_Change(ByVal R As Range) For n = 1 To 4 Set S(n) = Sheets(Chr(65 + n)): S(n).Visible = 2 Next Application.ScreenUpdating = 0 Select Case [B1] Case "Start1": S(1).Visible = 1: S(2).Visible = 1 Case "Start2": S(3).Visible = 1: S(4).Visible = 1 Case "Start3": S(1).Visible = 1: S(2).Visible = 1: S(3).Visible = 1: S(4).Visible = 1 End Select End Sub