IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Macros et VBA Excel Discussion :

Liste déroulante sans vide


Sujet :

Macros et VBA Excel

  1. #1
    Membre régulier
    Homme Profil pro
    Touche à tout
    Inscrit en
    Mai 2017
    Messages
    434
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Allier (Auvergne)

    Informations professionnelles :
    Activité : Touche à tout

    Informations forums :
    Inscription : Mai 2017
    Messages : 434
    Points : 108
    Points
    108
    Par défaut Liste déroulante sans vide
    Bonjour à tous,

    J'affecte une liste déroulante aux cellules d'une colonne (page 1) avec les données contenues (page 2).

    Je souhaite pour une flexibilité de l'application que le taille de ma liste déroulante s'adapte automatiquement au nombre de lignes qui alimentent ma liste déroulante, le tout en ayant une valeur unique (c'est à dire que si toto est inscrit deux fois, il n'apparaît qu'une fois dans ma liste :-)

    Ci joint mon code:

    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
    'TypeOfTest array:
    If ActiveSheet.Name = "PAGE1" Then
        If ActiveColumn = 5 And ActiveRow < 101 Then
            'MsgBox "Column: " & ActiveColumn & ", line: " & ActiveRow
            With Range("E" & ActiveRow).Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, Formula1:="=PAGE2!G2:G10"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
        End If
    End If
    Merci par avance

  2. #2
    Membre chevronné Avatar de mfoxy
    Homme Profil pro
    Automation VBA
    Inscrit en
    Février 2018
    Messages
    752
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 37
    Localisation : Belgique

    Informations professionnelles :
    Activité : Automation VBA
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : Février 2018
    Messages : 752
    Points : 1 971
    Points
    1 971
    Par défaut
    Bonjour,

    En fonction de votre version excel les function sort et unique pourraient faire le job.

    Avis perso, je base toujours mes listes déroulantes (liste validation et combobox) sur un tableau structuré dédié, ce qui évite ce genre de "chipotages", rend la chose plus maintenableet secure.

    L'ajout de liste de validation par code est aussi pour moi un défaut de conception. Avec un tableau structuré, il suffit de mettre une fois la liste de validation bien en place pour qu'elle soit automatiquement répliqué sur les lignes suivantes d'une même colonne.

    Bav,
    Michaël

    Si mon aide/avis vous a été profitable , n'hésitez pas à cliquer sur , ça fait toujours plaisir...
    _________________________________________________________________________________________________________________

    "Tout le monde est un génie. Mais si on juge un poisson sur sa capacité à grimper à un arbre, il passera sa vie à croire qu'il est stupide..."
    Albert Einstein

  3. #3
    Membre éclairé Avatar de Nicolas JACQUIN
    Homme Profil pro
    .
    Inscrit en
    Avril 2014
    Messages
    551
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Morbihan (Bretagne)

    Informations professionnelles :
    Activité : .
    Secteur : Transports

    Informations forums :
    Inscription : Avril 2014
    Messages : 551
    Points : 787
    Points
    787
    Par défaut
    Bonjour à tous,

    Insserer Controle ActiveX - Zone de liste déroulante

    et macro à lancer

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    Sub Alimente_la_ComboBox_de_la_Feuil1_sans_doublon()
    Dim Cell As Range
    Feuil1.ComboBox1.Clear
    For Each Cell In Feuil1.Range("E1:E47") ' A adapter
        Feuil1.ComboBox1 = Cell
        If Feuil1.ComboBox1.ListIndex = -1 Then _
            Feuil1.ComboBox1.AddItem Cell
    Next Cell
    End Sub
    @+
    Nicolas
    Merci d'exprimer votre message le plus clairement possible pour qu'on puisse vous aider

    n'oubliez pas de cliquer sur et si cela vous a aidé pensez à voter

  4. #4
    Membre régulier
    Homme Profil pro
    Touche à tout
    Inscrit en
    Mai 2017
    Messages
    434
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Allier (Auvergne)

    Informations professionnelles :
    Activité : Touche à tout

    Informations forums :
    Inscription : Mai 2017
    Messages : 434
    Points : 108
    Points
    108
    Par défaut
    Il n'existe pas un moyen en VBA dans le code
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    Operator:=xlBetween, Formula1:="=Informations!A2:A50"
    de mettre tout ça en forme? C'est à dire qu'il doit bien exister une formule pour dire :"Dans la colonne A, tu ne récupères que les cellules non vide et enlèves les doublons"...

    Merci par avance

  5. #5
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    Salut.

    Merci de préciser ta version d'Excel.

    Je suis d'accord avec Michaël. Jusqu'à la version 2016, j'utilisais des tableaux structurés spécifiques puis des listes nommées pour réaliser ce genre d'opérations. Avec 365, on peut travailler sur les plages dynamiques résultant de l'utilisation des fonctions UNIQUE, TRIER (TRIERPAR) et FILTRE qui permettent d'éviter les tableaux intermédiaires.

    Je suis aussi d'accord avec lui sur le fait que, sauf rarissime exception, la mise en place de listes de validation s'effectue lors de la conception du classeur et ne nécessite pas de VBA.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  6. #6
    Membre régulier
    Homme Profil pro
    Touche à tout
    Inscrit en
    Mai 2017
    Messages
    434
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Allier (Auvergne)

    Informations professionnelles :
    Activité : Touche à tout

    Informations forums :
    Inscription : Mai 2017
    Messages : 434
    Points : 108
    Points
    108
    Par défaut
    La version d'Excel est sous Office365. Après j'utilise VBA pour automatiser l'ensemble pour que cela soit transparent pour l'utilisateur , en plus cela éviterai les erreurs de supression ou modificatgion...). Après s'il existe une façon beaucoup plus simple je suis preneur :-)

  7. #7
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    Pour Office 365, avec 0 lignes de VBA:
    1. Travailler avec des tableaux structurés (ici, j'ai créé un tableau avec une colonne pour les départements de l'entreprise comprenant des doublons);
    2. Dans une feuille de paramètres, créer tes plages dynamiques avec les fonction TRIER(UNIQUE(...) (formule à saisir dans la première cellule, Excel étendant la plage dynamique aux cellules en dessous. Attention: ne rien mettre sous la cellule, la colonne doit être entièrement vide sous la cellule de formule pour pouvoir accueillir les valeurs);
    3. Nommer cette cellule (ici, DPTS);
    4. Dans le tableau structuré où tu souhaites placer la liste déroulante, utiliser une liste de validation en faisant suivre le nom de la plage du signe #.


    En plaçant la liste de validation dans la colonne d'un tableau structuré, tu t'assures qu'Excel étendra la liste de validation aux nouvelles saisies dans le tableau. Pour plus d'infos sur les tableaux structurés, voir mon tuto qui explique tout cela.


    Nom : 2021-01-14_131417.png
Affichages : 156
Taille : 13,3 Ko

    Nom : 2021-01-14_131433.png
Affichages : 128
Taille : 4,9 Ko

    Nom : 2021-01-14_131451.png
Affichages : 166
Taille : 14,9 Ko

    Nom : 2021-01-14_131505.png
Affichages : 136
Taille : 7,5 Ko

    Nom : 2021-01-14_131530.png
Affichages : 141
Taille : 10,7 Ko


    En complément, voici la formule pour supprimer les vides: =TRIER(UNIQUE(FILTRE(t_Départements[Dpt];t_Départements[Dpt]<>"")))
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  8. #8
    Membre régulier
    Homme Profil pro
    Touche à tout
    Inscrit en
    Mai 2017
    Messages
    434
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Allier (Auvergne)

    Informations professionnelles :
    Activité : Touche à tout

    Informations forums :
    Inscription : Mai 2017
    Messages : 434
    Points : 108
    Points
    108
    Par défaut
    Merci je vais regarder cela.

    Mais comme je suis un peu têtu... Si je mets cette fonction dans une cellule
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =UNIQUE(FILTRE(Informations!G2:G100;NON(ESTVIDE(Informations!G2:G100))))
    J'ai bien toutes mes valeurs uniques et sans vides... Alors pourquoi lorsque je tente de crééer une validation de données avec listes et cette fonction dans source j'ai une erreur??

  9. #9
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    Parce que les listes de validation, comme d'autres outils (Mise en forme conditionnelle notamment), n'ont pas évolué pour permettre d'utiliser ces fonctions qui créent des plages dynamiques ni les colonnes des tableaux structurés. L'astuce consiste donc à "leurrer" Excel en créant des plages nommées que ces outils savent manipuler.

    J'admets que l'explication peut sembler étrange puisque la liste de validation accepte le signe # (extension d'une cellule à "sa" plage dynamique, inconnu des autres versions), mais c'est la seule explication que je vois.

    D'une manière générale, les tableaux structurés et les plages dynamiques permettent une approche différente du vba et, lorsque la programmation en vba est encore nécessaire, allègent considérablement le code à produire.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

Discussions similaires

  1. [XL-2016] Liste déroulante sans ligne vide
    Par retraite83 dans le forum Macros et VBA Excel
    Réponses: 10
    Dernier message: 23/06/2018, 15h30
  2. [XL-MAC 2011] Remplissage Zone de liste déroulante sans doublons & non vides via VBA
    Par SuperKiwi dans le forum Macros et VBA Excel
    Réponses: 1
    Dernier message: 19/03/2014, 21h29
  3. [XL-2007] Liste déroulante sans vides
    Par apt dans le forum Macros et VBA Excel
    Réponses: 6
    Dernier message: 08/02/2012, 01h12
  4. Réponses: 2
    Dernier message: 22/09/2009, 18h56
  5. Liste déroulante sans valeur par défaut ?
    Par magic8392 dans le forum Balisage (X)HTML et validation W3C
    Réponses: 2
    Dernier message: 22/02/2005, 17h59

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo