Passer au contenu principal

Comment effectuer une saisie semi-automatique lors de la saisie dans la liste déroulante Excel?

Pour une liste déroulante de validation des données avec de nombreux éléments, vous devez faire défiler la liste vers le haut et vers le bas pour trouver celui dont vous avez besoin ou saisir correctement le mot entier dans la zone de liste. Existe-t-il un moyen de compléter automatiquement la liste déroulante lors de la saisie des caractères correspondants ? Cela aiderait les gens à travailler plus efficacement dans les feuilles de calcul avec des listes déroulantes dans les cellules. Ce didacticiel propose deux méthodes pour vous aider à y parvenir.

Rendre les listes déroulantes à saisie semi-automatique avec le code VBA
Créez facilement des listes déroulantes à saisie semi-automatique en 2 secondes
Plus de tutoriels pour la liste déroulante ...

Rendre les listes déroulantes à saisie semi-automatique avec le code VBA

Veuillez procéder comme suit pour créer une liste déroulante automatiquement après avoir tapé les lettres correspondantes dans la cellule.

Tout d'abord, vous devez insérer une zone de liste déroulante dans la feuille de calcul et modifier ses propriétés.

1. Ouvrez la feuille de calcul contenant les cellules de la liste déroulante que vous souhaitez rendre automatiques.

2. Avant d'insérer une zone de liste déroulante, vous devez ajouter l'onglet Développeur au ruban Excel. Si l'onglet Développeur s'affiche sur votre ruban, passer à l'étape 3. Sinon, procédez comme suit pour afficher l'onglet Développeur dans le ruban : Cliquez sur Déposez le > Options ouvrir le Options la fenêtre. Dans ce Options Excel fenêtre, cliquez sur Personnaliser le ruban dans le volet gauche, vérifiez le Développeur , puis cliquez sur le OK bouton. Voir la capture d'écran:

3. Cliquez Développeur > insérer > Zone de liste déroulante (contrôle ActiveX).

4. Dessinez une zone de liste déroulante dans la feuille de calcul actuelle. Faites un clic droit dessus puis sélectionnez biens dans le menu contextuel.

5. dans le biens boîte de dialogue, veuillez remplacer le texte original dans le (Nom) terrain avec TempCombo.

6. Éteignez le Mode de conception en cliquant Développeur > Mode de conception.

Ensuite, appliquez le code VBA ci-dessous

7. Cliquez avec le bouton droit sur l'onglet de la feuille actuelle et cliquez sur Voir le code dans le menu contextuel. Voir la capture d'écran:

8. Dans l'ouverture Microsoft Visual Basic pour applications , veuillez copier et coller le code VBA ci-dessous dans la fenêtre Code de la feuille de calcul.

Code VBA: saisie semi-automatique lors de la saisie dans la liste déroulante

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2020/01/16
    Dim xCombox As OLEObject
    Dim xStr As String
    Dim xWs As Worksheet
    Dim xArr
    
    Set xWs = Application.ActiveSheet
    On Error Resume Next
    Set xCombox = xWs.OLEObjects("TempCombo")
    With xCombox
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
    End With
    If Target.Validation.Type = 3 Then
        Target.Validation.InCellDropdown = False
        Cancel = True
        xStr = Target.Validation.Formula1
        xStr = Right(xStr, Len(xStr) - 1)
        If xStr = "" Then Exit Sub
        With xCombox
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 5
            .Height = Target.Height + 5
            .ListFillRange = xStr
            If .ListFillRange = "" Then
                xArr = Split(xStr, ",")
                Me.TempCombo.List = xArr
            End If
            .LinkedCell = Target.Address
        End With
        xCombox.Activate
        Me.TempCombo.DropDown
    End If
End Sub
Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case KeyCode
        Case 9
            Application.ActiveCell.Offset(0, 1).Activate
        Case 13
            Application.ActiveCell.Offset(1, 0).Activate
    End Select
End Sub

9. presse autre + Q touches simultanément pour fermer le Applications Microsoft Visual Basic fenêtre.

À partir de maintenant, lorsque vous cliquez sur une cellule de la liste déroulante, la liste déroulante s'affiche automatiquement. Vous pouvez commencer à taper la lettre pour compléter automatiquement l'élément correspondant dans la cellule sélectionnée. Voir la capture d'écran:

Remarque: Ce code ne fonctionne pas pour les cellules fusionnées.

Créez facilement une liste déroulante à saisie semi-automatique en 2 secondes

Pour la plupart des utilisateurs d'Excel, la méthode VBA ci-dessus est difficile à maîtriser. Mais avec le Liste déroulante consultable caractéristique de Kutools for Excel, vous pouvez facilement activer la saisie semi-automatique pour les listes déroulantes de validation des données dans une gamme, feuille de calcul actuelle, classeur actuel or une feuille de travail spécifique selon vos besoins en seulement 2 secondes. De plus, cette fonctionnalité est disponible pour toutes les versions d'Excel.

Conseil : Avant d'appliquer cet outil, veuillez installer Kutools for Excel d'abord. Aller au téléchargement gratuit maintenant.

  1. Sélectionnez Kutools > La liste déroulante > Liste déroulante consultable.
  2. Dans le Liste déroulante consultable boîte de dialogue, spécifiez où vous souhaitez appliquer cette fonctionnalité dans la Définir la portée section, puis cliquez sur OK pour enregistrer les paramètres.
Résultat

Une fois configuré, cliquer sur une cellule de la liste déroulante dans la plage spécifiée fera apparaître une zone de liste. Lorsque vous tapez des caractères, dès qu'un élément correspond exactement, le mot entier sera complété automatiquement.

Notes: Pour appliquer cette fonctionnalité, veuillez Télécharger et installer Kutools for Excel d'abord.

Articles Liés:

Comment créer une liste déroulante avec plusieurs cases à cocher dans Excel?
De nombreux utilisateurs d'Excel ont tendance à créer une liste déroulante avec plusieurs cases à cocher afin de sélectionner plusieurs éléments de la liste à la fois. En fait, vous ne pouvez pas créer une liste avec plusieurs cases à cocher avec la validation des données. Dans ce didacticiel, nous allons vous montrer deux méthodes pour créer une liste déroulante avec plusieurs cases à cocher dans Excel. Ce didacticiel fournit la méthode pour résoudre le problème.

Créer une liste déroulante à partir d'un autre classeur dans Excel
Il est assez facile de créer une liste déroulante de validation des données parmi les feuilles de calcul d'un classeur. Mais si les données de liste dont vous avez besoin pour la validation des données se trouvent dans un autre classeur, que feriez-vous? Dans ce didacticiel, vous apprendrez à créer une liste déroulante à partir d'un autre classeur dans Excel en détail.

Créer une liste déroulante consultable dans Excel
Pour une liste déroulante avec de nombreuses valeurs, trouver la bonne n'est pas une tâche facile. Auparavant, nous avons introduit une méthode de liste déroulante de remplissage automatique lorsque vous entrez la première lettre dans la liste déroulante. Outre la fonction de saisie semi-automatique, vous pouvez également rendre la liste déroulante consultable pour améliorer l'efficacité du travail en trouvant les valeurs appropriées dans la liste déroulante. Pour rendre la liste déroulante consultable, essayez la méthode de ce didacticiel.

Remplir automatiquement d'autres cellules lors de la sélection de valeurs dans la liste déroulante Excel
Supposons que vous ayez créé une liste déroulante basée sur les valeurs de la plage de cellules B8: B14. Lorsque vous sélectionnez une valeur dans la liste déroulante, vous souhaitez que les valeurs correspondantes dans la plage de cellules C8: C14 soient automatiquement renseignées dans une cellule sélectionnée. Pour résoudre le problème, les méthodes de ce didacticiel vous rendront service.

Plus de tutoriels pour la liste déroulante ...

Meilleurs outils de productivité bureautique

Caractéristiques populaires: Rechercher, mettre en évidence ou identifier les doublons   |  Supprimer les lignes vides   |  Combinez des colonnes ou des cellules sans perdre de données   |   Tour sans formule 
Super recherche: VSearchup à critères multiples  |   VSearch à valeurs multiples  |   Recherche virtuelle sur plusieurs feuilles   |   Recherche floue ....
Liste déroulante avancée: Créez rapidement une liste déroulante   |  Liste déroulante dépendante   |  Liste déroulante à sélection multiple ....
Gestionnaire de colonnes: Ajouter un nombre spécifique de colonnes  |  Déplacer les colonnes  |  Basculer l'état de visibilité des colonnes masquées  |  Comparer les plages et les colonnes 
Caractéristiques en vedette: Mise au point de la grille   |  Voir Design   |   Grande barre de formule   |  Gestionnaire de classeurs et de feuilles   |  Centre de ressources (Texte automatique)   |  Sélecteur de date   |  Combiner des feuilles de travail   |  Crypter/déchiffrer les cellules   |  Envoyer des e-mails par liste   |  Super filtre   |   Filtre spécial (filtre gras/italique/barré...) ...
Les 15 meilleurs ensembles d'outils:  12 Texte Outils (Ajouter du texte, Supprimer les caractères, ...)   |   50+ Graphique Types (Diagramme de Gantt, ...)   |   40+ Pratique Formules (Calculer l'âge en fonction de l'anniversaire, ...)   |   19 Insertion Outils (Insérer le code QR, Insérer une image à partir du chemin, ...)   |   12 Conversion Outils (Nombres en mots, Conversion des devises, ...)   |   7 Fusionner et fractionner Outils (Lignes de combinaison avancées, Cellules divisés, ...)   |   ... et plus

Boostez vos compétences Excel avec Kutools for Excelet faites l'expérience d'une efficacité comme jamais auparavant. Kutools for Excel Offre plus de 300 fonctionnalités avancées pour augmenter la productivité et gagner du temps.  Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...

onglet kte 201905

Office Tab Apporte une interface à onglets à Office et facilite grandement votre travail

  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
Comments (325)
No ratings yet. Be the first to rate!
Cristiane
Cristiane
This comment was minimized by the moderator on the site
Hi, many thanks for this material. It helped a lot.
Reply
0
0
catherine
catherine
This comment was minimized by the moderator on the site
Hello,

following on from my last comment:

I'm setting up 'standard' rows with the dropdown lists in them. These I'm wanting to copy below into an extensive spreadsheet.
Unfortunately the dropdown's don't copy down when I do that after using the VBA.
Is there a way to do whats described above?

Cheers,
Catherine
Reply
Report
0
0
crystal
crystal     catherine
This comment was minimized by the moderator on the site
Hi Catherine,
The autocomplete drop-down lists generated by VBA code cannot be copied. You can only copy selected items that are displayed in the drop-down cell. Sorry for the inconvenience.
Reply
Report
0
0
catherine
catherine
This comment was minimized by the moderator on the site
Hello,

Have implemented this successfully, so thank you for this page and code.
But now I have an associated problem...:

After doing all of the above points, the code works as described, but I cannot do any undo/redo actions in the entire spreadsheet.
Is there a way to turn this combo box on for when I want to work with the dropdowns and off for when I want to work in other cells? so that the undo/redo actions are possible again?
and note, yes I have turned the design mode off after doing the above steps, but it still doesn't help the problem.

Cheers,
Catherine
Reply
Report
0
0
crystal
crystal     catherine
This comment was minimized by the moderator on the site
Hi Catherine Foley,
If you want to use the undo operation on cells other than the drop-down cells, the following VBA code can help you. Please give it a try.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2022/09/22
    Dim xCombox As OLEObject
    Dim xStr As String
    Dim xWs As Worksheet
    Dim xArr
    
    On Error Resume Next

    Set xWs = Application.ActiveSheet
    
    Set xCombox = xWs.OLEObjects("TempCombo")
    If Target.Validation.Type <> 3 Then
        If xCombox.Visible Then
            xCombox.Visible = False
        End If
        Exit Sub
    End If
    
    With xCombox
        .ListFillRange = ""
        .LinkedCell = ""
'        .Visible = False
    End With

    Target.Validation.InCellDropdown = False
    Cancel = True
    xStr = Target.Validation.Formula1
    xStr = Right(xStr, Len(xStr) - 1)
    If xStr = "" Then Exit Sub
    With xCombox
        .Visible = True
        .Left = Target.Left
        .Top = Target.Top
        .Width = Target.Width + 5
        .Height = Target.Height + 5
        .ListFillRange = xStr
        If .ListFillRange = "" Then
            xArr = Split(xStr, ",")
            Me.TempCombo.List = xArr
        End If
        .LinkedCell = Target.Address
    End With
    xCombox.Activate
    Me.TempCombo.DropDown

End Sub
Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case KeyCode
        Case 9
            Application.ActiveCell.Offset(0, 1).Activate
        Case 13
            Application.ActiveCell.Offset(1, 0).Activate
    End Select
End Sub
Reply
Report
0
0
Yoni
Yoni
This comment was minimized by the moderator on the site
I hope now it will be upload
Reply
Report
0
0
Mbuchmeier
Mbuchmeier
This comment was minimized by the moderator on the site
This code works really well for a single drop down. However, if I have 5 dropdowns using the same list of values but they need to operate independently of each other how is that accomplished? Despite my best efforts, if I put more than one combo box on the page, any selection is one is mirrored in the other.
Reply
Report
0
0
crystal
crystal     Mbuchmeier
This comment was minimized by the moderator on the site
Hi Mbuchmeier,Only need one combo box on the page. The code works on all drop-down lists in current page. You just need to manually click on the drop-down list cell to activate the combo box.
Reply
Report
0
0
Yoni
Yoni     crystal
This comment was minimized by the moderator on the site
Hi crystal,
I am working with the combo box (The VBA code from 2021/11/05). It is working great and very useful but there are 2 issues:1. Issue #1: Usually when you work on any cell in Excel, there is indication on the row number which indicate which row you work about. When the combo box is opened this indication is missing2.  Issue #2: When the combo box is opened in the last column of the dynamic table, then it isn't  working as usual: when I select value from the list, the cell remain empty until I move the cursor to another cell
Attached screenshot.
Thank in advance
Reply
Report
0
0
crystal
crystal     Yoni
This comment was minimized by the moderator on the site
Hi Yoni,Thanks for your feedback. For the issues you mentioned:1. Issue #1: Excel does not highlight the row number when selecting an object in the worksheet. Since we use a combo box to replace the data validation drop-down list to handle the autocomplete operation, we can't show the indication in this case;2. Issue #2: I have tried as you described, but the problem cannot be reproduced. The screenshot you attached does not display, you need to save the screenshot and uplode it via the "Upload files" button below.
Reply
Report
0
0
Yoni
Yoni     crystal
This comment was minimized by the moderator on the site
Hi Crystal,Thanks a lot for your response. Attached the screenshot. hope it will work now
Reply
Report
0
0
crystal
crystal     Yoni
This comment was minimized by the moderator on the site
Hi Yoni,Sorry for the inconvenience. The screenshot you uploaded is still not shown on the page. You need to save the screenshot on your disk in advance and then uplode it via the "Upload files" button. See the attached screenshot.
View Attachments (1 / 5)
Report
0
0
B.K
B.K
This comment was minimized by the moderator on the site
will, thank you is not enugh :::: alot of thank you then ;;;;; that worked like magic :)
Reply
0
0
Nick M
Nick M
This comment was minimized by the moderator on the site
Yeah, basically completely useless.  Want the cell to auto-complete when typing in a cell using list data validation.Tried this, and now I have to start over from scratch because I can't Undo it.  Thanks.Also loaded with syntax errors.
Reply
0
0
Ahmed
Ahmed
This comment was minimized by the moderator on the site
It works as it should be except for two things, first, there is no validation after insertion. i.e. if I typed anything at the combo then clicked enter it will accept the typed value while it should not do this since the data validation is being used to prevent such behaviors and make sure that the entered data is from selected range. Second, at the data validation list, sometimes I use big range with empty cells and select ignore empty at validation list but the combo takes all the range and shows it, will be nice to remove the empty cells from the combo range.
Thanks & hope you can implement these things to make it perfect.
Reply
0
0
crystal
crystal     Ahmed
This comment was minimized by the moderator on the site
Hi, AhmedThe VBA below helps to solve the problem of blank cells. Since the Combo box accepts the value that are not in the list, we still can't find a way to solve this problem. Sorry for the inconvenience.
<div data-tag="code">Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2021/11/05
Dim xCombox As OLEObject
Dim xStr As String
Dim xWs As Worksheet
Dim xArr, xArr1
Dim xRg As Range
Dim xSrc As Variant
Dim xI, xJ, xIndex, xCount As Integer

Set xWs = Application.ActiveSheet
On Error Resume Next
Set xCombox = xWs.OLEObjects("TempCombo")
With xCombox
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
If Target.Validation.Type = 3 Then
Target.Validation.InCellDropdown = False
Cancel = True
xStr = Target.Validation.Formula1
xStr = Right(xStr, Len(xStr) - 1)
If xStr = "" Then Exit Sub
Set xRg = Range(xStr)
If xRg Is Nothing Then
xArr1 = Split(xStr, ",")
Else
ReDim xArr1(0 To xRg.Count - 1)
For xI = 0 To xRg.Count - 1
xArr1(xI) = xRg.Item(xI + 1).Value
Next
End If
xI = 0
xCount = UBound(xArr1) + 1
For xJ = 0 To UBound(xArr1)
If Replace(xArr1(xJ), " ", "") = "" Then xCount = xCount - 1
Next
ReDim xArr(0 To xCount - 1)
xIndex = 0
For xJ = 0 To UBound(xArr1)
If Replace(xArr1(xJ), " ", "") <> "" Then
xArr(xIndex) = xArr1(xJ)
xIndex = xIndex + 1
End If
Next
xStr = ""
With xCombox
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = xStr
If .ListFillRange = "" Then
'xArr = Split(xStr, ",")
Me.TempCombo.List = xArr
End If
.LinkedCell = Target.Address
End With
xCombox.Activate
Me.TempCombo.DropDown
End If
End Sub
Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case 9
Application.ActiveCell.Offset(0, 1).Activate
Case 13
Application.ActiveCell.Offset(1, 0).Activate
End Select
End Sub
Reply
Report
0
0
Yoni
Yoni     crystal
This comment was minimized by the moderator on the site
Hi Crystal,
I am working with the combo box (The VBA code from 2021/11/05). It is working great and so useful! thanks a lot.
I found the source of my problem. There was a line of freeze panes which override the combo box.
The only problem now is that the Undo is not working. Any idea?
Thanks in advanced,
Yoni
Reply
Report
0
0
crystal
crystal     Yoni
This comment was minimized by the moderator on the site
Hi,
The code does not support Undo. Sorry for the inconvenience.
Reply
Report
0
0
Yoni
Yoni     crystal
This comment was minimized by the moderator on the site
Thanks. Maybe do you know why Worksheet_Change event is not firing once I choose value from the combo box?
Reply
Report
0
0
Syu
Syu     crystal
This comment was minimized by the moderator on the site
Hi, could you please doublecheck this code, when I use it instead of the dropdown list appearing, the source for the dropdown appears instead. This is the exact function I want, could you please fix it.
Reply
0
0
Sven
Sven     Syu
This comment was minimized by the moderator on the site
If you use a named range or something similar, like I have with a table column, it will display the named range name instead of the values in that named range. To get what you want, you need to change the xStr to display the worksheet range of that named range, since the .ListFillRange does not take in named ranges directly. This should accomplish it : dim RangeAddress as String: RangeAddress = Range(YourNamedRange).addressand then make the xStr something like this: xStr = "YourWorksheetNameWithTheNamedRangeInIt!" & RangeAddress (important: add the '!' for the sheet reference)
This will make your xStr look something like: NamedRangeSheet!$A$1:$A$5
xStr = Right(xStr, Len(xStr) - 1)

If .ListFillRange = "" Then
xArr = Split(xStr, ",")
Me.TempCombo.List = xArr
End If

with these adjustments your combobox should display the list values instead of the source
Reply
0
0
crystal
crystal     Syu
This comment was minimized by the moderator on the site
Hi Syu,Sorry I don't quite understand your description. Can you try to be more specific of it?After applying the VBA code mentioned in the above method, when the cell with the data validation drop-down list is checked, the drop-down list turn into a combo box, and then all the items in the list are listed. 
Reply
Report
0
0
anton
anton     crystal
This comment was minimized by the moderator on the site
do you have an example file please?
Reply
0
0
xxxZIDANExxx
xxxZIDANExxx
This comment was minimized by the moderator on the site
Hello. The code is very cool. Please make sure that the formula "INDIRECT" is carried out and displayed. It is very necessary
Reply
0
0
Zaher
Zaher
This comment was minimized by the moderator on the site
The code is not working for a validation list which is created by vba code and the source is a named range
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations