Comment modifier la plage utilisé par le code en renseignant une cellule
Bonjour
Dans mon code il y a une référence à une plage, je souhaiterais pouvoir changer cette plage en renseignant une cellule (ex sur la feuille congé dans la cellule en N23 en y inscrivant C9:M52) ou deux cellules (ex:sur la feuille congé dans la cellule N23 en y inscrivant C9 et dans la cellule N24 en y inscrivant M52).
Ainsi je n'aurai pas à aller changer la plage dans le code de chaque plage au risque d'en oublier car mon tableau évolue en insérant des lignes au cours de l'année.
Voila en rouge ce que je souhaite changer dans mon code
Public Flag As Boolean
Dim nABSSOGJ&, nABSINC2J&, nABSINC1J&, nABSSOGN&, nABSINC2N&, nABSINC1N&, n12h&, nCA&, nCAJ&, nCAN&, nRPM&
Private Sub Worksheet_Change(ByVal Target As Range)
Dim PW As String
Dim nABSSOGJ As Double
Dim nABSINC2J As Double
Dim nABSINC1J As Double
Dim nABSSOGN As Double
Dim nABSINC2N As Double
Dim nABSINC1N As Double
If Intersect([C9:M52], Target) Is Nothing Then Exit Sub
Col = Chr(Target.Column + 64)
[Q2] = Col & [C67] & ":" & Col & [D67] ' Nombre absence jour sur plage des SOG
[R2] = Col & [C68] & ":" & Col & [D68] 'Nombre absence jour sur plage des INC2
[S2] = Col & [C69] & ":" & Col & [D69] 'Nombre absence jour sur plage des INC1
[U2] = Col & [C67] & ":" & Col & [D67] 'Nombre absence nuit sur plage des SOG
[V2] = Col & [C68] & ":" & Col & [D68] 'Nombre absence nuit sur plage des INC2
[W2] = Col & [C69] & ":" & Col & [D69] 'Nombre absence nuit sur plage des INC1
nABSSOGJ = Range("Q1")
nABSINC2J = Range("R1")
nABSINC1J = Range("S1")
nABSSOGN = Range("U1")
nABSINC2N = Range("V1")
nABSINC1N = Range("W1")
If nABSSOGJ > [D73] Or nABSSOGN > [D73] Then
MsgBox "Le nombre maximal de SOG absent est atteint !", vbCritical, "Absence SOG"
Target.Interior.Color = RGB(255, 255, 255)
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
If nABSINC2J > [D74] Or nABSINC2N > [D74] Then
MsgBox "Le nombre maximal d'INC2 absent est atteint !", vbCritical, "Absence INC2"
Target.Interior.Color = RGB(255, 255, 255)
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
If nABSINC1J > [D75] Or nABSINC1N > [D75] Then
MsgBox "Le nombre maximal d'INC1 est atteint !", vbCritical, "Absence INC1"
Target.Interior.Color = RGB(255, 255, 255)
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
If Intersect([C9:M52], Target) Is Nothing Then Exit Sub
nCA = Application.CountIf(Intersect([9:52], Target.EntireColumn), "CA")
nRPM = Application.CountIf(Intersect([9:52], Target.EntireColumn), "RPM")
nCAJ = Application.CountIf(Intersect([9:52], Target.EntireColumn), "CAJ*")
nCAN = Application.CountIf(Intersect([9:52], Target.EntireColumn), "*CAN")
If (nCA + nCAJ + nRPM) > [C70] Or (nCA + nCAN + nRPM) > [C70] Then
MsgBox "Le nombre maximal de 12 CA total est déjà atteint !", vbCritical, "Saisie CA"
Target.Interior.Color = RGB(255, 255, 255)
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
If Intersect([C9:M52], Target) Is Nothing Then Exit Sub
n12h = Application.CountIf(Intersect([9:52], Target.EntireColumn), "12h")
If n12h > [C71] Then
MsgBox "Le nombre maximal de 12h pour ce jour est déjà atteint !", vbCritical, "Saisie 12h"
Target.Interior.Color = RGB(255, 255, 255)
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End Sub
Merci bien
Bonjour,
Une piste !?
'Déclaration constante
Const RNG = "C9:M52"
'et
If Intersect(Target, Me.Range(RNG)) Is Nothing Then ...
Cdlt.
Avec ce que tu me proposes il faudra que je rentre dans le code de chaque feuille pour un changement mais c'est déjà mieux que ce que j'ai car je n'aurais qu'un changement par feuille à faire.
Je souhaiterais réaliser un seul changement sur la feuille excel congé puis qu'avec un effet domino cette plage se mette à jour dans les codes de toutes les feuilles.
Bonjour,
Une autre piste...
Utilise des plages nommées ainsi tu n'auras jamais à changer leurs adresses :
Pour chaque feuille nomme plage la plage C9:M52 et le tour est joué.
Tu peux également nommer de la même manière d'autres plage : plgSOC, plgINC1, plgINC2...
Comme le code de toutes les feuilles est identique il est possible de supprimer toussa et de faire un seul code Worksheet_Change dans ThisWorkbook comme j'ai fait.
En réduisant le tout à une seule macro dans ThisWorkbook tu peux quand même espérer une meilleure maintenance !
Je ne vois pas d'intéret de variabiliser :
nABSSOGJ = .Range("Q1") (et assimilé)
En revanche là encore ce que tu pourrais variabiliser pour toutes les feuilles c'est rngQ1 (et assimilé) dans le gestionnaire de fichier...
Les adresses du gestionnaire de fichier s'adaptent à tous les déplacements. Ainsi une cellule nommée rngQ1 ne changera pas de nom mais sera toujours référencé comme telle si tu insères une colonne avant. ainsi au départ elles auraont la m^me adresse de base (Q1) mais avec le temps elle peuvent très bien se balader plus ou moins (de la m^me manière que tes plages et cela sans que tu aies la nécessité de baliser chaque plage dans ta feuille...
Les range multicellulaires s'adaptent en hauteur et en largeur (pour peu que tu ne supprimes pas le début et la fin !) Voire avec la FeuilleTest...
Avec ça tu as de quoi te simplifier la vie.
En revanche les [ ] dans le code sont sauf exception à bannir.
Regarde bien comme j'ai supprimés tes macros : Voire dans ThisWorkbook la macro unique qui fait le boulot de tout le monde... et dans l'explorateur de projet comment j'ai renommé Ws4 et Ws5...
A+
Bonjour
Je découvre la vba grâce à l'aide de ces forum et j'essaie de perfectionner mon tableau.
Je suis conscient qu'il y a beaucoup de chose que je pourrais remplacer par d'autres plus faciles.
J'ai bien compris pour nommer la plage avec le gestionnaire de noms (connaissance nouvelle)
Par contre je ne vois pas : "le code Worksheet_Change dans ThisWorkbook que tu as fait"
je ne dois pas chercher au bon endroit
je vais étudier le gestionnaire de fichier que je ne connais pas.
La vie sera plus simple avec plus de connaissances mais j'avance pas à pas...
Merci
Non sorry c'est moi qui ai écrasé le mauvais fichier. Je te retravaille ça et je te le reposte dans un moment.
... Voilà c'est réparé :
Je précise bien non testé car tu as des notations un peu "cavalière" (absconse) et je peine à peu à te suivre. M2bon sur le fond c'est comme ça que ça devrait se faire...
A+
C'est corrigé dans le message juste au-dessus...
Je te le remet ici pour la route...
Ça à l'air de fonctionner comme je veux très intéressant la plage qui se met à jour toute seule.
Quand je rajoute une ligne ma plage passe à E9:M53, par contre il y a une partie qui ne se met pas à jour (en rouge), si elle se mettait à jour il y aurait [9:53]
If Intersect(.[plage], Target) Is Nothing Then Exit Sub
nCA = Application.CountIf(Intersect(.[9:52], Target.EntireColumn), "CA")
nRPM = Application.CountIf(Intersect(.[9:52], Target.EntireColumn), "RPM")
nCAJ = Application.CountIf(Intersect(.[9:52], Target.EntireColumn), "CAJ*")
nCAN = Application.CountIf(Intersect(.[9:52], Target.EntireColumn), "*CAN")
If (nCA + nCAJ + nRPM) > .[AC5] Or (nCA + nCAN + nRPM) > .[AC5] Then
MsgBox "Le nombre maximal de 12 CA total est déjà atteint !", vbCritical, "Saisie CA"
Target.Interior.Color = RGB(255, 255, 255)
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
If Intersect(.[plage], Target) Is Nothing Then Exit Sub
n12h = Application.CountIf(Intersect(.[9:52], Target.EntireColumn), "12h")
Il est vrai qu'en débutant en vba, j'ai un code un peu grossier.
Je peux t'expliquer ce que je recherche à travers mon code pour que tu comprennes car il doit y a avoir des moyens plus simple.
Dans ma plage que j'ai créer sur le mois d'avril PlageSOG, il ne doit pas y avoir plus de 2 cellules comportant d'absence jour [P3:P52] ou plus de 2 cellules comportant d'absence nuit [T3:T52] impossible d'en mettre plus avec message d'information.
Dans ma plage que j'ai créer sur le mois d'avril PlageINC2, il ne doit pas y avoir plus de 4 cellules comportant d'absence jour [P3:P52] ou plus de 4 cellules comportant d'absence nuit [T3:T52] impossible d'en mettre plus avec message d'information.
Dans ma plage que j'ai créer sur le mois d'avril PlageINC1, il ne doit pas y avoir plus de 8 cellules comportant d'absence jour [P3:P52] ou plus de 8 cellules comportant d'absence nuit [T3:T52] impossible d'en mettre plus avec message d'information.
Dans chaque colonne de ma [plage] il ne doit pas y avoir plus de 5 cellules "12h" impossible d'en mettre plus avec message d'information.
Dans chaque colonne de ma [plage] il ne doit pas y avoir plus de 12 cellules comprenant CA + CAJ* + RPM ou CA+*CAN+RPM impossible d'en mettre plus avec message d'information.
Si tu as des propositions je suis preneur.
Merci bien
Bonjour à tous,
remplace [9:52]
par [ta_plage.entirerow]
eric
ma plage se nomme: « plage »
J’ai essayé avec ce que tu proposes mais ça ne fonctionne pas
« Erreur d’exécution ‘424’ »
Merci
Bonjour,
je viens d'ouvrir ton classeur, ce sont des noms de niveau feuille.
Ta plage ne s'appelle donc pas plage mais 'nom de la feuille'!plage
eric
Eric : pas besoin de nom de feuille si c'est une macro de Workbook avec un With sh... et le point KIVABIEN !
Pour moi ce [9:52] c'est du grand n'importe quoi : Alors n'importe quoi pour n'importe quoi, remplace le par .[plage]... Chez moi ça ne fait rien (si ça ne guérit pas le malade au moins ça le tuera pas !)
Et rappelle toi qu'en matière de boule de cristal on n'est pas fortiche. Alors ton fichier modifié ne nous ferait pas de mal.
Et si en plus tu nous indique quelle cellule modifier (et comment la modifier) pour provoquer une erreur : la on pourra peut-être essayer de tester quelque chose parce que tes abréviations ou du chinois pour moi c'est bonnet jaune...
A+
Ah super ça fonctionne.
Le gestionnaire des noms est très efficace pour faire évoluer ma plage lorsque je rajoute des lignes.
Afin d'améliorer mon code, en perpétuelle évolution, j'ai créer pour le mois d'avril PlageSOG, PlageINC2 et PlageINC1
Je souhaiterais changer mon code en d'utilisant ces 3 plages afin que les plages évoluent aussi automatiquement.
Y a t-il un moyen d'utiliser les PlageSOG, PlageINC2 et PlageINC1 avec un code peut-être plus simple pour avoir le même résultat qu'actuellement?
Voici la partie du code que je souhaiterais modifier en utilisant PlageSOG, PlageINC2 et PlageINC1:
If Intersect(.[Plage], Target) Is Nothing Then Exit Sub
Col = Chr(Target.Column + 64)
.[Q2] = Col & .[AC2] & ":" & Col & [AD2] ' Nombre absence jour sur plage des SOG
.[R2] = Col & .[AC3] & ":" & Col & [AD3] 'Nombre absence jour sur plage des INC2
.[S2] = Col & .[AC4] & ":" & Col & [AD4] 'Nombre absence jour sur plage des INC1
.[U2] = Col & .[AC2] & ":" & Col & [AD2] 'Nombre absence nuit sur plage des SOG
.[V2] = Col & .[AC3] & ":" & Col & [AD3] 'Nombre absence nuit sur plage des INC2
.[W2] = Col & .[AC4] & ":" & Col & [AD4] 'Nombre absence nuit sur plage des INC1
nABSSOGJ = .Range("Q1")
nABSINC2J = .Range("R1")
nABSINC1J = .Range("S1")
nABSSOGN = .Range("U1")
nABSINC2N = .Range("V1")
nABSINC1N = .Range("W1")
If nABSSOGJ > 2 Or nABSSOGN > 2 Then 'sur la plage SOG le nombre de cellule comprenant une valeur de la colonne P ou la colonne T ne doit pas être supérieur à 2
MsgBox "Le nombre maximal de SOG absent est atteint !", vbCritical, "Absence SOG"
Target.Interior.Color = RGB(255, 255, 255)
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
If nABSINC2J > 4 Or nABSINC2N > 4 Then 'sur la plage INC2 le nombre de cellule comprenant une valeur de la colonne P ou la colonne T ne doit pas être supérieur à 4
MsgBox "Le nombre maximal d'INC2 absent est atteint !", vbCritical, "Absence INC2"
Target.Interior.Color = RGB(255, 255, 255)
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
If nABSINC1J > 8 Or nABSINC1N > 8 Then 'sur la plage INC1 le nombre de cellule comprenant une valeur de la colonne P ou la colonne T ne doit pas être supérieur à 4
MsgBox "Le nombre maximal d'INC1 est atteint !", vbCritical, "Absence INC1"
Target.Interior.Color = RGB(255, 255, 255)
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
Merci
Relire ma réponse précédente.
Le fichier joint n'était pas parti...
Non mais là c'est pas ton code qu'il va falloir revoir (encore que...) mais toutes tes feuilles de calcul parce qu'on ne passe pas son temps à recalculer des plages qu'Excel gère automatiquement.
Prend la cellule Q3
Au lieu de faire un indirect(blabla) tu fais :
=NB.SI(PlageSOG;$P3)et ta plage Q2 tu n'en a plus rien à fiche...
et puis YAKA tirer la formule vers le bas...
et puis tes 6 lignes de code tu peux les virer...
OK ?
... Si j'ai bien compris parce que j'avoue que je navigue un peu à vue et dans le brouillard...
Le fichier modifié en colonne QRS
Du coup j'ai encore supprimé quelques variables inutiles...
EDIT : Pour construire toutes tes plages nommées et reconstruire toutes tes formules, je te conseille d'inhiber momentanément la
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
en rajoutant un "X_" devant Workbook ainsi la macro ne se déclenchera pas pendant tout le temps ou tu vas bricoler dans tes feuilles...
Private Sub X_Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Quand tu auras terminé de bricoler tes formules TORAPUKA enlever le "X_" et ta macro redeviendra opérationnelle.
En fait, j'ai mal expliqué, je souhaite que le nombre de cellules contenant une valeur de la colonne P dans chaque colonne de la PlageSOG ne soit pas supérieur à 2 ou le nombre de cellules contenant une valeur de la colonne T dans chaque colonne de la PlageSOG ne soit pas supérieur à 2
Dans mon code actuellement c'est ainsi que je peux rechercher dans les colonnes de chaque plage
Col = Chr(Target.Column + 64)
.[Q2] = Col & 9 & ":" & Col & 12 ' Nombre absence jour sur plage des SOG
.[R2] = Col & 9 & ":" & Col & 18 'Nombre absence jour sur plage des INC2
.[S2] = Col & 22 & ":" & Col & 38 'Nombre absence jour sur plage des INC1
.[U2] = Col & 9 & ":" & Col & 12 'Nombre absence nuit sur plage des SOG
.[V2] = Col & 9 & ":" & Col & 18 'Nombre absence nuit sur plage des INC2
.[W2] = Col & 22 & ":" & Col & 38 'Nombre absence nuit sur plage des INC1
Y a t-il un autre moyen en utilisant PlageSOG?
Merci
une possibilité...
Il n'y a pas de code dans ce fichier