Formulario de contacto

Nombre

Correo electrónico *

Mensaje *

domingo, 26 de julio de 2015

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.


Image result for coffee icon


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+

23 comentarios:

  1. Excelente aporte... Ahora tengo una pregunta que seguramente mas de uno ha tenido el problema:
    1) Se tiene una lista de cliente de mas de 2.000 en una hoja1 llamada CLIENTES
    2) Se debe capturar en la hoja 2 (en este caso es un CRM) llamada CRM en cada linea el nombre del cliente como se puede hacer esa lista filtrada en cada celda de esa columna.
    Gracias de nuevo

    ResponderEliminar
    Respuestas
    1. Hola Sergio, si entendí bien, lo que necesitas es poner un burcarV para traer el nombre del cliente y ajustar esa columna a la lista desplegable. Saludos

      Eliminar
  2. Muy buen aporte... una presunta... quiero crear muchas celdas de este tipo pero en diferentes hojas... como lo puedo hacer?

    ResponderEliminar
  3. geniales sus ejemplos, ahora a practicar

    ResponderEliminar
  4. Acabo de leer (y probar) esta entrada cuatro años después y la verdad es que me funciona perfectamente y se adapta a lo que buscaba.

    El problema que encuentro es que si lo aplico a un origen de datos de unas 8000 filas se ralentiza muchísimo.

    ¿Habría alguna forma de evitar eso?

    Muchas gracias y un saludo.

    Carlos.

    ResponderEliminar
    Respuestas
    1. Muchas gracias!!! Ya entiendo... habría que revisarlo todo para ver como se puede ir mas rapido, o bien mediante macros, claro está.

      Eliminar
  5. excelente aporte, ahora bien si deseo aplicar el combobox a mas de una celda con el propósito de seleccionar varios elementos de la una misma lista, de que manera podría hacerlo? la idea es de montar una factura en la cual pueda montar hasta 10 artículos de una base de 10.000 registros gracias

    ResponderEliminar
  6. Hola, pues tendras que hacerlo una a una. Suerte!

    ResponderEliminar
  7. Excelente aporte, me gustaria realizar algo parecido a esto
    https://www.youtube.com/watch?v=kOqiub5NpBQ&ab_channel=SipoapSoftware
    Estoy buscando la informacion necesaraia para hacerlo

    ResponderEliminar
  8. hola, tengo problemas con la compilación de Visual basic, me da error o me lleva a la ventada de codigo cuando intento escribir algo en el objeto.. por favor su ayudaaa siento que estoy muy cercaa

    ResponderEliminar
  9. hola!! generé mi informacion con su aporte, el unico problema que presento es que algunas celdas, cuando selecciono en la lista desplegable, me salen en blanco y tengo que reseleccionar el nombre (solo algunas líneas) y el otro es que, cualquier movimiento de renglon, se vuelve a desplegar la lista. Como podria solucionar esto???

    Gracias.

    ResponderEliminar
    Respuestas
    1. no se si tu problema sigue, si eso mandame un email a elsabiodeexcel@gmail.com

      Eliminar
  10. Estoy copiando tal cual el codigo y da error. (ComboBox1.ListFillRange = "Lista"Me.ComboBox1.DropDownEnd Sub) esta parte pone las letras en rojo. que debo hacer?

    ResponderEliminar
    Respuestas
    1. Perdona, ha de escribirse asi:


      Private Sub ComboBox1_Change()
      ComboBox1.ListFillRange = "Lista"
      Me.ComboBox1.DropDown
      End Sub

      Eliminar