Trouver le nom d'une plage nommée

Bonjour,

je cherche à connaître le nom de la plage nommée dans laquelle je me situe. Pour faire ça, j'utilise le code :

    Dim nomListe As String, ListeR As Range

    Set ListeR = Range(Cells(2, Target.Column), Cells(Target.Cells.End(xlDown).Row, Target.Column))

    nomListe = ListeR.Name.Name

Si ma liste Excel est définie de façon fixe, mon code fonctionne, par exemple :

[MaListeExcel]=Listes!$A$2:$A$6 (=nomListe)

Par contre si ma liste Excel est définie en utilisant la fonction DECALER(), je génère une erreur d'éxécution 1004 (erreur d'éxécution 1004 définie par l'application ou par l'objet):

[MaListeExcel]=DECALER(Listes!$A$2;0;0;NBVAL(Listes!$A:$A)-1)

Vous auriez une idée,

Merci beaucoup

Bonjour,

voilà un code à mettre en remplacement du début de ton code actuel.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim nomListe As String, avant As String, apres As String, plage As Range, Liste As Name
    Dim pl As Range, c As Range, nb As Long
    Dim cel As Range
    If Target.Row = 1 Then Exit Sub
On Error Resume Next
For Each Liste In ThisWorkbook.Names
    Set plage = Liste.RefersToRange
        For Each cel In plage
            If cel.Address = Selection.Address Then
                nomListe = Liste.Name
            End If
        Next cel
Next Liste
MsgBox nomListe

A plus !

Et bien écoutes, ça marche génialement bien même si je ne comprends pas trop ton code.

D'habitude, je préfère faire des choses "moins belles" mais que je comprends mais là, je ne vais pas faire la fine bouche!!!

Merci encore!

Bonjour,

Un test pour détecter la plage nommée dans laquelle se trouve Target.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim nomListe As Name
    If Target.Row = 1 Then Exit Sub
    For Each nomListe In ThisWorkbook.Names
        If Not Intersect(Target, Range(nomListe.Name)) Is Nothing Then
            MsgBox nomListe.Name
            Exit For
        End If
    Next nomListe
End Sub

J'y crois pas. J'ai essayé dans tous les sens de faire un truc du genre mais impossible de faire un truc qui marche...

Merci Mferrand

(re)

Je suis désolé mais j'ai un petit problème d'adaptation dans mon fichier global où il y a des listes dans tous les sens. Le code s'arrête à une liste filterdatabase! qui vient a priori des filtres (mais je n'en suis pas sûr) mais ce n'est pas ça que je cherche...

Il faudrait donc que je limite ma recherche des .Names aux seules plages que j'ai "vraiment" créées ou alors que je limite ma recherche aux listes existantes dans ma sheet active et pas à celles existantes dans tout le workbook

J'ai essayé de remplacer ce code

For Each nomListe In ThisWorkbook.Names

qui me référence bien toutes mes plages mais aussi ces filterdatabase qui (a priori) me bloquent

par

For Each nomListe In Me.Names

qui ne me référence rien du tout...

Quelle est mon erreur?

Les noms sont dans le classeur.

Il faut mettre la recherche sous gestion d'erreurs si tu as des noms qui ne sont pas des plages.

D'accord. Comment peut on faire ça?

Par exemple :

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim nomListe As Name
    If Target.Row = 1 Then Exit Sub
    On Error Resume Next
    For Each nomListe In ThisWorkbook.Names
       If Not Intersect(Target, Range(nomListe.Name)) Is Nothing Then
            If Err.Number <> 0 Then
                Err.Clear: GoTo Suite
            End If
            MsgBox nomListe.Name
            Exit For
        End If
Suite:
    Next nomListe
    On Error GoTo 0
End Sub

Merci MFerrand et Braters,

mais quand je sors de mon petit fichier exemple, je n'arrive pas à faire quelque chose qui marche. Vous trouverez éventuellement mon fichier réel en pièces jointes avec dans l'onglet "parametres" le code suivant :

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

'Code pour modifier dans toutes les feuilles les libelles de listes qui ont ete modifies

    Dim nomListe As String, avant As String, apres As String, plage As Range, Liste As Name
    Dim pl As Range, c As Range, nb As Long
    Dim cel As Range
    If Target.Row = 1 Then Exit Sub
On Error Resume Next

        'Trouver la plage nommee dans laquelle on se situe
    For Each Liste In ThisWorkbook.Names

    If InStr(1, Liste.Name, "_FilterDataBase", vbTextCompare) = 0 And InStr(1, Liste.Name, "_xlfn", vbTextCompare) = 0 Then
        If Not Intersect(Target, Range(Liste.Name)) Is Nothing Then
            nomListe = Liste.Name
            MsgBox nomListe
            Exit For
        End If
    End If
    Next Liste

'MsgBox nomListe

        'Trouver la valeur precedente et la nouvelle valeur
    apres = Target.Value
    If apres = "" Then Exit Sub
    Application.EnableEvents = False
    Application.Undo
    avant = Target.Value
    Target = apres
    Application.EnableEvents = True

        'Remplacer dans chaque feuille les cellules controlees par une liste de validation l ancienne valeur par la nouvelle valeur

    Dim Ws As Worksheet

    For Each Ws In Sheets

    MsgBox Ws.Name

        Set pl = Ws.Cells.SpecialCells(xlCellTypeAllValidation)
        If Not pl Is Nothing Then
            For Each c In pl
                If c.Validation.Formula1 = "=" & nomListe Then
                    If c.Value = avant Then c = apres
                End If
            Next c

        End If
    Next Ws
End Sub

Dans l'onglet parametres, si vous modifiez un libellé, celui-ci devrait donc être modifié dans les feuilles du classeur où il est utilisé.

Par exemple, si vous modifiez "ATOL LES OPTICIENS" en Parametres.F4 par "ATOL OPTICIENS", on devrait voir le changement dans l'onglet Direct notamment en B18, B19, B20.

La liste dans laquelle on se situe lorsqu'on modifie ce libellé est la liste [Param_Regroupement] mais ce n'est pas celle-ci qui est identifiée mais [Adbook_Liste_structures], je ne sais pas pourquoi... il s'agit par contre dans l'ordre alphabétique de la première des plages nommées dans le classeur...

Merci d'avance pour votre aide éventuelle

Après des tests pour identifier le problème, c'est le "Intersect" qui donne un résultat erroné. Dés le premier nom existant de plages, la macro continue même si ce n'est pas la bonne plage...

Après examen (il a fallu trouver !! ) je ne vois pas ce que viens faire le If Instr... mais son effet doit être nul (on ne peut voir, le cas n'apparaît pas.

Tu mets bien la boucle sous gestion d'erreur, mais tu ne la gères pas, et tu ne l'annules pas en sortie de boucle... Or si tu ne gères pas, tu exécutes en cas d'erreur les instructions prévues lorsque la condition est satisfaite et tu sors de la boucle, alors que la gestion d'erreur est là justement pour sauter ces instructions et poursuivre la boucle.

En la rétablissant, je note qu'il se produit nombre d'erreurs (27 je crois) avant d'arriver à un Vrai sur la condition et le message de contrôle ne s'affiche qu'une fois.

J'ai limité mon examen exclusivement à la boucle...

Ensuite si tu n'annules pas la gestion d'erreur, tu ne verras pas les erreurs éventuelles dans la suite du code, et tu risques d'avoir des effets inattendus...

Cordialement.

Merci, je comprends ce problème de gestion d'erreur qui entraîne des conséquences inattendues.

Mais je ne sais pas comment faire. Le Range(Liste) entraîne une erreur de type "La méthode Range de l'objet Worksheet a échoué" alors que Liste est bien identifée par sa plage de cellules dans Excel (plage de cellules toutes de type DECALER())

    Dim nomListe As String, avant As String, apres As String, plage As Range, Liste As Name
    Dim pl As Range, c As Range
    Dim cel As Range
    If Target.Row = 1 Then Exit Sub

        'Trouver la plage nommee dans laquelle on se situe
    For Each Liste In ThisWorkbook.Names
                'Eliminer les noms de liste correspondant à des filterdatabase
    'MsgBox "les coordonnees sont " & Target.Cells.Row & "-" & Target.Cells.Column

    If InStr(1, Liste.Name, "_FilterDataBase", vbTextCompare) = 0 And InStr(1, Liste.Name, "_xlfn", vbTextCompare) = 0 And Liste.Name = "Param_Regroupement" Then

        If Not Intersect(Target, Range(Liste)) Is Nothing Then
            nomListe = Liste.Name
            MsgBox "La liste selectionnee est " & nomListe
            Exit For
        End If
    End If
    Next Liste

Bonsoir,

Reprenons calmement :

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim nomListe As String, avant As String, apres As String, plage As Range, Liste As Name
    Dim pl As Range, c As Range, cel As Range
    If Target.Row = 1 Then Exit Sub
    'Trouver la plage nommee dans laquelle on se situe
    On Error Resume Next
    For Each Liste In ThisWorkbook.Names
        If Not Intersect(Target, Range(Liste.Name)) Is Nothing Then
            If Err.Number <> 0 Then
                Err.Clear: GoTo Suivant
            End If
            nomListe = Liste.Name
            Exit For
        End If
    End If
Suivant:
    Next Liste
    'Vérification
    MsgBox "La liste selectionnee est " & nomListe
    Exit Sub 'provisoire
    'suite proc.
End Sub

Cordialement

Merci beaucoup MFerrand. Avec Vba, en se posant des questions qu'on imagine simples, on se retrouve dans des problématiques compliquées . Heureusement qu'il existe des sachants sympas comme toi.

Malheureusement, ton code me sort toujours la première liste dans l'ordre alphabétique, soit exactement ce que j'avais déjà comme problème. J'ai juste apporté une micro correction dans ton code, il y a apparemment un end if en trop.

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim nomListe As String, avant As String, apres As String, plage As Range, Liste As Name
        Dim pl As Range, c As Range, cel As Range
        If Target.Row = 1 Then Exit Sub
        'Trouver la plage nommee dans laquelle on se situe
       On Error Resume Next
        For Each Liste In ThisWorkbook.Names
            If Not Intersect(Target, Range(Liste.Name)) Is Nothing Then
                If Err.Number <> 0 Then
                    Err.Clear: GoTo Suivant
                End If
                nomListe = Liste.Name
                Exit For
            End If
        'End If
    Suivant:
        Next Liste
        'Vérification
       MsgBox "La liste selectionnee est " & nomListe
        Exit Sub 'provisoire
       'suite proc.
    End Sub

Un oubli en effet... désolé ! Mais le code étant indenté, cela se voit tout de suite : un End If n'a rien à faire sur l'alignement For...Next.

Ceci étant quelques essais sur ton fichier de cette procédure me montre que à chaque édition-revalidation d'une cellule de ta feuille paramètres, le message me renvoie bien le nom de la plage dans laquelle j'ai édité la valeur !

Et si j'opère en dehors d'une plage, je n'ai évidemment pas de nom dans le message.

Cordialement.

Merci beaucoup M. Ferrand pour ton aide.