[VBA] - Range pour une liste déroulante

Bonjour à tous,

Voici un petit problème dont je n'arrive pas à me sortir... A partir de VBA, je cherche à créer une fonction qui récupère une liste d'éléments dans une feuille et ensuite à l'utiliser de deux manières :

- afficher cette liste dans une colonne d'une autre feuille

- utiliser cette liste dans une liste déroulante de validation de données

Code ci-dessous :

Function PHASES_ETUDES(Projet As String) 'renvoie la liste des phases d'étude du projet sous la forme d'un tableau à une dimenssions

Application.Volatile 'le résultat est recalculé à chaque fois qu'une cellule est modifiée

Dim LeBonRange As Variant
Dim Lign       As Long
Dim Col0       As Long
Dim Colx       As Long

'Lign = position du projet en question au sein de la matrice
    Lign = Application.Match(Projet, Range("_PROJET_LISTE_Projets"), 0)
'Valeur à laquelle on ajoute la ligne de départ de la plage nomée. On obtient le numéro de ligne au sein de la feuille + 1, donc on soutrait pour rester sur la ligne des phases
    Lign = Lign + Range("_PROJET_LISTE_Projets").Cells(1).Row - 1
'Col0 = colonne de départ de la période (études, chantier ou missions complémentaires)
    Col0 = Range("_PROJET_LARG_Etudes").Cells(1).Column
'Colx = la dernière cellule non vide de la bonne ligne, au sein des colonnes de la période, afin d'éliminer les valeurs nulles en fin de liste
    Dim Col9 As Long
    Col9 = Range("_PROJET_LARG_Etudes").Columns.Count
    Colx = Range(Cells(Lign, Col0), Cells(Lign, Col0 + Col9)).Find("*", , , , , xlPrevious).Column

    LeBonRange = Range(Cells(Lign, Col0), Cells(Lign, Colx))

'Renvoyer le résultat sur une colonne
PHASES_ETUDES = Application.Transpose(LeBonRange)
'PHASES_ETUDES = Range(Cells(Lign, Col0), Cells(Lign, Colx)).Address '--> j'ai essayé de renvoyer l'adresse, mais çà ne change rien

End Function

En gros tout fonctionne, sauf la validation de données (que je fait à partir d'excel et non en VBA, mais je peux apprendre).

Dans la validation / Liste j'ai essayé de mettre

"=Phases_Etudes("A14")" mais il me dit qu'il ne connait pas ce nom...

"=test" avec test comme nom défini comme ceci "=Phases_Etudes("A14")" mais il me dit qu'il faut que la liste n'ai qu'une ligne ou qu'une colonne. Ce qui est pourtant le cas...

Si vous avez des idées, je suis preneur ! Merci à vous !

Bonsoir,

Peut-être avec :

=Phases_Etudes!A14

Salut, et merci d'avoir pris le temps de tout lire... !

Effectivement j'ai pas fait gaffe, mais évidemment pas de guillemets, je fais la manip en cliquant sur excel du coup au moment d'écrire mon post j'ai pas fait attention, mea culpa ... en cliquant çà donne : =PHASES_ETUDES($A$14), et "impossible de trouver une plage nommée que vous avez spécifié".

=Phases_Etudes!A14 renvoi "impossible d'utiliser des références à d'autres classeurs comme validation de données".

Ma fonction renvoi un range, à une dimension certes, mais un range quand même, qui pourrait très bien être à deux dimensions... J'ai l'impression qu'en voyant çà arriver, la validation de données dit que non, si çà à le droit d'avoir deux dimensions, j'en veux pas"...

Sinon peut-être en renvoyant une matrice ? Genre avec une boucle faire une grande matrice à une dimension. J'ai essayé avec des tableaux sans faire fonctionner mon code, pour le moment...

Ce sont mes deux autres pistes, mais je préfèrerai faire fonctionner celui-ci avec un Cvrt ou une autre manière de renvoyer le résultat, histoire de rester au plus léger... M'enfin pour l'instant çà ne marche pas alors...

Edit : Fichier joint, je viens de voir que j'avais oublié de le joindre !

Petite évolution...., en arrière. La fonction Phases_Etudes va chercher des infos dans la feuille "Projets" pour les ressortir n'importe où dans le classeur. J'ai donc ajouté Worksheets("Projets") pour les lignes où le Range ne fait pas référence à une plage nommée (qui donne de toutes façons dans le bonne feuille du coup) :

    Colx = Worksheets("Projets").Range(Cells(Lign, Col0), Cells(Lign, Col0 + Col9)).Find("*", , , , , xlPrevious).Column

    LeBonRange = Worksheets("Projets").Range(Cells(Lign, Col0), Cells(Lign, Colx))

Mais çà ne marche pas, la fonction ne reste utilisable que dans l'onglet "Projets", sinon elle me renvoie #Valeur ... Je commence à me demander si j'ai pas visé un peu haut avec cette fonction Ou alors va falloir acheter VBA pour les nuls vite fait...

Bonsoir

J'essaye de comprendre mais pas évident

Regardes la page extraction et dis si j'ai saisi

Bonjour, et merci de ta réponse.

Alors pour commencer, j'ai grâce à toi réglé mon erreur de syntaxe et la fonction marche sur toutes les feuilles. Merci pour çà ! Donc effectivement je peux afficher la colonne jaune où je veux. 8)

Pour la validation de données, le rendu de la cellule verte correspond exactement au but recherché, seulement en créant cette fonction je pensais pouvoir me passer d'une liste intermédiaire dans un coin de feuille. Par exemple pour une validation de données dans l'onglet "Saisie" dans la colonne "Phases", la validation de données pourrait être quelque chose du genre "=Phases_Etudes($A25)". Maix excel n'a pas l'air d'aimer les formules en validation de données. Donc en passant par un nom genre "LaListe" avec LaListe=Phases_Etudes($A25), mais là excel à peur d'avoir un tableau à plusieurs dimensions...

Je me demandait si il n'y avait pas moyen de faire renvoyer à la fonction un tableau à une dimenssions dans un format acceptable par la validation de données, plutôt que de renvoyer un range, mais je ne suis pas encore arrivé à faire marcher quoique ce soit...

Je vais voir si mtnt que la fonction tiens plus la route si j'arrive à faire quelque chose, et merci encore !!!

Bon, je crois que çà va mieux...

Function PHASES_ETUDES(Projet As String, Adresse_Valeurs As Boolean) 'renvoie la liste des phases d'étude du projet sous la forme d'un tableau à une dimenssions

Application.Volatile 'le résultat est recalculé à chaque fois qu'une cellule est modifiée

Dim Lign       As Long
Dim Col0       As Long
Dim Colx       As Long
Dim Col9       As Long

'Lign = position du projet en question au sein de la matrice
    Lign = Application.Match(Projet, Range("_PROJET_LISTE_Projets"), 0)
'Valeur à laquelle on ajoute la ligne de départ de la plage nomée. On obtient le numéro de ligne au sein de la feuille + 1, donc on soutrait pour rester sur la ligne des phases
    Lign = Lign + Range("_PROJET_LISTE_Projets").Cells(1).Row - 1
'Col0 = colonne de départ et de fin de la période (études, chantier ou missions complémentaires)
    Col0 = Range("_PROJET_LARG_Etudes").Cells(1).Column
    Col9 = Range("_PROJET_LARG_Etudes").Columns.Count

With Worksheets("Projets")
    'Colx = la dernière cellule non vide de la bonne ligne, au sein des colonnes de la période, afin d'éliminer les valeurs nulles en fin de liste
    Colx = .Range(.Cells(Lign, Col0), .Cells(Lign, Col0 + Col9)).Find("*", , , , , xlPrevious).Column

    If Adresse_Valeurs = False Then
    'On renvoie l'adresse de la bonne plage
    PHASES_ETUDES = .Range(.Cells(Lign, Col0), .Cells(Lign, Colx)).Address
    Else
    'Renvoyer le résultat sur une colonne
    PHASES_ETUDES = Application.Transpose(.Range(.Cells(Lign, Col0), .Cells(Lign, Colx)))
    End If

End With

End Function

J'ai rajouté une argument à la fonction afin qu'elle renvoie soit les valeurs (pour faire l'équivalent de ta colonne jaune, l'ensemble des phases sur une colonne), soit l'adresse de la plage.

Le fait de rajouter la possibilité de renvoyer l'adresse permet d'utiliser la fonction dans la validation de données en passant par une Indirect(Phases_Etudes("projet")).

çà rajoute quelques intermédiaires mais çà marche ! Du coup si quelqu'un a plus simple (sans rajouter d'argument par exemple) je prend, sinon je marquerai le sujet comme résolu... En tous cas Merci Banzai par ce que je bloquais bêtement sur de la syntaxe....

Bonjour tout le monde,

je reviens à la charge. La solution décrite plus haut contourne le problème sans vraiment le résoudre, et çà me bloque par la suite (ou il j'aimerai "concatener" ces adresses) Je m'explique, la validation de données excel accepte soit des valeurs (val1;val2val3), soit un nom faisant référence à une adresse simple (=PlageUnique).

N'y a-t-il pas moyen de lui rentrer un nom faisant référence à des valeurs et non à une adresse ? Genre =NomVal avec NomVal = val1;val2;val3 (ce qui était mon objectif en début de post, mais même en saisissant NomVal = "test", il me renvoie la fameuse "la liste source doit être délimitée ou faire référence à une seule ligne ou colonne")

Ou alors à defaut, un nom faisant référence à plusieurs plages ? Genre =Plages avec Plages = A1:C1 ; F1:G1

Ci joint un fichier ultra-simplifié à deux onglets : l'objectif est d'avoir le rendu de la liste de validation faite à la main dans la cellule bleue, mais en faisant référence aux cellules vertes de l'onglet projet... Le tout sans plage intermédiaire si possible puisque je veux pouvoir appliquer cette liste de validation en fonction de chaque projet (a, b, c, etc...) et que je ne vais pas me créer une plage intermédiaire par projet... Mais cette seconde partie ne devrait pas poser de pb si la première est résolu. Je suis ouvert à tout, VBA ou pas VBA...

Je continuerai de m'auto spammer si j'avance, je me dit que çà évite aux autres de plancher pour rien, et que çà servira peut-être pour la postérité...