Discussion :

[modus57]

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 :

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

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.

[Philippe Tulliez]

Bonjour,

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 :

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

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 :

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

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.

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.

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

@+

[modus57]

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 :

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

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 :

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

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

Pour le moment je patauge !

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

[modus57]

Bonsoir,

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

Code :

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

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 & @+

[modus57]

Bonjour à tous,

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

Code :

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

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.