Simplifier conditions

[yannlion]

Bonjour,

Juste par curiosité et pour la perfection du geste, est-ce possible de simplifier tous ces or ?

Sub CREATOTAL()

Dim wSheet As Worksheet

For Each wSheet In ActiveWorkbook.Worksheets
    If wSheet.Visible And (wSheet.Name = "FPOU" Or wSheet.Name = "FBEN" Or wSheet.Name = "FMIN" Or wSheet.Name = "FCAD" Or wSheet.Name = "FJUN" Or wSheet.Name = "FJUN_Elite" Or wSheet.Name = "FSEN" Or wSheet.Name = "FSEN_Elite" Or wSheet.Name = "MPOU" Or wSheet.Name = "MBEN" Or wSheet.Name = "MMIN" Or wSheet.Name = "MCAD" Or wSheet.Name = "MJUN" Or wSheet.Name = "MJUN_Elite" Or wSheet.Name = "MSEN" Or wSheet.Name = "MSEN_Elite") Then
        wSheet.Activate
        ActiveSheet.Unprotect (GPassword)
        Application.Run "TOUTES_IMPRESSIONS_SOLISTES"
        wSheet.Protect (GPassword)
    Else
        If wSheet.Visible And (wSheet.Name = "DBEN" Or wSheet.Name = "DMin" Or wSheet.Name = "DCAD" Or wSheet.Name = "DJUN" Or wSheet.Name = "DSEN" Or wSheet.Name = "EBEN" Or wSheet.Name = "FSEN" Or wSheet.Name = "FSEN_Elite" Or wSheet.Name = "EBEN" Or wSheet.Name = "MBEN" Or wSheet.Name = "EMIN" Or wSheet.Name = "ECAD" Or wSheet.Name = "EJUN" Or wSheet.Name = "ESEN" Or wSheet.Name = "GJUN" Or wSheet.Name = "GSEN" Or wSheet.Name = "G1520") Then
        wSheet.Activate
        ActiveSheet.Unprotect (GPassword)
        Application.Run "TOUTES_IMPRESSIONS_DUO_EQUIPES"
        wSheet.Protect (GPassword)
        End If
    End If
Next

End Sub

Merci
Yannlion

 

[chris]

Re : Simplifier conditions

Bonjour

Un select case sur wSheet.Name serait plus concis.

Pourquoi le déprotection juste pour imprimer ?

 

[Paf]

Re : Simplifier conditions

Bonjour à tous

A essayer
Pas sûr que le geste soit parfait !


If wSheet.Visible And (wSheet.Name Like "F*" Or wSheet.Name Like "M*") Then

même principe pour l'autre test.

A+

 

[yannlion]

Re : Simplifier conditions

Bonjour Chris et Paf,

Et merci de m'aider dans mes recherches.
Je connais pas select case je vais me renseigner et essayer.

Pourquoi le déprotection juste pour imprimer ?

Non je déverrouille pour modifier les feuilles avant de créer les fichiers et les imprimer, je reverouiille pour empêcher la saisie dans certaines cellules.

If wSheet.Visible And (wSheet.Name Like "F*" Or wSheet.Name Like "M*") Then

Je vais aussi tenter mais j'aurais autant de or qu'avant non ?

Est-ce qu'il est possible de déclarer un ensemble de nom (ex FPOU, FBEN, FMIN, FCAD, FJUN, FJUN_Elite, FSEN, FSEN_Elite, MPOU, MBEN, MMIN, MCAD, MJUN, MJUN_Elite, MSEN, SEN_Elite) et de lancer :

If wSheet.Visible And (wSheet.Name = élément faisant parti de l'ensemble de nom) then ..

Merci d'avance
Yannlion

 

[Paf]

Re : Simplifier conditions

Re

wSheet.Name Like "F*" remplace tous les tests de noms de feuille commençant par F (soit 8) et wSheet.Name Like "M*" remplace tous les tests de noms de feuille commençant par M (soit 8 également)

If wSheet.Visible And (wSheet.Name Like "F*" Or wSheet.Name Like "M*") Then

fait la même chose que

If wSheet.Visible And (wSheet.Name = "FPOU" Or wSheet.Name = "FBEN" Or wSheet.Name = "FMIN" Or wSheet.Name = "FCAD" Or wSheet.Name = "FJUN" Or wSheet.Name = "FJUN_Elite" Or wSheet.Name = "FSEN" Or wSheet.Name = "FSEN_Elite" Or wSheet.Name = "MPOU" Or wSheet.Name = "MBEN" Or wSheet.Name = "MMIN" Or wSheet.Name = "MCAD" Or wSheet.Name = "MJUN" Or wSheet.Name = "MJUN_Elite" Or wSheet.Name = "MSEN" Or wSheet.Name = "MSEN_Elite") Then

Bonne suite

 

[yannlion]

Re : Simplifier conditions

Oui c'est parfait !

Voilà ce que ça donne au final :

Sub CREATOTAL()

Dim wSheet As Worksheet

For Each wSheet In ActiveWorkbook.Worksheets
    If wSheet.Visible = "-1" And (wSheet.Name Like "F*" Or wSheet.Name Like "M*") Then
        wSheet.Activate
        ActiveSheet.Unprotect (GPassword)
        Application.Run "TOUTES_IMPRESSIONS_SOLISTES"
        wSheet.Protect (GPassword)
    Else
    If wSheet.Visible = "-1" And (wSheet.Name Like "D*" Or wSheet.Name Like "E*" Or wSheet.Name Like "G*") Then
        wSheet.Activate
        ActiveSheet.Unprotect (GPassword)
        Application.Run "TOUTES_IMPRESSIONS_DUO_EQUIPES"
        wSheet.Protect (GPassword)
        End If
    End If
Next

End Sub

Beaucoup plus léger en effet !
Merci beaucoup

Yannlion

 

[chris]

Re : Simplifier conditions

Re

Sub CREATOTAL()

Dim wSheet As Worksheet

For Each wSheet In ActiveWorkbook.Worksheets
    If wSheet.Visible Then
        Select Case wSheet.Name
            Case "FPOU", "FBEN", "FMIN", "FCAD", "FJUN", "FJUN_Elite", "FSEN", "FSEN_Elite", "MPOU", "MBEN", "MMIN", "MCAD", "MJUN", "MJUN_Elite", "MSEN", "MSEN_Elite"
                wSheet.Activate
                wSheet.Unprotect (GPassword)
                Application.Run "TOUTES_IMPRESSIONS_SOLISTES"
                wSheet.Protect (GPassword)
            Case "DBEN", "DMin", "DCAD", "DJUN", "DSEN", "EBEN", "FSEN", "FSEN_Elite", "EBEN", "MBEN", "EMIN", "ECAD", "EJUN", "ESEN", "GJUN", "GSEN", "G1520"
                wSheet.Activate
                wSheet.Unprotect (GPassword)
                Application.Run "TOUTES_IMPRESSIONS_DUO_EQUIPES"
                wSheet.Protect (GPassword)
        End Select
    End If
Next

End Sub

Dans ton code d'origine tu cites certaines feuilles dans les 2 cas : sont-elles imprimées 2 fois ?

Sinon on peut aussi simplifier le select case

Sub CREATOTAL()

Dim wSheet As Worksheet

For Each wSheet In ActiveWorkbook.Worksheets
    If wSheet.Visible Then
        Select Case Left(wSheet.Name, 1)
            Case "F", "M"
                wSheet.Activate
                wSheet.Unprotect (GPassword)
                Application.Run "TOUTES_IMPRESSIONS_SOLISTES"
                wSheet.Protect (GPassword)
            Case "D", "E", "G"
                wSheet.Activate
                wSheet.Unprotect (GPassword)
                Application.Run "TOUTES_IMPRESSIONS_DUO_EQUIPES"
                wSheet.Protect (GPassword)
        End Select
    End If
Next

End Sub

 

[yannlion]

Re : Simplifier conditions

Non c'était une erreur.
Effectivement ta solution marche parfaitement et m'a permis de découvrir le select case !

Merci à tout les deux.