Gestion nom des plages par VBA

Bonjour a tous,

Je suis confronté à un problème de noms de plage.

Mon fichier comporte deux listes déroulantes liées.

J'ai donc, la première liste déroulante maitre qui fait référence à une plage (A1 à M1).

Et mes listes indirecte (A2 ; A 20) ; (B 2 ; B 20) etc...

Pour que les listes soient liées, la zone de chaque liste indirect doit porter le nom de la cellule maitre.

Si A1 = " Usinage "
A2 = " CN " ; A3 = "Debit " ; etc...

La plage A2 à A 20 doit porter le nom "Usinage".

La valeur des cellules maitres pouvant être modifiée, je dois passer par du VBA.

Code:

---

1 2 3 4 5 6

Sub Renommer_Plage(TargetCell As Range) If TargetCell.Row = 1 And TargetCell.Column >= 2 And TargetCell.Column <= 16 Then ActiveWorkbook.Names.Add Name:=TargetCell.Value, RefersTo:=Range(TargetCell.Offset(1, 0), TargetCell.Offset(26, 0)) Else: End If End Sub

---

et Sur les feuilles sources:

Code:

---

1 2 3

Private Sub Worksheet_Change(ByVal Target As Range) Renommer_Plage Target End Sub

---

Les listes déroulantes marchent, mais au lieu de renommer la plage déjà existante, excel crée une nouvelle plage sur la même zone avec un nom différent.

Du coup, les données peuvent avoir des problèmes de fiabilité.

Je voudrais que lorsqu'une modif est faite dans la liste déroulante maitre, que la liste déroulante dépendante de la première renomme la plage au lieu d'en recréer une autre.

Je cherche depuis un moment la solution mais je crois bien avoir besoin d'aide....

Comment puis-je résoudre ce problème ?

Merci de vos commentaires,

Bonjour

Citation:

---

Comment puis-je résoudre ce problème ?

---

par exemple et entre autres, dèjà :
en supprimant le nom puis en le redéfinissant.;)

La méthode delete permet la suppression d'un élément de la collection Names
Exemple :

Code:

---

thisworkbook.names("toto").delete

---

Salut,

Mais comment je peux faire pour supprimer la bonne plage ?

J'ai déja créer les noms de plage manuellement, ne puis-je pas demander de modifier le nom plutot que d'enregistrer une nouvelle plage nommée ?

Salut.

Si tes listes déroulantes liées étaient créées autrement, tu n'aurais besoin d'aucune ligne de code pour tes modifs...

Cela étant, la propriété Name d'une plage nommée est en lecture-écriture, ce qui permet de renommer la plage en modifiant simplement la propriété

Code:

---

names("MaPlage").Name="TaPlage"

---

Bonjour,

pas sûr d'avoir bien compris mais si tes plages par elles-mêmes ne changent tu pourrais travailler avec une table d'alias.

Par exemple en G1:H2 :
Nom Alias
Usinage Liste1

Liste1 est défini sur A2:A20. Si Usinage change tu ne changes que G2.
Et pour avoir ta plage :

Code:

---

=INDIRECT(RECHERCHEV($A$1;$G$2:$H$10;2;FAUX))

---

(ou l'équivalent en vba)
eric

J'ai besoin de l'écrire en Vba car il y a plusieurs feuilles sur lesquelles dépendant les listes déroulantes.

Un "simple" rename n'existe pas ?

Code:

---

1 2 3 4 5 6 7 8 9 10

Sub Renommer_Plage(TargetCell As Range) If TargetCell.Row = 1 And TargetCell.Column >= 2 And TargetCell.Column <= 16 Then     ActiveWorkbook.Names.Add Name:=TargetCell.Value, RefersTo:=Range(TargetCell.Offset(1, 0), TargetCell.Offset(26, 0)) ' Add Name a changer pour renommer la plage qui porte déjà un nom (l'ancien nom avant modif de la Cellule C1 pour la colonne C par exemple).   Else: End If End Sub

---

Citation:

---

Envoyé par Pierre Fauconnier

[...]Cela étant, la propriété Name d'une plage nommée est en lecture-écriture, ce qui permet de renommer la plage en modifiant simplement la propriété

Code:

---

names("MaPlage").Name="TaPlage"

---

---

Ma réponse ne te convient pas?

Si mais par quoi dois-remplacer "Maplage" ; "Taplage" ?

Ben... Ton ancien nom par ton nouveau... 8O

mais il est variable !!

Je modifie la plage usinage:

- En cellule C1 => Usinage
- La plage C2 à C20 s'appelle usinage.


Usinage devient Perçage.

La macro doit renommer la plage Usinage par "perçage".


Demain je fais la même chose sur la colonne P avec un autre nom.

Comment faire pour qu'il détecte l'ancien nom (qui vient d’être modifié)...

Oui, je sais qu'il est variable mais ok, je comprends mieux la manip (même s'il ne me viendrait jamais à l'idée de développer de cette manière...).

Tu dois utiliser l'évènement Change de la feuille concernée pour détecter un changement de valeur sur la plage concernée (celle qui contient ce qui génère les noms des listes). Le problème est qu'au moment du changement, tu as perdu l'ancienne valeur. Il faut donc monter une usine à gaz pour y arriver...

C'est le problème des listes déroulantes en cascade créées avec des plages nommées. Tu figes dans la structure du classeur des données qui foutent la pagaille lorsqu'elles sont changées. Normalement, les noms des listes en cascade répondent à une structure qui est peu modifiée par la suite. Si tu modifies cette structure à la volée, c'est le crash... Cela vaudrait peut-être la peine de se poser la question d'une meilleure conception de ton classeur et/ou d'une réflexion "métier" en amont... ;)

On touche aux limites d'Excel qui n'est pas du tout l'outil adéquat pour des traitements de ce genre.

Une solution pourrait consister en ceci: boucler sur les plages nommées, regarder vers quoi elles pointent et en fonction de cela, modifier le nom...

Tu pourrais utiliser cette fonction, en l'appelant depuis une boucle qui teste chaque cellule de la ligne d'entête de tes plages nommées

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

Function RenameNamedRange(TitleRange As Range) Dim tmpName As Name Dim Counter As Long Dim Found As Boolean   Counter = 1 Do While Counter <= Names.Count And Not Found Set tmpName = Names(Counter) If Not Intersect(Range(tmpName.RefersTo), TitleRange(2)) Is Nothing Then Found = True tmpName.Name = TitleRange.Value End If Counter = Counter + 1 Loop End Function

---

Si la plage de tes intitulés de listes est en C1:I1, tu peux utiliser une procédure qui appelle la fonction de renommage pour chaque cellule de la plage d'intitulés

Code:

---

1 2 3 4 5 6 7

Sub UpdateNames() Dim oCell As Range   For Each oCell In Range("c1:i1") RenameNamedRange oCell Next End Sub

---

Je trouve tellement aberrant de changer cela que je n'ai pas pris la peine de tester que les noms qui seront utilisés sont valides (commencent par une lettre, ne contiennent pas d'espaces ou de caractères bizarres, ...). Je pense qu'il serait temps de réfléchir à la conception de ton outil (notamment des listes en cascade) mais pas que. Une solide remise en cause des procédures qui amènent à ces modifs serait à mon avis nécessaire et salutaire.

Bonjour,

mais c'est quoi le problème qu'une même plage porte 2 ou 10 noms tant que c'est pas 50 ou 300 parce que c'est ch... à faire ?
Et peut-être que tu te compliques la vie en essayant de résoudre ton problème de cette façon et qu'il y a une solution plus simple.
Pour ça il faudrait savoir en détail ton besoin réel d'origine.
eric

Salut Eric,

Citation:

---

Envoyé par eriiic

[...]Et peut-être que tu te compliques la vie en essayant de résoudre ton problème de cette façon et qu'il y a une solution plus simple.
Pour ça il faudrait savoir en détail ton besoin réel d'origine.
eric

---

Là-dessus, je suis d'accord avec toi. Mais comme Joris ne répond pas à cette question-là, pourtant essentielle pour l'aider efficacement...

Par contre, je ne te suis pas sur le "pas de problèmes si plusieurs noms pointent vers la même plage"... Si Usinage devient Perçage (déjà, c'est douteux, mais bon) puis que ça redevient usinage, ben ça va planter.

Au delà, à partir du moment où on change ce qui ne devrait pas l'être, on ouvre la porte à des manipulations qui, tôt ou tard, mettront le bazar dans le classeur avec une kyrielle de noms qui n'ont plus aucune utilité.

Pour moi, la vraie question est: Pourquoi devoir changer les noms des plages? Il y a un manager fou qui tous les matins renomment usinage en perçage, emballage en manutention, perçage en forage etc etc? Ce n'est pas très sérieux, à mon sens.

Le problème est, avant tout, un problème de conception de classeur et, en amont, un problème de procédure.

D'accord, je comprends le problème.

Les modifications sur la première liste déroulante seront tout de meme assez rare.

Peut être qui sera plus simple de faire une révision annuelle et manuelle des noms de plage....

Je vais tout de meme tester la fonction qui m'a l'air compréhensible ^^.

Je reviens vers vous si j'ai besoin d'aide.

Un grand merci pour votre aide.

Mon fichier doit permettre de répertorier tout les documents de travail utilisés dans les atliers et des les affecter à un poste ou un produit.


les noms présent dans les listes maitres seront amenées à être modifié 2 à 3 fois par an.

J'ai plusieurs choses dans ces listes:

- Des types d'atelier (usinage ; perçage) => Celles ci ne changeront jamais
- Des noms de produit => Qui évoluent

Les listes indirectes seront:

- Pour les ateliers: Poste 1 ; Poste 2 ; etc
- Pour les noms de produit => dessus / dessous / joues / etc

Sur une feuille excel, l'utilisateur choisi le tri "maitre" puis son poste ou son produit et une recherche lui affichera toutes les procédures affectées à son choix.

Les procédures et les affectations sont sur une feuille "répertoire", où je retrouve en colonne tout les postes et tout les produits (formule auto).

Les procédures (une pour chaque ligne) sont affectées aux colonnes concernées par un "1".

Citation:

---

Si Usinage devient Perçage (déjà, c'est douteux, mais bon) puis que ça redevient usinage, ben ça va planter.

---

Non, j'ai dû mal me faire comprendre.
Il ne s'agit pas de changer le nom. Il peut très bien y avoir 2 ou 10 noms qui pointent la même plage A2:A20 et plus aucun problème sans complications inutiles.

Sinon je n'ai toujours pas compris la nécessité d'avoir plusieurs noms pour la même plage. Je n'arrive pas à visualiser et qq chose m'échappe.
eric

Eric,

Si on ne supprime pas les noms mais qu'on en ajoute, on risque à un moment d'ajouter un nom qui existe déjà et donc, plantage. (Usinage -> perçage -> usinage).

Ce que veut justement éviter le demandeur, c'est d'avoir plusieurs noms pour la même plage et donc, il souhaite pouvoir renommer sa plage...

Je ne pense pas.
Logiquement s'il utilise un nom qui n'existe pas (car supprimé) là il y aura problème, donc ça ne doit pas arriver.*
Et combien même il utiliserait à tour de rôle l'un ou l'autre quel serait le problème ? L'essentiel est qu'ils soient bien définis sur la bonne plage.
Je ne vois aucun inconvénient à ce qu'il y en ait plusieurs pour la même plage, qu'ils servent ou pas.
eric

PS après relecture : je pensais les créer une fois pour toute.

Citation:

---

Des types d'atelier (usinage ; perçage) => Celles ci ne changeront jamais

---

De toute façon je ne vois toujours pas l'intérêt de changer de nom si la plage ne change pas. Il n'y a qu'à l'appeler liste1 qq soit le contenu de A1 puisque ça reste la même chose indépendamment du contenu de A1

Citation:

---

Envoyé par eriiic

[...]

De toute façon je ne vois toujours pas l'intérêt de changer de nom si la plage ne change pas. Il n'y a qu'à l'appeler liste1 qq soit le contenu de A1 puisque ça reste la même chose indépendamment du contenu de A1

---

Elles doivent être en cascade. Tu choisis une valeur dans la première liste (alimentée par A2:Ax) et la deuxième t'offre alors les items de B2:Bx ou C2:Cx... en fonction du choix de la première. Les plages B2:Bx, C2:Cx... doivent alors avoir un nom identique à ce qui a été choisi dans la première. Faut suivre le fil, hein... ;)

Perso, j'ai une autre technique, car je n'aime pas bloquer mes listes sur la structure des noms...