Discussion :

[Papertiger]

Hello, j'aimerais pouvoir récupérer en VBA le nom d'une plage de cellules en cliquant sur une seule cellule Excel.

J'ai défini 2 zones de cellules fusionnées:

Code :

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

1
2
    Range("B1:E1").Merge
    Range("A2:F2").Merge

Puis j'ai défini 1 seul nom pour l'ensemble de ces 2 zones :

Code :

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

1
2
    Range("B1:E1,A2:F2").Select
    ActiveWorkbook.Names.Add Name:="gaga", RefersToR1C1:="=Feuil1!R1C2,Feuil1!R2C1"

Ceci me permet par exemple de changer la couleur de toute la zone facilement :

Code :

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

Range("gaga").Interior.Color = RGB(255, 0, 0)

Lorsque je clique sur une cellule de cet ensemble, je peux récupérer l'adresse d'une ou de quelques cellule(s) de cet ensemble :

Code :

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

1
2
3
4
5
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      MsgBox Target.Address
' ou bien
      MsgBox ActiveCell.Address
End Sub

Ceci me donne un résultat du genre $A$2:$F$2 ou $B$1.

Mais ce que j'aimerais vraiment récupérer, c'est le nom de cet ensemble de cellules, ici "gaga" (par exemple pour changer sa couleur comme expliqué juste avant) ce que je ne sais malheureusement pas faire.

Quelqu'un a une idée ? (si possible simple et rapide car il y a une centaine de ces noms de définis sur des cellules différentes...)

[Pierre Fauconnier]

Salut.

On a deux soucis avec les noms qui pointent vers des plages. Je ne comprends pas pourquoi Microsoft a programmé cela ainsi, mais on est bien obligé de faire avec. Normalement, la propriété RefersToRange renvoie vers un objet Range mais:

1. Si le nom ne renvoie pas vers une plage, il est impossible de manipuler RefersToRange. Perso, j'aurais apprécié que dans ce cas, ReferstoRange renvoie Nothing;
2. Si la plage à laquelle le nom fait référence est multizones, il est également impossible de manipuler RefersToRange. Je ne comprends pas pourquoi RefersToRange ne renvoie pas la plage, car ce serait parfaitement possible.

Quoi qu'il en soit, on est bien obligé de faire avec ce qui est pour moi un problème de conception de cette propriété, et je ne ne vois personnellement pas d'autre possibilité que de devoir gérer l'erreur qui survient en cas de tentative de manipulation d'un RefersTorange invalide.



Je proposerais donc le code suivant, mais il y a peut-être moyen de mieux l'écrire. On pourrait tester le numéro d'erreur, mais je ne vois pas trop ce que cela apporterait dans ce cas. On passe un range à la fonction et elle renvoie soit le nom de la plage nommée soit une chaine vide. Il suffit qu'une cellule du range passé en argument soit dans la plage nommée pour récupérer le nom.

Code :

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

1
2
3
4
5
6
7
8
9
10
Function getName(Target As Range) As String
  Dim i As Long: i = 1
 
  On Error GoTo catch
  Do While i <= Names.Count And getName = ""
    If Not Intersect(Target, Range(Replace(Names(i).RefersTo, "=", ""))) Is Nothing Then getName = Names(i).Name
catch:
    i = i + 1
  Loop
End Function

[EDIT]: Je suis curieux de savoir à quoi cela peut servir...

[ARTURO83]

Bonjour,

Une proposition, on établit en premier lieu, à l'aide d'une macro, la liste de toutes les zones de noms, (la liste est créée dans la feuille "Feuil2").
Puis, sur la feuille "Feuil1", la sélection d'une des cellules d'une zone de noms renvoie le nom de la zone à laquelle fait partie cette cellule.
Important: Si les cellules doivent être fusionnées, il est impératif de déclarer les zones de noms avant la fusion des cellules, sinon seule la première cellule sera enregistrée dans la liste au lieu de la plage complète.

Exemple ici sur la feuille "Feuil1", avec 3 zones de noms distinctes et de formes différentes.
La première fois, on clique sur le bouton vert pour relever la liste de toutes les zones de noms, ensuite la sélection d'un des cellules de la feuille 1 renverra le nom de la zone sélectionnée.
Pièce jointe 593942

le code

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
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
    Dim f1 As Worksheet, f2 As Worksheet
    Dim Lig As Long
    Dim Nom As Variant
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim DerLig_f2 As Long, i As Long
    Set f2 = Sheets("Feuil2")
    DerLig_f2 = f2.Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To DerLig_f2
        If Not Intersect(Target, Range(f2.Cells(i, "A"))) Is Nothing Then
            MsgBox "Nom de la zone sélectionnée = " & f2.Range("B" & i).Value
            Exit Sub
        End If
    Next i
    Set f2 = Nothing
End Sub
 
Sub Releve_Zones_de_noms()
    Application.ScreenUpdating = False
    Set f1 = Sheets("Feuil1")
    Set f2 = Sheets("Feuil2")
    f2.Cells.ClearContents
    Lig = 1
    For Each Nom In ActiveWorkbook.Names
        f2.Cells(Lig, "A") = Replace(Replace(Nom, "Feuil1!", ""), "=", "")
        f2.Cells(Lig, "B") = Nom.Name
        Lig = Lig + 1
    Next Nom
    f2.Select
    Set f1 = Nothing
    Set f2 = Nothing
End Sub

Cdlt

[Papertiger]

C'est bien ce que je craignait : devoir passer par des boucles de tests.

Merci beaucoup, c'est implémenté.
C'est pas très rapide, mais ça fonctionne.

[Pierre Fauconnier]

Pour mon info, c'est quoi la finalité?