Excel vba formule de liste avec variable
Todesfall
Messages postés
74
Statut
Membre
-
pijaku Messages postés 13513 Date d'inscription Statut Modérateur Dernière intervention -
pijaku Messages postés 13513 Date d'inscription Statut Modérateur Dernière intervention -
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.
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.
6 réponses
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?
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?
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").
Bonjour tout le monde!
Pour insérer une variable dans une formule, il faut utiliser des &, comme ceci :
Cordialement,
Franck P
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
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 & ")"
Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(" & StrAdressCell & ")"
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..........
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
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
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--@
- ç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--@
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.
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.
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 :
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 :
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.
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.
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.
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.
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?
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
