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+

10manjul.xlsm (371.51 Ko)

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é :

4manjul.xlsm (370.23 Ko)

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+

Ok

Merci

C'est corrigé dans le message juste au-dessus...

Je te le remet ici pour la route...

10manjul.xlsm (370.23 Ko)

Ç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.

4manjul.xlsm (221.76 Ko)

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.

3manjul-v1.xlsm (222.74 Ko)

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

8manjul.xlsm (221.44 Ko)

une possibilité...

15manjul-v2.xlsm (220.98 Ko)

Il n'y a pas de code dans ce fichier

Rechercher des sujets similaires à "comment modifier plage utilise code renseignant"