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.
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
.
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))
ajouter la liste de validation sur la cellule
Sheet1!A1
qui refert à la liste=MyList
mises en garde
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
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 unSUMLPRODUCT
qui va calculer le nombre d'éléments correspondantsvotre liste de sources doit être triée
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.
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))
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
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.
=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)
créer le nom de la feuille comme
Namelist
. Dans la colonne a, remplissez la liste des données.créer un autre nom de feuille comme
FillData
pour faire de la validation des données de la liste que vous le souhaitez.tapez le premier alphabet et sélectionnez, le menu déroulant apparaîtra dépend de votre type.