Microsoft 365 liste déroulante adaptée ?

[Kan]

Bonjour à tous,

Je suis depuis plusieurs jours, à la recherche d'une liste déroulante, la plus adaptée pour mon tableau.
J'ai une liste de + de 300 fournisseurs, (pour certains contenant dans leur dénomination, des chiffres, et caractères spéciaux "*" "/" ...
ce qui fait que lorsque j'utilise la liste déroulante et je commence à taper le nom de mon fournisseur, ça ne fonctionne pas, je suis obliger d'utiliser le curseur pour descendre dans la liste, et le trouver...ça fait bcp !

Je me sert de ce type de tableau tous les jours....
(en pj le tableau test)

Avez vous une idée ou solution pour une liste déroulante qui dès la première lettre tapée ferait un tri ... ?

quelqu'un peu m'aider ?

d'avance merci pour vos retours !
Kan

 

[Kan]

merci !! oui dans la ligne 2 doit apparaitre les dernières valeurs, je veux bien la fonction personnalisée !

Pour le "Pro", j'ai apporté les modifs, (j'ai retiré, les validations de données ) et renommé ma liste "Laliste" .
dans la colonne G, lorsque je tape les premières lettres d'un fournisseur, il le remonte, mais je n'ai pas la poignée déroulante d'une liste déroulante. (j'ai l'impression que c'est de la recopie auto, ou mémoire...)

exemple : je tape AC et je fais "entré", il passe à la cellule en dessous.

alors que dans le tableau exemple test, j'ai la cellule avec la liste déroulante.
et lorsque je tape : AAA123, je fais "entré", et il me sort dans la liste déroulante tous les AAA123....

 

[job75]

Bonjour Kan, Pierre,

oui dans la ligne 2 doit apparaitre les dernières valeurs, je veux bien la fonction personnalisée !

C'est inutile, il n'y aura plus de référence circulaire en J2 avec cette formule :

=RECHERCHE("zzzz";J:J)

à condition qu'il y ait des textes sous J2 inférieurs à "zzzz".

Bien sûr mettez la même formule en D2 et G2.

A+

 

[pierrejean]

Salut Gerard
@Kan
As-tu recopié le code du dernier fichier que j'ai publié
Il y a des modifications

 

[job75]

Bonjour Kan, Pierre, le forum,

Si l'on veut obtenir une saisie intuitive les listes de validation ne vont pas, il faut une ComboBox.

Voyez le fichier joint et le code de la feuille "CDE" :

Dim liste() 'mémorise la variable

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim derlig&
ComboBox1.Visible = False
derlig = Range("G" & Rows.Count).End(xlUp).Row
If Intersect(ActiveCell, Range("G4:G" & derlig + 1)) Is Nothing Then Exit Sub
liste = Application.Transpose([Laliste])
ComboBox1.List = liste
ComboBox1.Top = ActiveCell.Top
ComboBox1.Left = ActiveCell.Left
ComboBox1 = ActiveCell
ComboBox1.Visible = True
ComboBox1.Activate
End Sub

Private Sub ComboBox1_Change()
If Not ComboBox1.Visible Then Exit Sub
Dim lig&
ComboBox1.List = Filter(liste, ComboBox1, True, vbTextCompare) 'merci Jacques Boisgontier
ComboBox1.DropDown
If ComboBox1.ListIndex > -1 Then ActiveCell.Value = ComboBox1
End Sub

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then ActiveCell.Offset(1).Select 'touche Entrée
End Sub

Merci à notre regretté Jacques BOISGONTIER pour l'utilisation de la fonction Filter.

J'ai mis aussi cette macro dans ThisWorkbook pour masquer ComboBox1 :

Private Sub Workbook_Open()
Application.Goto Sheets("CDE").[A1] 'pour masquer ComboBox1 car elle est vide
Me.Saved = True 'évite le message à la fermeture si aucune modification
End Sub

Le tableau de la feuille "CDE" est un tableau structuré pour que les formules et les formats soient copiés quand il s'agrandit.

J'ai revu la formule en colonne E.

A+

 

[Kan]

Bonjour PierreJean,
Bonjour Job75,

merci à vous 2 pour vos retours

j'ai essayé de reprendre les formules VBA de Job45, dans mon vrai tableau pro... malheureusement, j'ai des messages de "nom ambigu détecté" dans le workbook_open.

J'ai déjà dans ce work book Open une formule :
Private Sub Workbook_Open()
Sheets("SOMMAIRE").Select
End Sub

Comment puis je renommer le Sub Workbook_Open() ??

Je voudrais bien vous envoyer mon fichier (l'original) mais il est un peut lourd (66Mo avant de le passer en .lsb) maintenant il fait un peu plus de 6 Mo
, .. je peux vous l'envoyer par email si vous êtes ok pour y jeter un œil ? et se l'échanger ainsi.
j'ai plein de bug à l'ouverture dès que je mets les nouvelles formules...
((


d'avance merci pour votre aide !

kan

 

[job75]

Il faut bien sûr une seule macro Workbook_Open écrivez-la :

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Application.Goto Sheets("CDE").[A1] 'pour masquer ComboBox1 car elle est vide
Sheets("SOMMAIRE").Select
Me.Saved = True 'évite le message à la fermeture si aucune modification
End Sub

Pour les bugs à l'ouverture donnez des informations ou un fichier allégé.

 

[Kan]

merci Job75
je vais corrigé comme ça, et faire une version allégé
je reviens vers vous !

merci !
Kan

 

[Kan]

re bonjour Job75,

j'ai donc zippé le fichier qui fait déjà 5Mo. (j'avoue je ne comprend pas, il n'a presque rien dedans...)

voici ce que j'ai pu faire :
onglet Liste fournisseurs : j'ai rentré ma liste de fournisseurs qui s'appel : "Laliste"
Colonne G de "CDE" : j'ai collé, la vraie colonne du tableau "Pro"
du coup, j'ai l'impression qu'il y a 2 fois la valeur "liste" dans chaque cellule...

dans la colonne G + F, (les listes Clients et Fournisseurs) les formules vont jusqu'en bas du tableau.
(je ne sais pas comment faire pour annulé ou nettoyer ça)

ligne 2 dans "CDE" je ne suis pas arrivée à mettre les formules améliorées..; ??

j'ai une petite Macro qui s'appelle : "Aller à la dernière ligne"... elle ne fonctionne pas problème dans VBA "feuil 1 (CDE) le: Sub dernière-ligne() (surligné en jaune)

Actuellement mon tableau fait : 1145 lignes, je souhaite archiver les 986 premières lignes (qui correspondent à l'année précédente, comment faire sans masquer tous ?


j'ai rajouté une onglet "Sommaire" et "BDC" bon de commande, pour la suite.
mon bon de commande pour avoir son numéro : devrait rappeler :
colonne A :
colonne B : mois
Colonne C : Année
Colonne C : Type
Colonne E : n°

(avec la formule = puis je click sur la cellule concernée en ligne 2) à moins qui'l y ai plus léger ou plus simple...

merci beaucoup pour l'aide apportée ! j'espère que sur toutes ces problématiques, quelques unes seront solutionnées !!

encore merci !

@++

Kan

 

[job75]

En feuille CDE mettez en ligne 2 et colonne E les formules que j'ai données, ce n'est pas compliqué !

Supprimez les listes de validation en colonne G.

Passez en mode Création pour agrandir la ComboBox à la largeur de la colonne G.

Enfin créez le tableau structuré (menu Insertion => Tableau).

 

[Kan]

Merci Job 75, je fais les modifs et je reviens vers vous !
Bonne soirée
Kan

 

[Kan]

bonjour Job75,
voici le fichier (toujours aussi lourd 5Mo) en Zip.
dans la colonne G = les lignes 1145 +1146 n'ont pas la liste qui s'affiche,
dans la ligne 1147 j'ai fais un essai, d'incrémentation d'un nouveau n° en colonne E, et la liste ne s'affiche pas.
j'ai essayé d'effacer la ligne 1147, et j'ai un message : " Opération importante"
l'action que vous êtes sur le point de faire concerne un nombre important de cellule...
et après : la mémoire disponible est insuffisante pour faire cette action. sélectionner moins de donner ou passer en version 64bits.
(je n'ai pas cette version de disponible)


du coup je ne peux pas le mettre en PJ, en zip il fait 1,04Mo
D'avance merci pour votre retour,

KAN

 

[Kan]

re bonjour Job75 !
finalement j'ai réussi à le passer à 5Mo (j'ai changé le format en XLS)
le voici en PJ.
pouvez vous m'aider pour les lignes en colonne G dont la liste ne s'affiche pas ?

 

[job75]

Bonjour Kan, le forum,

Vous avez enregistré le fichier en .xls et comme ce format n'accepte pas les tableaux structurés vous avez corrompu la feuille "CDE".

J'ai dû refaire cette feuille, enregistré le fichier en .xlsm et créé le tableau structuré A4:T1146.

Alors il n'y a plus de problème pour les cellules G1144 G1145 G1146.

Nota 1 : j'ai mis en E2 la bonne formule =SI(LIGNE()=4;1865;DECALER(E4;-1; )+1)

Elle fonctionne même si l'on supprime des lignes.

Nota 2 : mettre "*" en 1ère cellule de la liste fournisseurs n'a aucun sens, je l'ai supprimé.

Nota 3 : le fichier "pèse" maintenant 201 Ko...

A+

 

[Kan]

Merci beaucoup Job75 !!
j'avoue je suis super contente .. vraiment super contente du résultat, je n'aurai pas su faire seule ce travail !! en plus le tableau est super léger maintenant (merci pour la leçon !!)

donc, la formule dans E2 impeccable ! le choix fournisseur se fait parfaitement bien !!

dans l'onglet "CDE" , j'ai une petite macro qui s'appelle : dernière ligne.
lorsque je clic j'ai un message d'erreur de compilation: variable non définie
Sub dernière_linge() (surligné en jaune)
et le "n" de For n = cells (surligné en bleu)
(voir pj)
comment puis je le corriger ?

aussi, maintenant je fais faire un autre onglet : liste clients
qui aura sa liste déroulante en colonne J de l'onglet "CDE"
dans Visual basic (feuil1(CDE), est ce que peux rajouter à la suite le même code VBA de la liste fournisseurs ? (cette liste je l''appellerai d'un autre nom que "Laliste" bien sûr )
tous ces codes à la suite peuvent fonctionner ?

un grand merci Job75 pour votre aide !
à vous lire dès que possible...

@ bientôt !

Kan

 

[job75]

dans l'onglet "CDE" , j'ai une petite macro qui s'appelle : dernière ligne.
lorsque je clic j'ai un message d'erreur de compilation: variable non définie

Avec Option Explicit il faut définir toutes les variables.

Donc définissez la variable n avec Dim n As Long ou Dim n&

Mais à mon avis cette macro ne sert à rien puisqu'on peut utiliser la barre de défilement, supprimez-la.

Quant à la colonne J on peut utiliser la même ComboBox qu'en colonne G.

Voyez ce fichier (3) avec la liste des clients et le code complété.

PS : en colonne J il y avait de nombreux espaces superflus dans les noms des clients, je les ai supprimés.