XL 2013 Liste déroulante : faire apparaître surface et commune correspondant à sélection

Sebast · 15 Février 2016

Bonjour à toutes et à tous,

j'essaie depuis un certain temps de rendre une liste déroulante dépendante d'une autre mais sans succès …

Pour faire simple :
Dans un tableau de saisie, j'appelle un n° de parcelle pour lequel je vais déclarer le type de production réalisée
Autant j'arrive à créer les listes déroulantes (parcelle 1 à n et production à choisir parmi "blé, maïs, etc.") autant je ne sais pas comment faire pour que la surface correspondant à la parcelle choisie se renseigne dans la colonne C "Surface".
Idem pour que la commune correspondant au n° de parcelle se renseigne dans la colonne E "Commune"...

Dans la feuille "Ma_saisie", l'ordre des colonnes n'a pas d'importance (si toutefois cela pouvait représenter une difficulté supplémentaire)

Par recherchev() je sais comment faire apparaître les infos adéquates dans les cellules correspondantes mais par à les intégrer dans une liste déroulante.

Quelqu'un a-t-il une idée comment réaliser ça ?

Merci d'avance

phlaurent55 · 15 Février 2016

Re : Liste déroulante : faire apparaître surface et commune correspondant à sélection

Bonjour Sebast,

voir fichier joint

à+
Philippe

Sebast · 15 Février 2016

Re : Liste déroulante : faire apparaître surface et commune correspondant à sélection

Bonsoir Philippe,

merci pour ta solution, qui fonctionne bien.

Cependant, je pensais - si possible - à une liaison dans le menu déroulant, en gros quand je sélectionne Parcelle8 alors automatiquement dans la colonne adéquate se colle la surface et la commune correspondantes, en arrière-plan, sans qu'on voit les formules (un peu comme quand on active le menu déroulant on ne voit pas la cuisine qu'il y a derrière ...)

Mais c'est vrai que ta solution a le mérite de la simplicité. Ma crainte : que la formule soit "écrasée" ou effacée par erreur par mes futurs utilisateurs ...
A défaut, existe-t-il une manière de "masquer" la formule (la rendre invisible dans la colonne concernée) ?

Encore merci

phlaurent55 · 16 Février 2016

Re : Liste déroulante : faire apparaître surface et commune correspondant à sélection

Re,

Mais c'est vrai que ta solution a le mérite de la simplicité. Ma crainte : que la formule soit "écrasée" ou effacée par erreur par mes futurs utilisateurs ...
A défaut, existe-t-il une manière de "masquer" la formule (la rendre invisible dans la colonne concernée) ?

une autre solution par macro, les colonnes B & D ne contiennent plus de formules et les données qu'elles contiennent ne peuvent plus être effacées ou modifiées

à+
Philippe

Sebast · 16 Février 2016

Re : Liste déroulante : faire apparaître surface et commune correspondant à sélection

Bonjour Philippe,

merci beaucoup de t'être penché sur ma demande spécifique.
Ce code fonctionne parfaitement et répond à mes attentes, qui plus est il est pour moi totalement nouveau, ce qui va me permettre d'en apprendre davantage, si j'ai quelques explications.

Puis-je te demander de commenter certaines lignes ?

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2:B" & Range("B65535").End(xlUp).Row)) Is Nothing Then 
 ' je ne comprends pas intersect ?
  ' je ne comprends pas comment on utilise Target sans l'avoir défini avant ?
For Each cellule In Sheets(3).[MesParcelles]
  ' je ne trouve pas trace d'un objet [MesParcelles] ou feuille [MesParcelles]?
    If cellule = Target Then
        ActiveSheet.Unprotect
        Target.Offset(0, 1) = cellule.Offset(0, 1)
        Target.Offset(0, 3) = cellule.Offset(0, 2)
        ActiveSheet.Protect
        Exit For
    End If
Next cellule
End If
End Sub

Encore merci

phlaurent55 · 16 Février 2016

Re : Liste déroulante : faire apparaître surface et commune correspondant à sélection

Re,

If Not Intersect(Target, Range("B2:B" & Range("B65535").End(xlUp).Row)) Is Nothing Then

La procédure WorkSheet_Change se déclenche automatiquement si il y a un changement de valeur dans une cellule de la colonne B
(de B2 à B & la dernière ligne non-vide de la colonne B) --> Range("B2:B" & Range("B65535").End(xlUp).Row)

' je ne trouve pas trace d'un objet [MesParcelles] ou feuille [MesParcelles]?

il ne s'agit pas d'un Objet ou d'une feuille mais d'une zone nommée MesParcelles
il suffit de presser Ctrl+F3 pour voir les zones nommées
cette zone est dynamique, il est possible d'ajouter ou supprimer des parcelles, la liste de validation en tiendra compte

à+
Philippe

Sebast · 16 Février 2016

Re : Liste déroulante : faire apparaître surface et commune correspondant à sélection

Re,

merci pour tes explications, que je dois encore digérer.

- Pour [MesParcelles] je cherchais en vain avec la case qui se trouve au-dessous de la barre, dans laquelle on clique et qui présente les champs nommés : bizarre que ça n'y figure pas (MaProduction y est bien)

- je connaissais les macros événementielles mais j'ai toujours du mal avec Intersect ...

- j'ai regardé du côté de MesParcelles et ne comprends pas :

decaler(Source_parcelles!$A$2;;;;nbval(Sources_parcelles!$A:$A)-1)

Si toutes les feuilles comportaient des Tableaux (au sens excel du terme), y aurait-il encore besoin de Decaler ?
Pourquoi autant de ;;;; à quoi correspondent-ils ?

Y a-t-il une alternative plus simple à Intersect ?

Merci pour ton aide

Dranreb · 17 Février 2016

Re : Liste déroulante : faire apparaître surface et commune correspondant à sélection

Bonsoir
Pour les tableaux vous pouvez utiliser Intersect(Target, Me.ListObjects(1).ListColumns("titre").DatabodyRange)

phlaurent55 · 17 Février 2016

Re : Liste déroulante : faire apparaître surface et commune correspondant à sélection

Re,

- j'ai regardé du côté de MesParcelles et ne comprends pas :

decaler(Source_parcelles!$A$2;;;;nbval(Sources_par celles!$A:$A)-1)

Si toutes les feuilles comportaient des Tableaux (au sens excel du terme), y aurait-il encore besoin de Decaler ?
Pourquoi autant de ;;;; à quoi correspondent-ils ?

tout d'abord, il n'y a que 3 point-virgule qui se suivent et pas 4
Pour toutes questions concernant une fonction, la touche F1 est ton amie

Fonction DECALER

Afficher tout

Masquer tout
Renvoie une référence à une plage qui correspond à un nombre déterminé de lignes et de colonnes d'une cellule ou plage de cellules. La référence qui est renvoyée peut être une cellule unique ou une plage de cellules. Vous pouvez spécifier le nombre de lignes et de colonnes à renvoyer.
Syntaxe
DECALER(réf;lignes;colonnes;hauteur;largeur)
réf est la référence par rapport à laquelle le décalage doit être opéré. L'argument réf doit être une référence à une cellule ou à une plage de cellules adjacentes ; sinon, la fonction DECALER renvoie la valeur d'erreur #VALEUR!.
lignes est le nombre de lignes vers le haut ou vers le bas dont la cellule supérieure gauche de la référence renvoyée doit être décalée. Si l'argument lignes est égal à 5, la cellule supérieure gauche de la référence est décalée de cinq lignes en dessous de la référence. L'argument lignes peut être positif (c'est-à-dire en dessous de la référence de départ) ou négatif (c'est-à-dire au-dessus de la référence de départ).
colonnes est le nombre de colonnes vers la droite ou vers la gauche dont la cellule supérieure gauche de la référence renvoyée doit être décalée. Si l'argument colonnes est égal à 5, la cellule supérieure gauche de la référence est décalée de cinq colonnes vers la droite par rapport à la référence. L'argument colonnes peut être positif (c'est-à-dire à droite de la référence de départ) ou négatif (c'est-à-dire à gauche de la référence de départ).
hauteur est la hauteur, exprimée en nombre de lignes que la référence renvoyée doit avoir. L'argument hauteur doit être un nombre positif.
largeur est la largeur, exprimée en nombre de colonnes que la référence renvoyée doit avoir. L'argument largeur doit être un nombre positif.
Notes

Si les arguments lignes et colonnes décalent la référence au-delà du bord de la feuille de calcul, la fonction DECALER renvoie la valeur d'erreur #REF!.

Si les arguments hauteur ou largeur sont omis, les valeurs par défaut des arguments hauteur et largeur sont celles de l'argument réf.

La fonction DECALER n'a pas pour rôle de décaler physiquement les cellules dans la feuille ni de modifier la sélection ; elle renvoie simplement une référence. La fonction DECALER peut être utilisée avec toutes les fonctions exigeant une référence comme argument. Par exemple, la formule SOMME(DECALER(C2;1;2;3;1)) calcule la valeur totale de la plage couvrant 3 lignes sur 1 colonne et se situant 1 ligne en dessous et 2 colonnes à droite de la cellule C2.

Exemple
L'exemple sera plus compréhensible si vous le copiez dans une feuille de calcul vide.

Comment copier un exemple

Créez un classeur ou une feuille de calcul vide.

Sélectionnez l'exemple de la rubrique d'aide. Remarque Ne sélectionnez pas les en-têtes des lignes ou des colonnes.

Sélection d'un exemple de l'aide

Appuyez sur Ctrl+C.

Dans la feuille de calcul, sélectionnez la cellule A1 et appuyez sur Ctrl+V.

Pour basculer entre l'affichage des résultats et la formule, appuyez sur Ctrl+` (accent grave) ou sous l'onglet Formules, dans le groupe Audit de formules, cliquez sur le bouton Afficher les formules.

1
2
3
4

A B
Formule Description (résultat)
=DECALER(C3;2;3;1;1) Affiche la valeur contenue dans la cellule F5 (0)
=SOMME(DECALER(C3:E5;-1;0;3;3)) Totalise la plage C2:E4 (0)
=DECALER(C3:E5;0;-3;3;3) Renvoie une erreur, car la référence ne se trouve pas sur la feuille de calcul (#REF!)

Sebast · 23 Février 2016

Re : Liste déroulante : faire apparaître surface et commune correspondant à sélection

Bonjour Danreb et Philippe,

désolé pour ma réponse tardive, je n'étais à la fois pas proche d'un ordi et surtout la notification de message ne s'était pas activée ...
Merci pour vos réponses complètes et détaillées.

Danreb : j'ai consulté la syntaxe de la macro événementielle, le Intersect est effectivement proposé d'emblée. Après réflexion, je l'ai digérée et espère l'avoir intégrée.

Philippe : merci pour ce tableau très didactique. De plus, tu as raison, F1 est insuffisamment utilisée ...

Encore merci à vous

XL 2013 Liste déroulante : faire apparaître surface et commune correspondant à sélection

Sebast

XLDnaute Impliqué

Pièces jointes

phlaurent55

Nous a quittés en 2020

Pièces jointes

Sebast

XLDnaute Impliqué

phlaurent55

Nous a quittés en 2020

Pièces jointes

Sebast

XLDnaute Impliqué

phlaurent55

Nous a quittés en 2020

Pièces jointes

Sebast

XLDnaute Impliqué

Dranreb

XLDnaute Barbatruc

phlaurent55

Nous a quittés en 2020

Sebast

XLDnaute Impliqué

Discussions similaires