sábado, mayo 11, 2013

Lista desplegable con combobox dinámico

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.

25 comentarios:

  1. 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

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

    ResponderBorrar
  3. 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.

    ResponderBorrar
  4. 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.

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

    ResponderBorrar
  6. 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.

    ResponderBorrar
  7. 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.

    ResponderBorrar
  8. 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.

    ResponderBorrar
  9. 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.

    ResponderBorrar
  10. 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.

    ResponderBorrar
  11. 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

    ResponderBorrar
  12. 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

    ResponderBorrar
  13. 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.

    ResponderBorrar
  14. Hola Jorge,

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

    ResponderBorrar
  15. 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!!!!

    ResponderBorrar
  16. 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

    ResponderBorrar
  17. 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!

    ResponderBorrar
  18. 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.

    ResponderBorrar
  19. 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.

    ResponderBorrar
  20. 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,

    ResponderBorrar
  21. 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.

    ResponderBorrar
  22. 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?

    ResponderBorrar
  23. 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.

    ResponderBorrar
  24. Hola tengo 4 listas desplegables en las columnas I, J y necesito que se limpien en función de los cambios en las celdas de la columna I; utilizando como referencias la ayuda en otro duda similar implemente el código que escribo abajo; sin embargo cuando hago un cambio en alguna de las listas de la columna I todas las de la columna J se reinician; es posible hacer que si limpien las celdas de manera independiente sin afectar el restpo de las filas?

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("I31") Then
    Range("J31").Value = "Seleccionar"
    End If
    If Target = Range("I32") Then
    Range("J32").Value = "Seleccionar"
    End If
    If Target = Range("I33") Then
    Range("J33").Value = "Seleccionar"
    End If
    If Target = Range("I34") Then
    Range("J34").Value = "Seleccionar"
    End If
    End Sub

    ResponderBorrar
  25. Hola, en lugar de

    If Target = Range("I31") Then

    tienes que usar

    If Target.Address = Range("I31").Address Then

    y lo mismo para el resto de las sentencias.

    También podrías usar

    If Union(Target,Range(I31:I34)).Address=Range(I31:I34).Address Then
    Target.Offset(0,1)="Seleccionar"
    End if

    con lo cual tu el código sería más sucinto.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.