Lista desplegable con combobox dinámico

sábado, mayo 11, 2013

En el pasado mostramos que una de las limitaciones de crear listas desplegables con Validación de Datos es la falta de la propiedad "autocompletar". En una nota del año 2008 mostramos como usar un cuadro combinado (combobox) de la colección de controles ActiveX para superar esta limitación.

Desde entonces he recibido muchas consultas sobre el tema. La más común es cómo hacer que el control aparezca en la celda activa y desaparezca después de haber elegido el valor.

Es decir, queremos mimetizar el comportamiento de las listas creadas con validación de datos, pero usando el cuadro combinado para disfrutar de la propiedad "autocompletar".

La técnica para hacerlo incluye, obviamente programar eventos (Vba). Mostraré aquí un ejemplo sencillo, que puede aplicarse a todo modelo. En nuestro ejemplo definimos un rango que contiene los nombres de los días de la semana, que será la fuente de los valores del combobox, y un rango en la hoja donde queremos utilizar el control.

Los pasos son los siguientes:

1 – Creamos un nombre definido que se refiere al rango que contiene los días de la semana



En este ejemplo usamos el cuadro de nombres para crear el nombre con facilidad. El rango está en la hoja "valores".

2 – En la hoja "lista" incrustamos un control cuadro combinado (combobox) de la colección de controles ActiveX



Ubicamos el control en el lugar deseado (en nuestro caso sobre la celda B2) asegurándonos que ocupe toda la celda.
En la propiedad ListFillRange ponemos el nombre definido que se refiere a la lista de valores (dia_semana).



De ser necesario cambiamos la definición de la fuente a un tamaño adecuado cambiando la definición de la propiedad Font.

Al finalizar el proceso desactivamos el botón "Modo Diseño" (lo controles no funcionan cuando Excel se encuentra en modo de diseño).

3 – Definimos el rango de la hoja "Lista"", donde queremos que aparezcan los controles cuando seleccionamos alguna celda del rango. En nuestro ejemplo el rango es B2:B20 ("rngDia")



4 – Programamos el evento Worksheet_SelectionChange en la hoja "lista" de manera que cuando el usuario seleccione alguna celda del rango "rngDia", el cuadro combinado aparezca sobre esa celda. El código, que va en el módulo de la hoja, es el siguiente

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Union(Target, Range("rngDia")).Address = Range("rngDia").Address Then
        With ComboBox1
            .Visible = True
            .Top = ActiveCell.Top
            .LinkedCell = ActiveCell.Address
        End With
    Else
        ComboBox1.Visible = False
    End If
End Sub


Lo que hace este código lo siguientes:
- Vuelve visible el control (cuando la celda activa no está en el rango deseado, el control es invisible, lo que hacemos definiendo la propiedad Visible como False)
- Definimos la propiedad Top del control de manera que coincida con el ángulo superior izquierdo de la celda activa
- Definimos que celda debe recibir el valor elegido (la celda activa).
La última línea del código oculta el control si la celda activa no pertenece al rango donde queremos que aparezca la lista desplegable.

El modelo funciona así



Podemos mejorar este código de manera que al alto y ancho del control se adapten dinámicamente el alto y ancho de la celda, definiendo las propiedades Height y Wide en el código del evento

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Union(Target, Range("rngDia")).Address = Range("rngDia").Address Then
        With ComboBox1
            .Visible = True
            .Top = ActiveCell.Top
            .Height = ActiveCell.Height
            .Width = ActiveCell.Width

            .LinkedCell = ActiveCell.Address
        End With
    Else
        ComboBox1.Visible = False
    End If
End Sub


De la misma manera podemos definer dinámicamente otras propiedades como el contenido de la lista (ListFillRange), el número de valores a mostrar en el cuadro (ListRows), etc.


Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

23 comments:

elmundodelennon 20 mayo, 2013 22:15  

Hola, sé que no es el lugar apropiado para preguntarte esto pero me sería de mucha ayuda.

Me podrías por favor indicar cómo haces el efecto en la imagen para que aparezca recortada?, me refiero al efecto tipo "sierra" o "dientes".

Y en segunda instancia, cómo haces para crear el gif explicativo?, es un software especial?

Muchísimas gracias por tu ayuda de antemano, siempre sigo tu blog. :D

Jorge Dunkelman 21 mayo, 2013 06:47  

Las efectos de las imágenes los produzco con FastStone Capture; las animaciones .gif con Screencast-o-matic.

Anónimo,  04 junio, 2013 10:03  

Muy bueno el artículo y todo el blog entero. Me haz salvado de varias dificultades que he tenido.
He estado viendo el ejemplo y creo que he hecho todo bien. El modo de autocompletar aparece unicamente cuando seleccionar la celda con el cursor, como se ve en el gif que mostras.
2 Preguntas:
1 Se puede hacer esto pero en vez de pasar el cursor haciendo que se mueva con enter? Es mucho más comodo.
2 Se puede generar el error por haber puesto mal un dato por medio de algún otro artificio.

Lo que he estado haciendo es usar el autocompletar del mismo excel. Escribo la lista (la cantidad máxima es fija, pero casi siempre esta algo incompleta, espacios en blanco) justo encima de la que voy a escribir. Oculto todas las filas donde esta la lista y cuando comienzo a escribir abajo en las celdas visibles, aparece el modo de autocompletar. Pero despues de que escribi algunas letras, en ocaciones todo, el nombre. En el caso que describis, aparece desde la primera letra, es mucho mejor. Supongo que se puede solucionar estos dos problemas sin mucho esfuerzo usando VBA, pero apenas si lo manejo.
Espero tu respuestas, si no tendré que conformarme con seguir haciendo lo mismo.

Jorge Dunkelman 04 junio, 2013 13:48  

Hola,

supongo que la primer consulta se refiere al hecho que el foco queda en la combobox y la forma de volver a la hoja es con el mouse. Efectivamente, para poder solucionar esta cuestión y también la validación, hay que usar un poco de código.
Estaré publicando una nota sobre el tema en breve.

Anónimo,  04 junio, 2013 20:29  

Si es precisamente a eso.
OK. Y muchas gracias por crear este blog y tomarte el tiempo de responder a cada consulta.

Jorge Dunkelman 06 junio, 2013 18:11  

Hola, después considerar las distintas posibilidades, la forma más práctica de volver el foco a la celda es apretar Esc.
En cuanto a la validación, irá una nota como prometido, más adelante.

Anónimo,  31 julio, 2013 08:03  

Hola Jorge, mi nombre es Américo y quisiera por favor me ayudaras a ver la manera de que el autocompletado sea aleatorio (o lista alternativa), es decir, no necesariamente sean las primeras letras de la lista sino que muestre las alternativas cercanas al dato ingresado... si tengo productos "espumadora piso latex", "espumadora techo cromo", "limadora piso madera", etc... al ingresar "piso" las posibles respuestas sean "espumadora piso latex" y "limadora piso madera".
Muchas gracias de antemano.

Jorge Dunkelman 02 agosto, 2013 18:53  

Hola Américo, si bien se puede hacer requeriría bastante código de Vba.
Un enfoque más práctico y eficiente, desde mi punto de vista, sería crear dos combobox dependientes. En la primera se leije la categoría (piso, techo, etc.) y de acuerdo a la elección la segunda muestra los valores dependientes. Para hacerlo tienes que crear agregar una columna a tu tabla de datos con las categorías.
Este enfoque es más correcto ya que te permite mejor control de los valores.
En el blog hay varias notas sobre listas desplegables dependientes.

Allan Rapalo 27 enero, 2014 16:43  

Hola Jorge, Actualmente estoy usando el código mejorado que publicaste mi solicitud es la siguiente, yo tengo una lista de mas de 1000 Nombres de Materiales y me gustaría que la lista desplegable pudiera auto completarse. Agradezco de antemano su valiosa Ayuda y lo felicito por su trabajo de mentor. Gracias.

Jorge Dunkelman 28 enero, 2014 11:43  

Allan, con autocompletarse, ¿te referís a que el rango de nombre sea dinámico o que al empezar a escribir el nombre del material vaya apareciendo el que más se aproxima? En este último caso tenés que definir la propiedad MatchEntry del combobox como 1-fmMatchEntryComplete. Podés fijarte en esta nota.

Anónimo,  31 enero, 2014 14:23  

Hola Jorge,

Funciona con Excel 2007? Me ocurre que el ComboBox dinámico está funcionando en el rango definido si le doy a la flecha desplegable pero el Autocompletar no funciona (sólo el propio de excel con los nombres que se van generando en la columna).
Gracias

Anónimo,  31 enero, 2014 14:35  

Hola Jorge,
Acabo de mandarte otro comentario sobre si funciona en Excel 2007 el Autocompletar. Ya sé lo que me ocurre. Cuando entro en la celda, tengo que volver a dar otro click para que empiece a funcionar el Autocompletado. Es como si primero se seleccionara la celda que está debajo (aunque aparece ya el combobox) y con el siguiente click se posiciona dentro del cuadro propiamente.
He probado a modificar los tamaños del combo, incluso usé el código mejorado pero me pasa eso. Es correcto ese funcionamiento o se puede evitar ese segundo click?
Un saludo y gracias

Jorge Dunkelman 31 enero, 2014 15:51  

Funciona en todas las versiones a partir de Excel 5. Para que el foco pase al control, de manera que el Autocompletar funciones, hay que agregar una línea al código del evento

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Union(Target, Range("rngDia")).Address = Range("rngDia").Address Then
With ComboBox1
.Visible = True
.Top = ActiveCell.Top
.Height = ActiveCell.Height
.Width = ActiveCell.Width
.LinkedCell = ActiveCell.Address
.Activate
End With
Else
ComboBox1.Visible = False
End If

Despues de poner el valor en el combobox hay que apretar Esc para que el foco vuelva a la celda.

Anónimo,  31 enero, 2014 17:15  

Hola Jorge,

Perfecto con el .Activate
Muchas gracias por la aclaración y por la rapidez.

SebaXX 05 marzo, 2014 03:00  

Estimado, tengo un libro con tres listas desplegables autocompletables hechas con combobox tal como lo explicaste, las que me funcionan correctamente. El problema me surge al querer "ocultar" el cuadro combinado, osea que aparezca al seleccionar el rango de celda. Al ocultar un cuadro combinado con el segundo código que agregaste me funciona perfectamente, sólo he modificado el rango que he puesto yo y funciono. He definido los tres rangos para que aparezcan las listas al seleccionar las celdas, cada uno en columnas distintas. El problema surge al querer ocultar los otros dos cuadros combinados, ya que agrego a continuación del macro el nuevo código con el segundo rango y me aparece "Se ha detectado un nombre ambiguo: Worksheet_SelectionChange". Seguramente hay que cambiar el código pero no he podido solucionarlo.

Ojala me puedas ayudar.

Lo que yo he escrito en la macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Union(Target, Range("rango1")).Address = Range("rango1").Address Then
With ComboBox1
.Visible = True
.Top = ActiveCell.Top
.Height = ActiveCell.Height
.Width = ActiveCell.Width
.LinkedCell = ActiveCell.Address
End With
Else
ComboBox1.Visible = False
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Union(Target, Range("rango2")).Address = Range("rango2").Address Then
With ComboBox1
.Visible = True
.Top = ActiveCell.Top
.Height = ActiveCell.Height
.Width = ActiveCell.Width
.LinkedCell = ActiveCell.Address
End With
Else
ComboBox1.Visible = False
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Union(Target, Range("rango3")).Address = Range("rango3").Address Then
With ComboBox1
.Visible = True
.Top = ActiveCell.Top
.Height = ActiveCell.Height
.Width = ActiveCell.Width
.LinkedCell = ActiveCell.Address
End With
Else
ComboBox1.Visible = False
End If
End Sub


Saludos!!!!

Jorge Dunkelman 05 marzo, 2014 07:19  

No puedes terner varias rutinas (sub) con el mismo nombre en un mismo módulo. Lo que tienes que hacer es poner todo el código en una misma rutina. Ru código tendría que quedar así:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Union(Target, Range("rango1")).Address = Range("rango1").Address Or _
Union(Target, Range("rango2")).Address = Range("rango2").Address Or _
Union(Target, Range("rango3")).Address = Range("rango3").Address Then
With ComboBox1
.Visible = True
.Top = ActiveCell.Top
.Height = ActiveCell.Height
.Width = ActiveCell.Width
.LinkedCell = ActiveCell.Address
End With
Else
ComboBox1.Visible = False
End If
End Sub

SebaXX 10 marzo, 2014 03:10  

Estimado, se te agradece la respuesta. Por si a alguien más le sirve, lo solucione de la siguiente manera:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Union(Target, Range("rango1")).Address = Range("rango1").Address Then
With ComboBox1
.Visible = True
.Top = ActiveCell.Top
.Height = ActiveCell.Height
.Width = ActiveCell.Width
.LinkedCell = ActiveCell.Address
.Activate
End With
Else
ComboBox1.Visible = False
End If
If Union(Target, Range("rango2")).Address = Range("rango2").Address Then
With ComboBox2
.Visible = True
.Top = ActiveCell.Top
.Height = ActiveCell.Height
.Width = ActiveCell.Width
.LinkedCell = ActiveCell.Address
.Activate
End With
Else
ComboBox2.Visible = False
End If
If Union(Target, Range("rango3")).Address = Range("rango3").Address Then
With ComboBox3
.Visible = True
.Top = ActiveCell.Top
.Height = ActiveCell.Height
.Width = ActiveCell.Width
.LinkedCell = ActiveCell.Address
.Activate
End With
Else
ComboBox3.Visible = False
End If
End Sub

Así, si quieren agregar más combobox. Yo llegue a los 7. Saludos!

Anónimo,  28 julio, 2014 17:23  

Buenas, tengo mi rango con 500 datos y pretendo ir agregando mas, puedo hacer dinamico esto, que en automatico el rango a mostrar se valla incrementando en automatico.

Jorge Dunkelman 29 julio, 2014 07:47  

Hay varias técnicas para hacerlo. Por ejemplo, usando al función DESREF o la función INDICE.
Una vez definido el rango din[amco se puede asignar a un nombre definido y usar este en las propiedades del combobox.

SALT 11 septiembre, 2014 17:04  

Hola, he intentado hacer funcionar el combobox con el código que está publicado en este Blog pero no logro hacerlo funcionar, ¿hay algún archivo que pueda descargar?

Saludos,

Jorge Dunkelman 12 septiembre, 2014 11:22  

No tengo disponible el archivo de esta nota, pero te invito a adquirir la guía sobre listas desplegables con Excel donde además de las explicaciones hay enlaces paa la descarga gratuita de todos los ejemplos.

Unknown 03 enero, 2015 22:24  

Hola, saludos y gracias por el post, que debo hacer paa que varias columnas de la misma hoja puedan tener esta opcion mostrada en el tutorial?

Jorge Dunkelman 04 enero, 2015 07:11  

Tienes que modificar el rango al que se refiere el nombre "rngdia". En el ejemplo de la nota el nombre se refiere al rango A2:A20; para que se extienda a las columnas B, C, y D, por ejemplo, tendría que ser A2:D20.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP