Discussion :

[Joris LEMONDE]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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,

[unparia]

Bonjour

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

thisworkbook.names("toto").delete

[Joris LEMONDE]

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 ?

[Pierre Fauconnier]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[eriiic]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

(ou l'équivalent en vba)
eric

[Joris LEMONDE]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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

Sélectionner tout - Visualiser dans une fenêtre à part

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

Ma réponse ne te convient pas?

[Joris LEMONDE]

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

[Pierre Fauconnier]

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

[Joris LEMONDE]

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é)...

[Pierre Fauconnier]

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.

[Pierre Fauconnier]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[eriiic]

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

[Pierre Fauconnier]

Salut Eric,

[...]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.

[Joris LEMONDE]

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.

[Joris LEMONDE]

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

[eriiic]

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

[Pierre Fauconnier]

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

[eriiic]

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.

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

[Pierre Fauconnier]

[...]

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