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.