(RESOLU) Comment améliorer mon code pour définition de « NOM » pour créer des listes

[corvelc]

Bonjour,
J’utilisais manuellement la définition de « NOM » pour créer des listes sur une plage de données qui peut évoluer en nombre de lignes. (options Excel : FORMULE => Définir un nom).
Je voudrais maintenant le faire mais via une macro pour intégrer dans une petite application.
Pour cela j’ai utilisé «enregistrer une macro » mais j’ai quelques soucis d'éxecution.
- Plantage si effacement de la liste si pas déjà créée
- Comment faire pour définir la plage si le nombre de lignes évolue en + ou –
Merci pour votre aide ou vos conseils

Mes codes ci dessous :

Sub Mac_creation_liste()
' Ma Macro
' pour effacer la liste
ActiveWorkbook.Names("Teffectifs").Delete
'*** comment tester si la liste n'existe pas
'*** pour ne pas planter ?
' création de la liste sur feuil1
Sheets("Feuil1").Select
ActiveWorkbook.Names.Add Name:="Teffectifs", RefersToR1C1:= _
"=Feuil1!R2C1:R983C11"
'**** comment definir la zone selon la plage de données
'**** si la plage change en nombre de ligne ?
End Sub

 

[Efgé]

Re : comment améliorer mon code pour définition de « NOM » pour créer des listes

Bonjour corvelc
Une proposition :
Inutile de supprimer le nom avant de le changer.
Pas besoin d'être sur la feuille concernée.
Ne reste plus que:

Sub Mac_creation_liste_2()
Dim LstRw As Long
'Récupération du numéro de la dernière ligne remplie en colonne A
LstRw = Sheets("Feuil1").Cells(Application.Rows.Count, 1).End(xlUp).Row
ActiveWorkbook.Names.Add Name:="Teffectifs", RefersToR1C1:= _
"=Feuil1!R2C1:R" & LstRw & "C11"
End Sub

Cordialement

 

[Misange]

Re : comment améliorer mon code pour définition de « NOM » pour créer des listes

Bonjour
Puisque tu utilises excel 2007, pourquoi ne pas tout simplement utiliser la fonctionnalité intégrée des tableaux excel (onglet accueil/style/mettre sous forme de tableau) ?
Ceci crée automatiquement dans excel des noms qui s'ajustent tout seul au fur et à mesure que tu ajoutes ou que tu enlèves des données dans ta colonne. Si ton tableau s'appelle tableau1 et que l'entête de ta colonne s'appelle Truc, tu fais référence à l'ensemble des données de cette colonne avec tableau1[truc]
Tu peux utiliser ces noms dans les listes de validation . Il faut juste ajouter un indirect :
=indirect("tableau1[truc]")

Tu peux aussi créer des noms plus courts ou plus sympas à partir des tableaux (en gardant le côté dynamique): clic droit en haut de la colonne pour en sélectionner le contenu (sans l'en tête). Onglet formules/définir un nom. Dans la zone de noms tu mets ce que tu veux (par exemple "bidule") et en bas excel a déjà écrit tout seul = tableau1[truc]. Tu valides c'est tout.
Pour utiliser ce nom dans une liste de validation c'est l'appel habituel :
= bidule

Gros avantage de cette méthode, tu peux déplacer ton tableau où tu veux sur ta feuille, en mettre plusieurs, les changer de feuille, peu importe, ça suit et ça s'adapte.
Plus d'infos sur les tableaux et leurs multiples avantages :
Ce lien n'existe plus

 

[corvelc]

Re : comment améliorer mon code pour définition de « NOM » pour créer des listes

Merci pour ton aide Efgé

Questions :

1 - dans le code les 2 "Feuil1" c'est bien le nom de l'onglet ou se trouvent mes données ?

2- j'ai utilisé ton code , mais après execution, je ne vois plus le nom de ma liste lorsque je selectionne, dans la barre de tache Excel, la zone ou s'affiche la position de la dernière cellule selectée (en haut à gauche liste deroulante) . Idem quant j'utilise FORMULE=> Gestionnaire des noms, le nom de la liste n'apparait pas.

CC

 

[corvelc]

Re : comment améliorer mon code pour définition de « NOM » pour créer des listes

Réponse pour MISAGE.

les données sont alimentées par une macro , et le tableau change tout le temps en nombre de ligne en + ou en - (jamais en nombre de colonne). Puis-je faire tout de même ?

CC

 

[Efgé]

Re : comment améliorer mon code pour définition de « NOM » pour créer des listes

Re
Bonjour Misange

@corvelc
Sans fichier exemple, au format .xls, je ne pourrais aller plus loin.
Je peux t'assurer qu'il y a bien un nom créer (Teffectifs) en rapport avec la plage Feuil1!$A$2:$K1000 (1000 par exemple, tous dépend du nombre de valeur dans la colonne A).
Si tu lances le code sans qu'il y ai de données après la ligne 2 de la colonne A, il ne se passera rien bien entendu.

Cordialement

 

[Misange]

Re : comment améliorer mon code pour définition de « NOM » pour créer des listes

Réponse pour MISAGE.

les données sont alimentées par une macro , et le tableau change tout le temps en nombre de ligne en + ou en - (jamais en nombre de colonne). Puis-je faire tout de même ?

CC

Oui tout à fait c'est tout l'intérêt des tableaux justement
Si tu veux manipuler les tableaux par macro tu peux aussi regarder cette page
Ce lien n'existe plus

Si tu es réfractaire aux tableaux (ce serait très dommage mais j'ai pu remarquer qu'à ma grande incompréhension c'est le cas pour certains !), tu peux aussi utiliser la vieille méthode qui consite à nommer les plages de façon dynamique avec la fonction decaler
Ce lien n'existe plus
Decaler : une fonction vraiment indispensable. | Bienvenue sur Excelabo | www.excelabo.net

Signé misage miturbulente

 

[corvelc]

Re : comment améliorer mon code pour définition de « NOM » pour créer des listes

Merci à tous pour votre aide précieuse
je vais essayer avec vos conseils

A plus sur le site

CC