Sujet Précédent Sujet Suivant

Excel vba formule de liste avec variable

Fermé
Todesfall Messages postés 70 Date d'inscription lundi 30 avril 2007 Statut Membre Dernière intervention 17 septembre 2014 - 9 août 2011 à 10:16
pijaku Messages postés 12263 Date d'inscription jeudi 15 mai 2008 Statut Modérateur Dernière intervention 4 janvier 2024 - 9 août 2011 à 17:19
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.

6 réponses

Réponse 1 / 6
Morgothal Messages postés 1236 Date d'inscription jeudi 22 avril 2010 Statut Membre Dernière intervention 19 mai 2015 183
9 août 2011 à 11:50
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
Réponse 2 / 6
Todesfall Messages postés 70 Date d'inscription lundi 30 avril 2007 Statut Membre Dernière intervention 17 septembre 2014
9 août 2011 à 12:02
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
Réponse 3 / 6
pijaku Messages postés 12263 Date d'inscription jeudi 15 mai 2008 Statut Modérateur Dernière intervention 4 janvier 2024 2 752
9 août 2011 à 12:12
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
Réponse 4 / 6
Todesfall Messages postés 70 Date d'inscription lundi 30 avril 2007 Statut Membre Dernière intervention 17 septembre 2014
9 août 2011 à 14:11
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
pijaku Messages postés 12263 Date d'inscription jeudi 15 mai 2008 Statut Modérateur Dernière intervention 4 janvier 2024 2 752
9 août 2011 à 14:38
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

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question
Réponse 5 / 6
Todesfall Messages postés 70 Date d'inscription lundi 30 avril 2007 Statut Membre Dernière intervention 17 septembre 2014
9 août 2011 à 15:01
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
pijaku Messages postés 12263 Date d'inscription jeudi 15 mai 2008 Statut Modérateur Dernière intervention 4 janvier 2024 2 752
9 août 2011 à 15:13
Peux tu me mettre l'intégralité de ton code ici?
0
Todesfall Messages postés 70 Date d'inscription lundi 30 avril 2007 Statut Membre Dernière intervention 17 septembre 2014
9 août 2011 à 15:22
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
pijaku Messages postés 12263 Date d'inscription jeudi 15 mai 2008 Statut Modérateur Dernière intervention 4 janvier 2024 2 752
9 août 2011 à 15:29
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
Réponse 6 / 6
Todesfall Messages postés 70 Date d'inscription lundi 30 avril 2007 Statut Membre Dernière intervention 17 septembre 2014
9 août 2011 à 15:53
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
pijaku Messages postés 12263 Date d'inscription jeudi 15 mai 2008 Statut Modérateur Dernière intervention 4 janvier 2024 2 752
9 août 2011 à 17:19
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

"L'indice n'appartient à la sélection" + VBA
fleur.31 -
geosevda -

2 réponses
Erreur d'exécution 13 Incompatibilité de type
grace -
pijaku -

5 réponses
ouverture d'un fichier excel à partir d'un code VBA
TraderAS -
pijaku -

3 réponses
Méthode find dans VBA - Recherche de données sous Excel
baissaoui -
baissaoui -

0 réponse
appliquer une pause dans une macro vba excel
ch'duj -
lerif25 -

10 réponses