Lista desplegable con sugerencias de búsqueda

Las listas desplegables son buenísimas, pero también estaría genial que una lista desplegable se fuera filtrando a medida que se escribía en ella. Un poco como cuando escribimos en Google. Pues hoy vemos como conseguir ese efecto. Imaginemos que tenemos una lista con países y una lista desplegable con esos países. Lo que queremos es por ejemplo al introducir un texto en la lista, la lista se filtre por los países que contienen ese texto. El efecto que queremos se puede ver a continuación:

Y cómo creamos este efecto?

Hay 3 partes para la creación de esta sugerencia de búsqueda en la lista desplegable:

• Configuración del cuadro de búsqueda
• Ajuste de los datos
• Introducir un poco de código VBA para trabajar

Paso 1 - Configuración del cuadro de búsqueda

Vamos a utilizar un cuadro combinado (control ActiveX) y configurarlo de modo se pueda escribir en él y a la vez se copie también en una celda. Estos son los pasos para hacer esto:

1. Ir a la ficha Desarrollador > Insertar > Controles ActiveX -> Cuadro combinado (control ActiveX).

Extra:

Si aún no disponer de la pestaña Desarrollador, haz lo siguiente:

Archivo > Opciones > Personalizar cinta de opciones 

Ahora en las pestañas principales selecciona Desarrollador para activarla.


                          


2. Haz clic en cualquier parte de la hoja e inserta el cuadro combinado

3. Haz clic derecho en el cuadro combinado y seleccione Propiedades

4. En el Cuadro de diálogo de Propiedades, introduce los siguientes cambios

• AutoWordSelect: False
• LinkedCell: F2 (o la celda que tu prefieras)
• ListFillRange: Lista (o el nombre que le quieras dar)
• MatchEntry: 2 - fmMatchEntryNone

------------------------------------------------------------------------------------------------------------------------

RECUERDA QUE:

Tú puedes colaborar para seguir manteniendo este blog de una manera gratuita haciendo varios clics en los anuncios de la página. Como referencia, 2 clics por artículo. Muchas gracias por querer colaborar.

------------------------------------------------------------------------------------------------------------------------

5. Ir al menú Desarrollador y haz clic en el Modo diseño, lo cual nos permitirá introducir texto en el cuadro combinado. Además, dado que la celda F2 está vinculada al cuadro combinado, cualquier texto que introduzca en el cuadro combinado se reflejará en F2 en tiempo real.

Paso 2 - Ajustando los datos

En el paso 1 ya hemos creado y configurado el cuadro combinado, ahora lo que neceistamos en ajustar los datos para que tan pronto como se escribe algo en el cuadro de combinado, se muestre sólo aquellos valores que contengan ese texto. En este paso, crearemos tres columnas auxiliares y un rango dinámico definido (este es el rango al cual nos referimos en el Paso 1 - Lista).

Columna Auxiliar 1:

Introduce la siguiente fórmula en la celda B2 y copiala al rango B3:B21

=--ESNUMERO(SI.ERROR(HALLAR($F$2,A2,1),""))


Lo que hace esta fórmula es devolver 1 cuando el texto en el cuadro combinado está contenido en el nombre del país a la izquierda. Por ejemplo, si escribe "guay", a continuación, sólo los valores de Paraguay y Uruguay son 1 y todos los valores restantes son 0.

Columna Auxiliar 2:

Introduce la siguiente fórmula en la celda C2 y arrastrarlo por toda la columna (C3:C21)

=SI(B2=1,CONTAR.SI($B$2:B2,1),"")


Esta fórmula devolverá 1 para el primer pais que contenga el texto introducido en el cuadro combinado, 2 para la segunda aparición, 3 para el tercero y así sucesivamente. Por ejemplo, si escribe 'guay', la celda C9 mostrará 1, ya que coincide con Paraguay, y la celda C11 mostrará 2, ya que coincide con Uruguay, dejando el resto de las celdas en blanco.

Columna Auxiliar 3:

Introduce la siguiente fórmula en la celda D2 y arrastrarlo por toda la columna (D3:D21)

=SI.ERROR(INDICE($A$2:$A$21,COINCIDIR(FILAS($C$2:C2),$C$2:$C$21,0)),"")

Esta fórmula apila todos los nombres de los países que coinciden juntos sin ningún celdas en blanco entre ellos. Por ejemplo, si escribe 'guay', esta columna mostraría Paraguay y Uruguay juntos y el resto de celdas estaría en blanco.

Asignamos un nombre al rango dinámico

Una vez que las columnas auxiliares han sido creados, debemos de crear un rango dinámico y asignarle un nombre. La idea es mostrar sólo los valores en el rango con nombre que coincide con el texto en el cuadro combinado. Este rango dinámico con nombre se utiliza para mostrar la lista desplegable en el cuadro combinado. Por consiguiente se asigna un nombre con una fórmula. Estos son los pasos para crear el rango dinámico con nombre:

Ir a las Fórmulas > Asignar nombres

Se abrirá un cuadro de diálogo Nombre nuevo. En el Nombre introducir Lista y el texto de se refiere a introducir la siguiente fórmula:

=$D$2:INDICE($D$2:$D$21,MAX($C$2:$C$21),1)

Paso 3 - Ajustando los datos

Ya casi estamos. La parte final es utilizar un simple código de VBA. Este código activa la lista desplegable según se está escribiendo en el cuadro de búsqueda. Para añadir el código al libro:

• Haga clic derecho sobre la pestaña de la hoja de trabajo y seleccione Ver código.
• En la ventana de VBA, copia y pega el siguiente código:

Private Sub ComboBox1_Change ()

ComboBox1.ListFillRange = "Lista"Me.ComboBox1.DropDownEnd Sub

Y ya está! Ya has creado el efecto deseado. Para una mejor apariencia, se puede cubrir la celda F2 con el cuadro combinado y ocultar todas las columnas que tienen fórmulas. 

Te ha gustado el artículo?

No te olvides de mostrar tu gratitud hacia el autor (yo) mediante una de las 3 siguientes acciones:

1. Puedes invitarme a un café pinchando en la siguiente imagen.

2. Puedes hacer clic en los anuncios mostrados en la página

3. Puedes recomendar el artículo en Google haciendo clic en g+1 (justo después del artículo), también puedes compartirlo en Facebook, y/o dejar comentarios.

Descarga:

Para descargarse el archivo con el ejemplo ve al siguiente enlace:

Lista desplegable con sugerencias de búsqueda

Aprende a crear los mejores dashboards 

Pincha aquí para ver los mejores ejemplos.

Subscríbete y recibe todos las entradas por email

Para ello regístrate suministrando tu email en el sitio "Recibe los trucos en tu email" del menú de la derecha.

O sígueme por las redes sociales

Facebook, Twitter, Youtube y Google+