Sélection d'une plage dans validation de données

Version imprimable

Bonjour,
J'intègre une validation de données dans une suite de cellule, grâce au code suivant :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
    Dim V3 As Integer
    V3 = 12
    While Sheets("Feuil1").Cells(V3, 1) <> ""
    Sheets("Feuil1").Select
    Cells(V3, 12).Select
        If Not ActiveCell <> "" Then
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=Listes_menus_deroulants!$A$2:$A$27"
                .IgnoreBlank = True
                .InCellDropdown = True
                .ShowInput = True
                .ShowError = True
            End With
        End If
        V3 = V3 + 1
    Wend
Else

Cependant je ne parviens pas à comprendre comment remplacer la plage par une variable

Merci par avance.
Cordialement.

27/08/2014, 15h15
Philippe Tulliez

Bonjour,
Tu sélectionnes la plage $A$2:$A$27 de la feuille [Listes_menus_deroulants], tu sélectionnes la référence A2 dans la zone des noms et tu tapes le nom que tu souhaites donner à cette plage en validant à l'aide de la touche Entrée. Le nom aura une portabilité classeur.
Si tu souhaites que le nom ne soit connu que dans une feuille, il faut passer par le gestionnaire de nom ou faire précéder le nom que par le nom de la feuille suivi du point d'exclamation.
Pour rendre la plage dynamique, il faut transformer la plage en tableau.
Le code en VBA doit alors être (code non testé, tapé de mémoire)

Code:

Formula1:="=leNomDeLaPlage"

ou

Code:

Formula1:="=NomDeLaFeuille!leNomDeLaPlage"

Bonjour,

je sais pas si j'ai bien compris!

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Dim R As Range
    While Sheets("Feuil1").Cells(V3, 1) <> ""
     Set R = Sheets("Feuil1").Cells(V3, 12)
            If R = "" Then
            With R.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=Listes_menus_deroulants!$A$2:$A$27"
                .IgnoreBlank = True
                .InCellDropdown = True
                .ShowInput = True
                .ShowError = True
            End With
        End If
        V3 = V3 + 1
    Wend

Bonjour le fil, bonjour le forum,

Si j'ai bien compris, essaie comme ça :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sub Macro1()
Dim L As Object 'déclare la variable L (onglet Liste_menus_deroulants)
Dim DL As Integer 'déclare la variable DL (Dernière Ligne)
Dim PL As Range 'déclare la variable PL (PLage)
Dim O As Object 'déclare la variable O (onglet)
Dim CEL As Range 'déclare la variable CEL (CELlule)
 
Set L = Sheets("Listes_menus_deroulants") 'définit l'onglet L
DL = L.Cells(Application.Rows.Count, 1).End(xlUp).Row 'définit la dernière ligne éditée DL de la colonne 1 (=A) de l'onglet L
Set PL = L.Range("A2:A" & DL) 'définit la plage PL
Set O = Sheets("Feuil1") 'définit l'onglet O
Set CEL = O.Cells(12, 1).End(xlDown).Offset(1, 0) 'définit la cellule CEL
With CEL.Validation 'prend en compte la validation de données de la cellule CEl
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=Listes_menus_deroulants!" & PL.Address
    .IgnoreBlank = True
    .InCellDropdown = True
    .ShowInput = True
    .ShowError = True
End With
End Sub

27/08/2014, 15h30
r.morel

1 pièce(s) jointe(s)

Citation:

Envoyé par Philippe Tulliez

Bonjour,
Tu sélectionnes la plage $A$2:$A$27 de la feuille [Listes_menus_deroulants], tu sélectionnes la référence A2 dans la zone des noms et tu tapes le nom que tu souhaites donner à cette plage en validant à l'aide de la touche Entrée. Le nom aura une portabilité classeur.
Si tu souhaites que le nom ne soit connu que dans une feuille, il faut passer par le gestionnaire de nom ou faire précéder le nom que par le nom de la feuille suivi du point d'exclamation.
Pour rendre la plage dynamique, il faut transformer la plage en tableau.
Le code en VBA doit alors être (code non testé, tapé de mémoire)

Code:

Formula1:="=leNomDeLaPlage"

ou

Code:

Formula1:="=NomDeLaFeuille!leNomDeLaPlage"

Bonjour,
Merci pour votre réponse mais cela me renvoie le défaut suivant : 1004
Pièce jointe 154834

Citation:

Envoyé par rdurupt

je sais pas si j'ai bien compris!

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Dim R As Range
    While Sheets("Feuil1").Cells(V3, 1) <> ""
     Set R = Sheets("Feuil1").Cells(V3, 12)
            If R = "" Then
            With R.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=Listes_menus_deroulants!$A$2:$A$27"
                .IgnoreBlank = True
                .InCellDropdown = True
                .ShowInput = True
                .ShowError = True
            End With
        End If
        V3 = V3 + 1
    Wend

Bonjour,
Je n'ai peut être pas bien formulée ma question : mais l'objectif est de faire varier la plage : "Listes_menus_deroulants!$A$2:$A$27" pour assurer un bon fonctionnement lors de la modification de la plage.
Merci.

27/08/2014, 15h43
Thautheme

Bonjour le fil, bonjour le forum,

Je pense que ma proposition du post #4 devrait convenir. L'as-tu regardée ?
27/08/2014, 15h52
r.morel

Oui.
J'étais justement dessus. Elle ne renvoie pas d'erreur, cependant les cellules "traitées" ne contiennent pas de validation de données.

Je ne comprend pas pourquoi.

Merci.
27/08/2014, 16h10
Thautheme

Bonjour le fil, bonjouir le forum,

Tel que j'avais compris, seule la première cellule vide de la colonne A de l'onglet Feuil1 contient une validation de donnée ! Sinon il faut que tu t'expliques un peu mieux...

Merci pour tes réponses.
Grâce au code suivant : J'insère avec la boucle une validation de données dans chaque cellule de la Feuil1, de la colonne 12 a partir de la ligne 12 si la cellule de la même ligne et de la colonne 1 est vide.
La liste de validation inséré dans ces cellules comportent les valeurs des cellules A2:A27 de la feuille Listes_menus_deroulants
C'est ici que je rencontre le problème car je ne comprend pas comment inséré une variable pour par exemple pouvoir sélectionner automatiquement la plage A2:A28 si la liste est modifiée.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Dim V3 As Integer V3 = 12 While Sheets("Feuil1").Cells(V3, 1) <> "" Sheets("Feuil1").Select Cells(V3, 12).Select If Not ActiveCell <> "" Then With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Listes_menus_deroulants!$A$2:$A$27" .IgnoreBlank = True .InCellDropdown = True .ShowInput = True .ShowError = True End With End If V3 = V3 + 1 Wend Else
Merci.

Bonjour le fil, bonjour le forum,

Peut-être comme ça alors :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Sub Macro1()
Dim O As Object 'déclare la variable O (onglet)
Dim DL As Integer 'déclare la variable DL (Dernière Ligne)
Dim PL As Range 'déclare la variable PL (PLage)
Dim CEL As Range 'déclare la variable CEL (CELlule)
 
Set O = Sheets("Feuil1") 'définit l'onglet O
DL = O.Cells(Application.Rows.Count, 1).End(xlUp).Row 'définit la dernière ligne éditée DL de la colonne 1 (=A) de l'onglet O
Set PL = O.Range("A12:A" & DL) 'définit la plage PL
For Each CEL In PL
   With CEL.Validation 'prend en compte la validation de données de la cellule CEL
       .Delete
       .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
         xlBetween, Formula1:="=Listes_menus_deroulants!$A$2:$A$27"
       .IgnoreBlank = True
       .InCellDropdown = True
       .ShowInput = True
       .ShowError = True
   End With
Next CEL
End Sub

[Édition]
Nos posts se sont croisés. Ce code ne correspond absolument pas. Je revois ma copie...

Bonjour le fil, bonjour le forum,

Peut-être comme ça :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
Sub Macro3()
Dim O As Object 'déclare la variable O (Onglet)
Dim DLO As Integer 'déclare la variable DLO (Dernière Ligne de l'onglet O)
Dim PLO As Range 'déclare la variable PLO (Plage de l'onglet O)
Dim L As Object 'déclare la variable L (onglet Listes_menus_deroulants)
Dim DLL As Integer 'déclare la variable DLL (Dernière Ligne de l'onglet L)
Dim PLL As Range 'déclare la variable PLL (Plage de l'onglet L)
Dim CEL As Range 'déclare la variable CEL (CELlule)
 
'.....
    Set O = Sheets("Feuil1") 'définit l'onglet O
    DLO = O.Cells(Application.Rows.Count, 1).End(xlUp).Row - 1 'définit la dernìere ligne vide DLO de la colonne 1 (=A) de l'onglet O
    Set PLO = Range("A12:A" & DLO) 'définit la plage PLO
    Set L = Sheets("Listes_menus_deroulants") 'défini l'onglet L
    DLL = L.Cells(Application.Rows.cont, 1).End(xlUp).Row 'définit la dernière ligne éditée DLL de la colonne 1 (=A) de l'onglet L
    Set PLL = Range("A2:A" & DLL) 'définit la plage PLL
    For Each CEL In PLO 'boucle sur toutes les cellules CEL de la palge PLO
        If CEL = "" Then 'condition : si la cellule est vide
            With CEL.Offset(0, 11) 'prend en compte la validation de données de la cellule en colonne 12 (=L)
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=Listes_menus_deroulants!" & PLL.Address
                .IgnoreBlank = True
                .InCellDropdown = True
                .ShowInput = True
                .ShowError = True
            End With
        End If
    Next CEL
Else
 
'.....
 
End Sub

27/08/2014, 17h12
r.morel

Désolé mais cela ne fonctionne toujours pas.
Je n'obtiens toujours pas de liste déroulante après le traitement des cellules par la macro.
Merci.
27/08/2014, 17h27
Thautheme

Bonjour le fil, bonjour le forum,

Sans le fichier difficile de faire mieux...

Bonjour,
La réponse que je t'ai donnée concernait la manière de nommer la plage manuellement où se trouvait la liste et d'y faire référence avec un code VBA. Je l'avais écrite de tête et sans test.
Avec le test cela fonctionne parfaitement et celui-ci est effectué sur une sélection et pas sur certaines cellules répondant à des conditions.
La plage est nommée pLogement et est de portabilité classeur
Code:

1 2 3 4 With Selection.Validation .Delete .Add Type:=xlValidateList, Formula1:="=pLogement" End With
[EDIT]
Voici une procédure qui place à l'aide de l'outil Validation de données une liste (référence à la plage A2:Ax de la feuille nommée ParamList) dans la cellule de la colonne 12 (L) si la cellule de la colonne 4 (D) contient la valeur M
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Sub ActiveValidationWithCondition() Dim rngSource As Range, rngList As Range, cel As Range With ThisWorkbook Set rngSource = .Worksheets("db").Range("A1").CurrentRegion With .Worksheets("ParamList") Set rngList = .Range("A2:A" & .Range("A2").End(xlDown).Row) End With End With For Each cel In rngSource.Offset(columnoffset:=3).Resize(columnsize:=1) rngSource.Cells(cel.Row, 12).Validation.Delete ' Supprime la validation de données de toutes les cellules If cel.Value = "M" Then With rngSource.Cells(cel.Row, 12).Validation '.Delete .Add Type:=xlValidateList, Formula1:="=" & rngList.Address(external:=True) End With End If Next cel End Sub

27/08/2014, 19h12
r.morel

Effectivement avec la portabilité classeur cela fonctionne et me convient.

J'avais essayé sans passe par le gestionnaire de nom. Mais je ne comprend pourquoi j'avais l'erreur 1004.

En tout cas merci à tous pou votre temps et votre patience.
Bonne soirée.