Excel 2010: comment utiliser autocomplete dans la liste de validation

j'utilise une grande liste de validation dont dépendent quelques fonctions de vlookup (). Cette liste est de plus en plus large. Y a-t-il un moyen de taper les premières lettres de l'élément de liste que je cherche, au lieu de faire défiler manuellement la liste à la recherche de l'élément?

j'ai fait quelques recherches sur Google mais cela suggère que c'est en effet possible dans les versions précédentes D'Excel, mais pas dans Excel 2010. Espérons que vous les gars peut vous aider.

15
demandé sur ashleedawg 2011-11-03 06:53:22

6 réponses

Ici est une très bonne façon de gérer cela (trouvé sur ozgrid):

disons que votre liste est sur Sheet2 et vous souhaitez utiliser la liste de Validation avec AutoComplete sur Sheet1.

Sheet1 A1 Entrée =Sheet2!A1 et copiez vers le bas en incluant autant de lignes de rechange que nécessaire (disons 300 lignes au total). Masquer ces lignes et l'utilisation de cette formule dans la Désigne: pour une dynamique de la plage nommée appelé Maliste:

=OFFSET(Sheet1!$A,0,0,MATCH("*",Sheet1!$A:$A0,-1),1)

Maintenant dans la cellule immédiatement sous la dernière ligne cachée, utilisez la Validation des données et pour la liste, utilisez=MyList

[EDIT] version adaptée pour Excel 2007+ (Impossible de tester sur 2010 mais AFAIK, il n'y a rien de vraiment spécifique à une version).

Disons que votre source de données est sur Sheet2!A1:A300 et supposons votre liste de validation (aka saisie semi-automatique) est sur la cellule Sheet1!A1.

  1. Créer une dynamique plage nommée MyList cela va dépendre de la la valeur de la cellule où vous avez mis la validation

    =OFFSET(Sheet2!$A,MATCH(Sheet1!$A&"*",Sheet2!$A:$A0,0)-1,0,COUNTA(Sheet2!$A:$A))

  2. ajouter la liste de validation sur la cellule Sheet1!A1 qui refert à la liste =MyList

mises en garde

  1. Ce n'est pas un réel saisie semi-automatique que vous avez à taper d'abord et ensuite cliquez sur la validation de la flèche : la liste sera alors begin le premier élément correspondant de votre liste

  2. la liste ira jusqu'à la fin de vos données. Si vous voulez être plus précis (dans la liste uniquement de la correspondance éléments), vous pouvez modifier le COUNTA avec un SUMLPRODUCT qui va calculer le nombre d'éléments correspondants

  3. votre liste de sources doit être triée

11
répondu JMax 2018-02-24 14:03:13

Excel le fait automatiquement chaque fois que vous avez une colonne verticale d'articles. Si vous sélectionnez la case vide ci-dessous (ou au-dessus) la colonne et commencez à taper, il fait autocomplete basé sur tout dans la colonne.

1
répondu Joel Spolsky 2011-11-03 03:04:35

en S'appuyant sur la réponse de JMax, utilisez cette formule pour la gamme dynamic named pour faire fonctionner la solution pour plusieurs lignes:

=OFFSET(Sheet2!$A,MATCH(INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN(),4))&"*",Sheet2!$A:$A0,0)-1,0,COUNTA(Sheet2!$A:$A))
1
répondu gwapongkabayo 2018-02-23 10:28:26

Voici une autre option. Il fonctionne en plaçant un ComboBox ActiveX sur le dessus de la cellule avec validation activée, puis en fournissant autocomplete dans le ComboBox à la place.

Option Explicit

' Autocomplete - replacing validation lists with ActiveX ComboBox
'
' Usage:
'   1. Copy this code into a module named m_autocomplete
'   2. Go to Tools / References and make sure "Microsoft Forms 2.0 Object Library" is checked
'   3. Copy and paste the following code to the worksheet where you want autocomplete
'      ------------------------------------------------------------------------------------------------------
'      - autocomplete
'      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'          m_autocomplete.SelectionChangeHandler Target
'      End Sub
'      Private Sub AutoComplete_Combo_KeyDown(ByVal KeyCode As msforms.ReturnInteger, ByVal Shift As Integer)
'          m_autocomplete.KeyDownHandler KeyCode, Shift
'      End Sub
'      Private Sub AutoComplete_Combo_Click()
'          m_autocomplete.AutoComplete_Combo_Click
'      End Sub
'      ------------------------------------------------------------------------------------------------------

' When the combobox is clicked, it should dropdown (expand)
Public Sub AutoComplete_Combo_Click()
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim cbo As OLEObject: Set cbo = GetComboBoxObject(ws)
    Dim cb As ComboBox: Set cb = cbo.Object
    If cbo.Visible Then cb.DropDown
End Sub

' Make it easier to navigate between cells
Public Sub KeyDownHandler(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Const UP As Integer = -1
    Const DOWN As Integer = 1

    Const K_TAB_______ As Integer = 9
    Const K_ENTER_____ As Integer = 13
    Const K_ARROW_UP__ As Integer = 38
    Const K_ARROW_DOWN As Integer = 40

    Dim direction As Integer: direction = 0

    If Shift = 0 And KeyCode = K_TAB_______ Then direction = DOWN
    If Shift = 0 And KeyCode = K_ENTER_____ Then direction = DOWN
    If Shift = 1 And KeyCode = K_TAB_______ Then direction = UP
    If Shift = 1 And KeyCode = K_ENTER_____ Then direction = UP
    If Shift = 1 And KeyCode = K_ARROW_UP__ Then direction = UP
    If Shift = 1 And KeyCode = K_ARROW_DOWN Then direction = DOWN

    If direction <> 0 Then ActiveCell.Offset(direction, 0).Activate

    AutoComplete_Combo_Click
End Sub

Public Sub SelectionChangeHandler(ByVal Target As Range)
    On Error GoTo errHandler

    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim cbo As OLEObject: Set cbo = GetComboBoxObject(ws)
    Dim cb As ComboBox: Set cb = cbo.Object

    ' Try to hide the ComboBox. This might be buggy...
    If cbo.Visible Then
        cbo.Left = 10
        cbo.Top = 10
        cbo.ListFillRange = ""
        cbo.LinkedCell = ""
        cbo.Visible = False
        Application.ScreenUpdating = True
        ActiveSheet.Calculate
        ActiveWindow.SmallScroll
        Application.WindowState = Application.WindowState
        DoEvents
    End If

    If Not HasValidationList(Target) Then GoTo ex

    Application.EnableEvents = False

    ' TODO: the code below is a little fragile
    Dim lfr As String
    lfr = Mid(Target.Validation.Formula1, 2)
    lfr = Replace(lfr, "INDIREKTE", "") ' norwegian
    lfr = Replace(lfr, "INDIRECT", "") ' english
    lfr = Replace(lfr, """", "")
    lfr = Application.Range(lfr).Address(External:=True)

    cbo.ListFillRange = lfr
    cbo.Visible = True
    cbo.Left = Target.Left
    cbo.Top = Target.Top
    cbo.Height = Target.Height + 5
    cbo.Width = Target.Width + 15
    cbo.LinkedCell = Target.Address(External:=True)
    cbo.Activate
    cb.SelStart = 0
    cb.SelLength = cb.TextLength
    cb.DropDown

    GoTo ex

errHandler:
    Debug.Print "Error"
    Debug.Print Err.Number
    Debug.Print Err.Description
ex:
    Application.EnableEvents = True
End Sub

' Does the cell have a validation list?
Function HasValidationList(Cell As Range) As Boolean
    HasValidationList = False
    On Error GoTo ex
    If Cell.Validation.Type = xlValidateList Then HasValidationList = True
ex:
End Function

' Retrieve or create the ComboBox
Function GetComboBoxObject(ws As Worksheet) As OLEObject
    Dim cbo As OLEObject
    On Error Resume Next
    Set cbo = ws.OLEObjects("AutoComplete_Combo")
    On Error GoTo 0
    If cbo Is Nothing Then
        'Dim EnableSelection As Integer: EnableSelection = ws.EnableSelection
        Dim ProtectContents As Boolean: ProtectContents = ws.ProtectContents

        Debug.Print "Lager AutoComplete_Combo"
        If ProtectContents Then ws.Unprotect
        Set cbo = ws.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, DisplayAsIcon:=False, _
                            Left:=50, Top:=18.75, Width:=129, Height:=18.75)
        cbo.name = "AutoComplete_Combo"
        cbo.Object.MatchRequired = True
        cbo.Object.ListRows = 12
        If ProtectContents Then ws.Protect
    End If
    Set GetComboBoxObject = cbo
End Function
0
répondu tomashm 2017-07-28 13:55:34

comme d'autres personnes l'ont suggéré, vous devez utiliser un combobox. Cependant, la plupart des tutoriels vous montrent comment configurer un seul combobox et le processus est assez fastidieux.

comme j'ai fait face à ce problème avant de saisir une grande quantité de données à partir d'une liste, je peux vous suggérer d'utiliser cette saisie semi-automatique add-in . Il vous aide à créer le combobox sur toutes les cellules que vous sélectionnez et vous pouvez définir une liste à apparaître dans la liste déroulante.

0
répondu Donna C 2017-11-20 00:40:09
=OFFSET(NameList!$A:$A0,MATCH(INDIRECT("FillData!"&ADDRESS(ROW(),COLUMN(),4))&"*",NameList!$A:$A0,0)-1,0,COUNTIF($A:$A0,INDIRECT("FillData!"&ADDRESS(ROW(),COLUMN(),4))&"*"),1)
  1. créer le nom de la feuille comme Namelist. Dans la colonne a, remplissez la liste des données.

  2. créer un autre nom de feuille comme FillData pour faire de la validation des données de la liste que vous le souhaitez.

  3. tapez le premier alphabet et sélectionnez, le menu déroulant apparaîtra dépend de votre type.

0
répondu Anan Phungmit 2018-02-23 10:27:06