Excel vba formule de liste avec variable

Todesfall -  
Bonjour à tous,

J'ai un petit problème sous excel. J'ai créer une liste déroulante dans une cellule puis une 2ème liste déroulante dans une autre cellule dépendant du résultat de la première liste déroulante.

Lorsque je clique sur un bouton de commande il m'insère une ligne et je voudrais en même temps qu'il m'insère les formules dans les colonnes. Pour la première liste déroulante tout se passe bien mais pour la deuxième je n'y arrive pas. Le code que je met en vba est le suivant :

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=indirect(cells(i,2))"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Il met met donc une erreur. La variable i présente dans le code me spécifie la ligne à laquelle je suis.

Si quelqu'un a une idée sur le problème. Merci d'avance.

PS : Si ce n'est pas très clair n'hésitez pas à le dire.
--

@--ToDeSfAlL--@

6 réponses

  1.  Morgothal
    Bonjour,
    Je ne comprend pas:
    "Lorsque je clique sur un bouton de commande" quel bouton ?
    "je voudrais en même temps qu'il m'insère les formules dans les colonnes" Quelles formules dans quelles colonnes? Le bouton au dessus n'insérait pas des lignes?
    Le code inséré correspond à quel contrôle?
    0
    1. Todesfall Auteur
      Quel bouton ? Un bouton de commande que j'ai insérer sur ma feuille excel. Celui-ci insère une ligne avec les formules du style (F4*G4), etc... je voudrais notamment une formule qu'en temps normal on insère en faisant Donnée/Validation choisir liste dans autoriser et source je tape =indirect("cellule comprenant la 1ère liste déroulante").

      0
      1. pijaku Modérateur
        Bonjour tout le monde!
        Pour insérer une variable dans une formule, il faut utiliser des &, comme ceci : 
        Dim StrAdressCell As String 'StrAdressCell va représenter le nom de la cellule
'sous la forme d'une chaine de caractères. Dans l'exemple ci-dessous : $B$2
Dim Lign As Integer 'définit le n° de la ligne (2 dans B2)
Lign = 2
StrAdressCell = "$B$" & Lign 'ici on affecte sa valeur à notre variable
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=INDIRECT(" & StrAdressCell & ")" 'utilisation des &
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
        --
        Cordialement,
        Franck P
        0
        1. Todesfall Auteur
          Merci pour la réponse mais le problème du code ci-dessus c'est que excel me met une erreur "Erreur d'exécution '1004' " et lorsque je clique sur Débogage il me surligne en jaune les lignes de codes suivantes :

          Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
          xlBetween, Formula1:="=INDIRECT(" & StrAdressCell & ")"
          0
          1. pijaku Modérateur
            C'est parce que tu lances la macro par un bouton.....
            Selection.Validation ne peux pas fonctionner car lorsque tu cliques sur ton bouton, tu le sélectionnes.......... 
            Sub ListeDeValid()
Dim Cel As Range
Dim StrAdressCell As String 'StrAdressCell va représenter le nom de la cellule
'sous la forme d'une chaine de caractères. Dans l'exemple ci-dessous : $B$2
Dim Lign As Integer 'définit le n° de la ligne (2 dans B2)
Set Cel = ActiveCell 'on attribue à Cel le range "actif"
Lign = 2
StrAdressCell = "$B$" & Lign 'ici on affecte sa valeur à notre variable
Cel.Select 'on sélectionne Cel
    With Cel.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=INDIRECT(" & StrAdressCell & ")" 'utilisation des &
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
Set Cel = Nothing
End Sub
            0
        3. Todesfall Auteur
          Alors 2 constatations :
          - ça marche très lorsque j'ai choisi 1 élément dans la cellule contenant la première liste déroulante ;
          - par contre si je ne met rien dans la cellule contenant la première liste déroulante et donc que cette cellule est vide, excel me met la même erreur que précédemment.

          @--ToDeSfAlL--@
          0
          1. pijaku Modérateur
            Peux tu me mettre l'intégralité de ton code ici?
            0
          2. Todesfall Auteur
            Voici le code que j'ai testé. Je ne l'ai pas ajouté à l'intégralité du code du classeur mais sur un nouveau classeur :

            Sub condition()
            Dim Cel As Range
            Dim StrAdressCell As String
            Dim Lign As Integer
            Set Cel = ActiveCell
            Lign = 18
            StrAdressCell = "$I$" & Lign
            Cel.Select
            With Cel.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=INDIRECT(" & StrAdressCell & ")"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
            End With
            Set Cel = Nothing
            End Sub

            Toujours la même erreur.
            0
          3. pijaku Modérateur
            Refaisons le test en entier.
            1- ouvrir un nouveau classeur
            2- Feuil1, de J1 à J10 saisir des valeurs (quelconques)
            3- en I18 saisir : J1:J10
            4- ALT+F11
            5- Insertion/Module
            6- Copie-Colle ce code : 
            Sub condition() 
Dim Cel As Range 
Dim StrAdressCell As String 
Dim Lign As Integer 

Set Cel = ActiveCell 
Lign = 18 
StrAdressCell = "$I$" & Lign 
Cel.Select 
With Cel.Validation 
    .Delete 
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ 
xlBetween, Formula1:="=INDIRECT(" & StrAdressCell & ")" 
    .IgnoreBlank = True 
    .InCellDropdown = True 
    .InputTitle = "" 
    .ErrorTitle = "" 
    .InputMessage = "" 
    .ErrorMessage = "" 
    .ShowInput = True 
    .ShowError = True 
End With 
Set Cel = Nothing 
End Sub

            7- Ferme l'éditeur visual basic
            8- dessine un bouton de commande
            9- en mode création double clic sur ton bouton
            10- le code du bouton est : 
            Private Sub CommandButton1_Click()
Call condition
End Sub

            11- re-ferme Visual Basic
            12- clic sur une cellule au hasard : D5
            13- clic sur ton bouton.

            Ca fonctionne!
            Ton erreur 1004, tiens dans le fait que ta cellule I18 doit contenir, non pas une liste de validation, mais l'adresse de la plage de valeurs. Ici tes valeurs ayant préalablement été saisies en J1:J10, il convient d'écrire dans la cellule I18 : J1:J10.
            0
        4. Todesfall Auteur
          Toujours le même souci, je me suis peut être mal expliqué et j'en ai peut-être pas dit assez. On va prendre un exemple concret :
          J'ai une liste de A1 à A5 comprenant a,b,c,d,e nommé alpha.
          J'ai 5 autres listes nommé :
          - a comprenant 1,2,3,4,5 positionné en B1 à B5 ;
          - b comprenant 10,11,12,13,14 positionné en C1 à C5;
          - c comprenant 20,21,22,23,24 positionné en D1 à D5;
          - d comprenant 30,31,32,33,34 positionné en E1 à E5;
          - e comprenant 40,41,42,43,44 positionné en F1 à F5.

          En I18 je fais Donnée/Validation, je choisis liste et marque =alpha. A cet instant la cellule I18 est vide car je n'ai toujours rien choisi entre a,b,c,d ou e.

          Si je me positionne en J18 et que j'appuie sur le bouton il me marque l'erreur dit précédemment.
          Au contraire si je choisi en I18 a ou b ou c ou d ou e et que j'appuie sur le bouton alors là je n'ai pas de problème. Il me choisi bien la bonne liste en fonction de I18.

          J'aimerai alors qu'excel ne bloque pas même si en I18 je n'ai pas encore choisi entre les propositions de la liste alpha.
          0
          1. pijaku Modérateur
            Ahhh l'importance de bien décrire son problème!!!!!!!!!!!!!!!!

            La meilleure solution est, encore une fois, de contourner le problème.
            On dit, par macro, que I18 doit contenir le 1er terme de sa liste (alpha dans l'exemple) et on efface le contenu en fin de macro. Ca te va comme ça? 

            Sub condition()
Dim Cel As Range
Dim StrAdressCell As String
Dim Lign As Integer

Set Cel = ActiveCell
'cf : http://boisgontierjacques.free.fr/pages_site/donneesvalidation.htm
Range("I18").Select 
SendKeys "%{down}"
If Range("I18") = "" Then
    Range("I18") = Range("alpha")(1)
End If
Lign = 18
StrAdressCell = "$I$" & Lign
Cel.Select
With Cel.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(" & StrAdressCell & ")"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With
Set Cel = Nothing
Range("I18") = ""
End Sub
            0

        Discussions similaires

        Code pour connecter une télécommande Grundig à TV Samsung
        Lancelot

        1 réponse