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

**r.morel** · 27/08/2014, 15h04

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

**Philippe Tulliez** · 27/08/2014, 15h15

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

Formula1:="=leNomDeLaPlage"

ou

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Formula1:="=NomDeLaFeuille!leNomDeLaPlage"

Invité · 27/08/2014, 15h17

je sais pas si j'ai bien compris!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**Thautheme** · 27/08/2014, 15h27

Bonjour le fil, bonjour le forum,

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**r.morel** · 27/08/2014, 15h30

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

Formula1:="=leNomDeLaPlage"

ou

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Formula1:="=NomDeLaFeuille!leNomDeLaPlage"

Bonjour,
Merci pour votre réponse mais cela me renvoie le défaut suivant : 1004
Nom : Capture.JPG
Affichages : 978
Taille : 160,4 Ko

**r.morel** · 27/08/2014, 15h35

Envoyé par rdurupt

je sais pas si j'ai bien compris!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

**Thautheme** · 27/08/2014, 15h43

Bonjour le fil, bonjour le forum,

Je pense que ma proposition du post #4 devrait convenir. L'as-tu regardée ?

**r.morel** · 27/08/2014, 15h52

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.

**Thautheme** · 27/08/2014, 16h10

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...

**r.morel** · 27/08/2014, 16h25

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

**Thautheme** · 27/08/2014, 16h30

Bonjour le fil, bonjour le forum,

Peut-être comme ça alors :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**r.morel** · 27/08/2014, 17h12

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.

**Thautheme** · 27/08/2014, 17h27

Bonjour le fil, bonjour le forum,

Sans le fichier difficile de faire mieux...

**Philippe Tulliez** · 27/08/2014, 17h48

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**r.morel** · 27/08/2014, 19h12

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.

Sélection d'une plage dans validation de données [XL-2010]

Macros et VBA Excel

Discussions similaires

Partager

Partager