texto - Excel 2010: cómo utilizar autocompletar en la lista de validación

opciones de autorrelleno excel

Question

Estoy usando una gran lista de validación de la que dependen un par de funciones vlookup (). Esta lista se está haciendo cada vez más grande. ¿Hay alguna forma de escribir las primeras letras del elemento de la lista que estoy buscando, en lugar de desplazarme manualmente por la lista en busca del elemento?

He hecho algunas búsquedas en Google, pero esto sugiere que esto es posible en versiones anteriores de Excel, pero no en Excel 2010. Espero que puedan ayudar.

Answer 1

Aquí hay otra opción. Funciona al poner un ActiveX ComboBox en la parte superior de la celda con la validación habilitada, y luego proporcionar autocompletar en el ComboBox en su lugar.

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

Answer 2

Como otras personas sugirieron, necesitas usar un combobox. Sin embargo, la mayoría de los tutoriales le muestran cómo configurar solo un cuadro combinado y el proceso es bastante tedioso.

Como enfrenté este problema antes, al ingresar una gran cantidad de datos de una lista, puedo sugerirle que use excelautocomplete.com . Le ayuda a crear el cuadro combinado en las celdas que seleccione y puede definir una lista para que aparezca en el menú desplegable.

Answer 3

Esta es una muy buena manera de manejar esto (que se encuentra en ozgrid ):

Digamos que su lista está en la Sheet2 y desea utilizar la Lista de validación con Autocompletar en la Sheet1 .

En la Sheet1 A1 Ingrese =Sheet2!A1 y copie e incluya tantas filas de repuesto como sea necesario (por ejemplo, 300 filas en total). Oculte estas filas y use esta fórmula en Se refiere a: para un rango dinámico denominado MyList:

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

Ahora en la celda inmediatamente debajo de la última fila oculta use Validación de datos y para el Origen de la lista use =MyList

[EDITAR] Versión adaptada para Excel 2007+ (aunque no se pudo probar en 2010, pero AFAIK, no hay nada realmente específico para una versión).
Digamos que su fuente de datos está en Sheet2!A1:A300 y asumamos que su lista de validación (también conocida como autocompletar ) está en la celda Sheet1!A1 .

1. Cree un rango dinámico llamado MyList que dependerá del valor de la celda donde coloque la validación

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

2. Agregue la lista de validación en la celda Sheet1!A1 que se referirá a la lista =MyList

Advertencias

1. Esto no es un autocompletado real ya que primero debe escribir y luego hacer clic en la flecha de validación: la lista comenzará en el primer elemento coincidente de su lista

2. La lista irá hasta el final de sus datos. Si desea ser más preciso (mantenga en la lista solo los elementos coincidentes ), puede cambiar el COUNTA con un SUMLPRODUCT que calculará el número de elementos coincidentes

3. Su lista de fuentes debe estar ordenada

Answer 4

Excel lo hace automáticamente cada vez que tiene una columna vertical de elementos. Si selecciona la celda en blanco debajo (o arriba) de la columna y comienza a escribir, se completa automáticamente en función de todo lo que hay en la columna.

Answer 5

Sobre la base de la respuesta de , use esta fórmula para el rango dinámico denominado para hacer que la solución funcione para varias filas:

=OFFSET(Sheet2!$A$1,MATCH(INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN(),4))&"*",Sheet2!$A$1:$A$300,0)-1,0,COUNTA(Sheet2!$A:$A))

Answer 6

=OFFSET(NameList!$A$2:$A$200,MATCH(INDIRECT("FillData!"&ADDRESS(ROW(),COLUMN(),4))&"*",NameList!$A$2:$A$200,0)-1,0,COUNTIF($A$2:$A$200,INDIRECT("FillData!"&ADDRESS(ROW(),COLUMN(),4))&"*"),1)

1. Crear nombre de hoja como lista de Namelist . En la columna A rellene la lista de datos.

2. Cree otro nombre de hoja como FillData para hacer la lista de validación de datos que desee.

3. Escriba el primer alfabeto y seleccione, el menú desplegable aparecerá dependiendo de su tipo.