Comment modifier la portée d’une plage nommée en vba

Version imprimable

Bonjour,

Quand on créer une plage nommée par le Gestionnaire de noms il offre la possibilité de spécifier la portée pour le Classeur ou [worksheet name]. Mais si ensuite je désire modifier l'étendue, la liste déroulante est grisée. Et dans ce cas il faut supprimée le nom avec étendue classeur et créer un nouveau avec l’étendue [worksheet name] et la même référence.

Bien sûr si il faut modifier qu'une seule portée aucun intérêt de faire un macro, par contre dans le cas de plusieurs, voici ma petite macro qui permet de modifier l’étendue [worksheet name] en étendue classeur de tous les noms de la feuille active :
Code:

1 2 3 4 5 6 7 8 9 10 Sub ModifÉtenduNomsFeuille() Dim Nom, Feuille, RangeRefersTo For Each N In ActiveSheet.Names 'tous les noms de la feuilles actives Nom = Mid(N.Name, InStr(N.Name, "!") + 1) Feuille = Left(N.RefersTo, InStr(N.RefersTo, "!") - 1) RangeRefersTo = "" & N.RefersTo & "" ActiveWorkbook.Names(N.Name).Delete ThisWorkbook.Names.Add Name:=Nom, RefersTo:=RangeRefersTo Next N End Sub
Mais comment vérifier que le nom existe et faire l’inverse c’est-à-dire modifier l’étendue classeur en étendue [worksheet name].

J’ai passé beaucoup temps et malgré mes recherches je n’ai pas pour trouver solution.

Merci d’avance pour vos aides er réponses.

30/08/2023, 15h28
Philippe Tulliez
Bonjour,

Citation:

Mais comment vérifier que le nom existe

Il y a deux possibilités, soit on parcoure l'ensemble des noms dans la collection Names soit on passe par une instruction qui provoque une erreur si l'objet dans la collection n'existe pas.

Exemple
Code:

1 2 3 4 5 6 7 8 Function IsNameExist(Name As String) As Boolean Dim n As Name Dim f As Integer On Error Resume Next f = Len(Names(Name).Name) On Error GoTo 0 IsNameExist = (f > 0) End Function
Exemple de son invocation
Code:

1 2 3 4 Sub TestIsNameExist() Debug.Print IsNameExist("MonNom") ' Portée classeur Debug.Print IsNameExist("Feuil1!myName") ' Portée feuille End Sub
Pour l'étendue feuille, il faut évidemment savoir dans laquelle se trouve le nom.

Citation:

et faire l’inverse c’est-à-dire modifier l’étendue classeur en étendue [worksheet name].

Oui mais dans quelle feuille ?
Sachant que rien n'empêche d'avoir le même nom sur plusieurs feuilles et également en portée classeur.
30/08/2023, 18h41
modus57

Bonjour Philippe Tulliez,

Merci beaucoup pour votre réponse, passer par une instruction qui provoque une erreur si l'objet dans la collection n'existe pas convient parfaitement.

Pour l'étendue feuille, il s'agit de la feuille active dans laquelle il me semble le nom doit être unique.

@+

Bonjour,

Sans autres réponses à cette discussion je me suis dis que la solution c’était peut-être de créer tous les noms étendue feuille avec ce bout de code et ensuite de supprimer les noms avec étendu classeur :
Code:

1 2 3 4 5 6 7 8 Sub ModifÉtenduNomsClasseurFeuille2() Dim N, Nom, NomFeuille For Each N In ThisWorkbook.Names 'tous les noms du classeur actif Nom = Mid(N.Name, InStr(N.Name, "!") + 1) NomFeuille = Split(Replace(N.RefersTo, "=", "!"), "!")(1) 'Extraire une chaine entre 2 caractères spéciaux ActiveWorkbook.Worksheets("" & NomFeuille & "").Names.Add Name:="" & Nom & "", RefersTo:="" & N.RefersTo & "" 'ajoute les noms étendue feuille Next N End Sub
J’ai essayé avec cette ligne qui en principe doit supprime les noms étendue classeur mais en fait ça supprime les noms étendue feuille :

Code:

ActiveWorkbook.Names("" & Nom & "").Delete

Pour le moment je patauge !

En espérant obtenir une réponse @+.

Bonsoir,

Finalement j'ai trouvé une solution pour modifier les noms étendue classeur en noms étendue feuille pour la feuille active :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub ModifÉtenduNomsClasseurFeuille()
    Dim N, Nom, NomFeuille, Ref
    For Each N In Application.Names
        'ici on verifie que la plage nommée appartient bien a la feuille active
        If ActiveSheet.Name = N.RefersToRange.Parent.Name Then
            Nom = Mid(N.Name, InStr(N.Name, "!") + 1)
            NomFeuille = Split(Replace(N.RefersTo, "=", "!"), "!")(1) 'Extraire une chaine entre 2 caractères spéciaux
            Ref = N.RefersTo
            ActiveWorkbook.Names("" & Nom & "").Delete
            ActiveWorkbook.Worksheets("" & NomFeuille & "").Names.Add Name:="" & Nom & "", RefersTo:=Ref 'ajoute les noms étendue feuille"
        End If
    Next N
End Sub

Serte ce n'est pas la meilleure si il en existe d'autre je suis preneur.

Merci & @+

Bonjour à tous, :D

Et voici la solution pour modifier les noms étendue Feuille en noms étendue Classeur pour la feuille active :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub ModifÉtenduNomsFeuilleClasseur()
    Dim N, Nom, NomFeuille, Ref
    For Each N In Application.Names
        'ici on verifie que la plage nommée appartient bien a la feuille active
        If ActiveSheet.Name = N.RefersToRange.Parent.Name Then
            Nom = Mid(N.Name, InStr(N.Name, "!") + 1)
            NomFeuille = Split(Replace(N.RefersTo, "=", "!"), "!")(1) 'Extraire une chaine entre 2 caractères spéciaux
            Ref = N.RefersTo
            ActiveWorkbook.Names("" & Nom & "").Delete
            ActiveWorkbook.Names.Add Name:="" & Nom & "", RefersTo:=Ref 'ajout les noms étendue Classeur"
        End If
    Next N
End Sub

Si certain d'entre vous ont une autre solution, ce sera un plaisir de lire une réponse.