VBA - Liste deroulante en cascade

Bonjour a tous,

J'ai un fichier excel avec un tableau et dans ce tableau je souhaite ajouter des lignes avec un format et des formules deja predifini. Le probleme c'est que pour une de c'est cellule, je souhaite mettre une liste deroulante dans une celulle (colonne G) qui va dependre de la valeur d'une autre celulle (colonne I). Je pensais en premier lieu utiliser le format R[-]C[-] mais celui ne semble pas adapter pour une liste deroulante.

Voila les 2 idees que j'avais en tete : (cell est defini au prealable par l'utilisateur pour lui indiquer ou ajouter cette ligne)

    'Cells text G : Object'
    With Range("G" & cell.Row - 1).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=INDIRECT(RC[2])"
    End With

Ou

    'Cells text G : Object'
    With Range("G" & cell.Row - 1).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=INDIRECT(""I"" & cell.Row -1)"
    End With

Voila egalement le fichier excel associe. Cette partie se trouve dans le module Addrows et le Sub Category.

Merci d'avance a celui ou celle qui saura m'aiguiller

Bonne journee

Bonjour,

Je ne suis pas sur qu'on puisse entrer une formule RC dans un champ de formule "classique". Pour les cellules par exemple il faut préciser FormulaR1C1=... Je pense que c'est ce qui bloque sur votre première approche.

Pour la seconde approche, il me semble qu'il manque un " fermant à l'intérieur de votre indirect. Il devrait etre du type :

"=INDIRECT(""I" & cell.Row -1 & """)"

On obtient bien en vérifiant :

debug.print "=INDIRECT(""I" & 2 & """)"
=INDIRECT("I2")

qui est la formule recherchée.

Bonjour Saboh,

En effet, votre solution permet au code de compiler sans bug. Neanmoins, je n'obtiens dans la liste deroulante que la valeur de I2 (si je prends votre cas), c'est lie a la surutilisation de guillement.

Pour moi l'orthographe devrait etre :

"=INDIRECT("I" & cell.Row -1)"

mais le code ne s'execute pas dans ce cas.

Je ne suis pas sur que vous ayez la bonne utilisation de la fonction indirect.
Elle évalue le texte donné en argument. Votre formule initiale renvoie vers une cellule, et non un texte. Vous obtiendrez forcément une erreur. Essayez dans le tableur sans VBA vous verrez.
J'avais compris que vous souhaitiez référer au contenu de la Cellule i_, c'est pourquoi je vous ai proposé cette formule. Si la cellule ne contient pas la liste des valeurs, alors oui effectivement il faut adapter la formule. Mais définissez d'abord ou se trouvent exactement toutes les données de votre liste. Votre fichier etant assez lourd je n'ai pas trop le temps de l'étudier en détails. Si c'est l'ensemble de la ligne i_, alors adaptez la formule que je vous ai proposé pour retourner une valeur de type =indirect("I5:I10").

Effectivement j'aurais du preciser ce point. La formule contient un texte qui est exactement le nom d'une plage de donnee que j'ai renome.

En ce cas vous y etes presque, il suffit d'encapsuler dans un autre indirect le premier résultat. Soit:

"=INDIRECT(INDIRECT(""I" & cell.Row - 1 & """))"

Il me semble que cela devrait fonctionner.

Ou, de manière plus élégante :

"=indirect(" & Range("I" & cell.Row - 1).Value2 &")"    ' pensez a indiquer sur quelle feuille prendre la range

La différence étant que si la valeur en i_ vient à changer la 1e formule s'adaptera, alors que la seconde non.

ou encore, pour éviter le sur-usage de la fonction indirect:

"=INDIRECT(TEXT(""I"" & cell.Row - 1; ""0""))"

Je pense que la solution au probleme est plus simple. Je souhaite juste remplacer l'appel classique dans les liste deroulante en cascade par appel variable.

Appel classique :

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=INDIRECT(J9)"

Je veux que le numero de ligne soit variable, mais dans tous les cas essayes, j'ai un probleme de syntaxe.

Excusez moi mais je vous ai déjà exactement expliqué pourquoi la formule que vous vous entetez à entrer ne fonctionne pas...

Vous ne prenez pas le temps d'effectuer des tests dans votre classeur car autrement vous auriez déjà trouvé la réponse, votre problème est lié à votre mauvaise compréhension de la fonction indirect, pas du VBA.

Vous n'avez en réalité pas besoin de la fonction indirect pour votre problème :

With Range("G" & cell.Row - 1)
    Dim listAddress As String
    listAddress = .Worksheet.Range("I" & cell.Row - 1).Value2

    .Validation.Delete
    .Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=" & listAddress
End With

Comme je le disais, l'unique différence est que la validation des données ne se mettra pas a jour sans relancer la macro si la cellule i_ change. Si vous en avez besoin alors vous devez utiliser indirect, et je vous renvoie vers les réponses précédentes, du type :

With Range("G" & cell.Row - 1).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=INDIRECT(INDIRECT(""I"" & cell.Row - 1))"
End With

ou l'encapsulation de l'adresse de i_ entre des " est nécessaire, et l'utilisation double de Indirect également.

Je n'ai peut etre pas été clair :

puisque vous souhaitez que les valeurs acceptées dans votre cellule soient liées à la valeur CONTENUE dans une autre cellule (la i_). Vous avez besoin d'accéder, de lire, le CONTENU de cette cellule.

a) Soit vous le faites de manière statique = méthode 1 de mon dernier message, ce qui "copie" la valeur contenue en i_. C'est équivalent à écrire dans la formule de validation de données : =listeABC

b) Soit vous le faites de manière dynamique, et alors il faut 2 étapes :
- d'abord accéder à la cellule i_, c'est le premier indirect ("extérieur")
- puis lire le contenu de la cellule i_, c'est le second indirect/text

Vous voulez sauter la première étape. Mais cela n'est pas possible, Excel vous renverra toujours le CONTENU de la cellule, à savoir le nom de votre liste. Il ne sera pas evalué.

Bonjour Saboh,

Merci pour vos retours, grace a vous j'ai pu trouver la solution qui me convenait. En revanche je vous assure que dans mon cas, les solutions que vous avez propose ne fonctionnaient pas. Merci tout de meme de m'avoir permis de comprendre mes erreurs.

Le code en question :

    Dim Package As String
    Package = Range("J" & cell.Row - 1).Value
    With Range("H" & cell.Row - 1).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=INDIRECT(Package)"
    End With

Bonne journee a vous,

Paul

Bonjour,

si votre problème est résolu n'hésitez pas a le marquer en tant que tel sur le forum. Je suis content que vous ayez trouvé une solution vous convenant.

Cependant il me semble important de signaler que le code que vous avez du entrer est un peu différent de celui que vous postez, ie. du type :

Formula1:="=INDIRECT(" & Package & ")"

Car avec

Formula1:="=INDIRECT(Package)"

Toutes les cellules sont vérifiées pour la plage nommée "package", pas la valeur que vous extrayez avec

Dim Package As String
Package = Range("J" & cell.Row - 1).Value