Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

VB Case

E

enzo_s

XLDnaute Junior
Bonjour à tous,

Petite question, j'ai un feuille avec un macro avec beaucoup de CASE.

L'idée est quand me met Yes en "D3", "E3" passe en No et Idem pour F et G

Le problème est que des fois cela ne change pas tout de suite, je dois le faire deux fois pour que ça change

Merci d'avance de l'aide

A+





VB: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("$D3:$G34")) Is Nothing Then Exit Sub
Select Case Target.Address
    Case "$D$3"
        Application.EnableEvents = False
        Range("E3") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$3"
        Application.EnableEvents = False
        Range("D3") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$4"
        Application.EnableEvents = False
        Range("E4") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$4"
        Application.EnableEvents = False
        Range("D4") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$5"
        Application.EnableEvents = False
        Range("E5") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$5"
        Application.EnableEvents = False
        Range("D5") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$6"
        Application.EnableEvents = False
        Range("E6") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$6"
        Application.EnableEvents = False
        Range("D6") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$7"
        Application.EnableEvents = False
        Range("E7") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$7"
        Application.EnableEvents = False
        Range("D7") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$8"
        Application.EnableEvents = False
        Range("E8") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$8"
        Application.EnableEvents = False
        Range("D8") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$9"
        Application.EnableEvents = False
        Range("E9") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$9"
        Application.EnableEvents = False
        Range("D9") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$10"
        Application.EnableEvents = False
        Range("E10") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$10"
        Application.EnableEvents = False
        Range("D10") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$11"
        Application.EnableEvents = False
        Range("E11") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$11"
        Application.EnableEvents = False
        Range("D11") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$12"
        Application.EnableEvents = False
        Range("E12") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$12"
        Application.EnableEvents = False
        Range("D12") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$13"
        Application.EnableEvents = False
        Range("E13") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$13"
        Application.EnableEvents = False
        Range("D13") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$14"
        Application.EnableEvents = False
        Range("E14") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$14"
        Application.EnableEvents = False
        Range("D14") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$15"
        Application.EnableEvents = False
        Range("E15") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$15"
        Application.EnableEvents = False
        Range("D15") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$16"
        Application.EnableEvents = False
        Range("E16") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E16"
        Application.EnableEvents = False
        Range("D16") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$17"
        Application.EnableEvents = False
        Range("E17") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$17"
        Application.EnableEvents = False
        Range("D17") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$18"
        Application.EnableEvents = False
        Range("E18") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$18"
        Application.EnableEvents = False
        Range("D18") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$19"
        Application.EnableEvents = False
        Range("E19") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$19"
        Application.EnableEvents = False
        Range("D19") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$20"
        Application.EnableEvents = False
        Range("E20") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$20"
        Application.EnableEvents = False
        Range("D20") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$21"
        Application.EnableEvents = False
        Range("E21") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E21"
        Application.EnableEvents = False
        Range("D21") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$22"
        Application.EnableEvents = False
        Range("E22") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$22"
        Application.EnableEvents = False
        Range("D22") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$23"
        Application.EnableEvents = False
        Range("E23") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$23"
        Application.EnableEvents = False
        Range("D23") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$24"
        Application.EnableEvents = False
        Range("E24") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$24"
        Application.EnableEvents = False
        Range("D24") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$25"
        Application.EnableEvents = False
        Range("E25") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$25"
        Application.EnableEvents = False
        Range("D25") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$26"
        Application.EnableEvents = False
        Range("E26") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E26"
        Application.EnableEvents = False
        Range("D26") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$27"
        Application.EnableEvents = False
        Range("E27") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$27"
        Application.EnableEvents = False
        Range("D27") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$28"
        Application.EnableEvents = False
        Range("E28") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$28"
        Application.EnableEvents = False
        Range("D28") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$29"
        Application.EnableEvents = False
        Range("E29") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$29"
        Application.EnableEvents = False
        Range("D29") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$30"
        Application.EnableEvents = False
        Range("E30") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$30"
        Application.EnableEvents = False
        Range("D30") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$31"
        Application.EnableEvents = False
        Range("E31") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E31"
        Application.EnableEvents = False
        Range("D31") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$32"
        Application.EnableEvents = False
        Range("E32") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$32"
        Application.EnableEvents = False
        Range("D32") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$33"
        Application.EnableEvents = False
        Range("E33") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$33"
        Application.EnableEvents = False
        Range("D33") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$D$34"
        Application.EnableEvents = False
        Range("E34") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E$34"
        Application.EnableEvents = False
        Range("D34") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$3"
        Application.EnableEvents = False
        Range("G3") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$3"
        Application.EnableEvents = False
        Range("F3") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$4"
        Application.EnableEvents = False
        Range("G4") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$4"
        Application.EnableEvents = False
        Range("F4") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$5"
        Application.EnableEvents = False
        Range("G5") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$5"
        Application.EnableEvents = False
        Range("F5") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$6"
        Application.EnableEvents = False
        Range("G6") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$6"
        Application.EnableEvents = False
        Range("F6") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$7"
        Application.EnableEvents = False
        Range("G7") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$7"
        Application.EnableEvents = False
        Range("F7") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$8"
        Application.EnableEvents = False
        Range("G8") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$8"
        Application.EnableEvents = False
        Range("F8") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$9"
        Application.EnableEvents = False
        Range("G9") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$9"
        Application.EnableEvents = False
        Range("F9") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$10"
        Application.EnableEvents = False
        Range("G10") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$10"
        Application.EnableEvents = False
        Range("F10") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$11"
        Application.EnableEvents = False
        Range("G11") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$11"
        Application.EnableEvents = False
        Range("F11") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$12"
        Application.EnableEvents = False
        Range("G12") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$12"
        Application.EnableEvents = False
        Range("F12") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$13"
        Application.EnableEvents = False
        Range("G13") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$13"
        Application.EnableEvents = False
        Range("F13") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$14"
        Application.EnableEvents = False
        Range("G14") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$14"
        Application.EnableEvents = False
        Range("F14") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$15"
        Application.EnableEvents = False
        Range("G15") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$15"
        Application.EnableEvents = False
        Range("F15") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$16"
        Application.EnableEvents = False
        Range("G16") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E16"
        Application.EnableEvents = False
        Range("F16") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$17"
        Application.EnableEvents = False
        Range("G17") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$17"
        Application.EnableEvents = False
        Range("F17") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$18"
        Application.EnableEvents = False
        Range("G18") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$18"
        Application.EnableEvents = False
        Range("F18") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$19"
        Application.EnableEvents = False
        Range("G19") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$19"
        Application.EnableEvents = False
        Range("F19") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$20"
        Application.EnableEvents = False
        Range("G20") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$20"
        Application.EnableEvents = False
        Range("F20") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$21"
        Application.EnableEvents = False
        Range("G21") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E21"
        Application.EnableEvents = False
        Range("F21") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$22"
        Application.EnableEvents = False
        Range("G22") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$22"
        Application.EnableEvents = False
        Range("F22") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$23"
        Application.EnableEvents = False
        Range("G23") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$23"
        Application.EnableEvents = False
        Range("F23") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$24"
        Application.EnableEvents = False
        Range("G24") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$24"
        Application.EnableEvents = False
        Range("F24") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$25"
        Application.EnableEvents = False
        Range("G25") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$25"
        Application.EnableEvents = False
        Range("F25") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$26"
        Application.EnableEvents = False
        Range("G26") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E26"
        Application.EnableEvents = False
        Range("F26") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$27"
        Application.EnableEvents = False
        Range("G27") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$27"
        Application.EnableEvents = False
        Range("F27") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$28"
        Application.EnableEvents = False
        Range("G28") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$28"
        Application.EnableEvents = False
        Range("F28") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$29"
        Application.EnableEvents = False
        Range("G29") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$29"
        Application.EnableEvents = False
        Range("F29") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$30"
        Application.EnableEvents = False
        Range("G30") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$30"
        Application.EnableEvents = False
        Range("F30") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$31"
        Application.EnableEvents = False
        Range("G31") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$E31"
        Application.EnableEvents = False
        Range("F31") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$32"
        Application.EnableEvents = False
        Range("G32") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$32"
        Application.EnableEvents = False
        Range("F32") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$33"
        Application.EnableEvents = False
        Range("G33") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$33"
        Application.EnableEvents = False
        Range("F33") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$34"
        Application.EnableEvents = False
        Range("G34") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$G$34"
        Application.EnableEvents = False
        Range("F34") = IIf(Target.Value = "Yes", "No", "Yes")
End Select
Application.EnableEvents = True
End Sub
 
Trier par date Trier par votes

dg62

XLDnaute Barbatruc
Bonjour Enzo_s

je viens d'essayer et cela fonctionne correctement. Je présume que votre application ne se limite pas à cette procédure. Le problème peut-être ailleurs.
 
Vote positif 0 Vote négatif

Dranreb

XLDnaute Barbatruc
Bonjour.
enzo_s à dit:
j'ai un feuille avec un macro avec beaucoup de CASE.
Cliquez pour agrandir...
Je trouve aussi …
VB: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("$D3:$G34")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Offset(, 1 - (Target.Column Mod 2) * 2) = IIf(Target.Value = "Yes", "No", "Yes")
Application.EnableEvents = True
End Sub
 
Vote positif 0 Vote négatif
E

enzo_s

XLDnaute Junior

Ha ben c'est bien plus simple avec votre solution !

Merci !!
 
Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.

Discussions similaires

  • Question
XL 2021 faire évoluer un code VBA
Réponses
3
Affichages
424
Dranreb
  • Question
XL 2021 Exportation feuille msgbox yes no avec module Thisworkbook
2
Réponses
21
Affichages
1 K
Nicolas JACQUIN
U
  • Résolu(e)
Autres Mettre en couleur ligne avec VBA car MFC occupées (Excel 2003)
2
Réponses
15
Affichages
1 K
un internaute
U
U
  • Question
Autres Enregistrer sur Onglet TOTO
Réponses
2
Affichages
502
un internaute
U
T
  • Question
XL 2016 Optimisation d'une boucle
2 3
Réponses
30
Affichages
2 K
thivame
T
C
  • Résolu(e)
Microsoft 365 Amélioration Macro.
Réponses
7
Affichages
528
crazy
C
M
  • Question
XL 2016 Impression d'un rapport PDF depuis une URL avec VBA
Réponses
3
Affichages
825
mrcooll
M
  • Résolu(e)
XL 2016 cellules A, b, C et D sont automatiquement vides
Réponses
3
Affichages
560
job75
U
  • Question
Autres [RESOLU] Application.ScreenUpdating = False
Réponses
4
Affichages
1 K
un internaute
U
L
  • Question
Microsoft 365 Faire agir une macro en fonction de l'emplacement du bouton
Réponses
2
Affichages
336
AtTheOne
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…