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.
Hola, sé que no es el lugar apropiado para preguntarte esto pero me sería de mucha ayuda.
ResponderBorrarMe 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
Las efectos de las imágenes los produzco con FastStone Capture; las animaciones .gif con Screencast-o-matic.
ResponderBorrarMuy bueno el artículo y todo el blog entero. Me haz salvado de varias dificultades que he tenido.
ResponderBorrarHe 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.
Hola,
ResponderBorrarsupongo 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.
Si es precisamente a eso.
ResponderBorrarOK. Y muchas gracias por crear este blog y tomarte el tiempo de responder a cada consulta.
Hola, después considerar las distintas posibilidades, la forma más práctica de volver el foco a la celda es apretar Esc.
ResponderBorrarEn cuanto a la validación, irá una nota como prometido, más adelante.
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".
ResponderBorrarMuchas gracias de antemano.
Hola Américo, si bien se puede hacer requeriría bastante código de Vba.
ResponderBorrarUn 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.
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.
ResponderBorrarAllan, 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.
ResponderBorrarHola Jorge,
ResponderBorrarFunciona 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
Hola Jorge,
ResponderBorrarAcabo 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
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
ResponderBorrarPrivate 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.
Hola Jorge,
ResponderBorrarPerfecto con el .Activate
Muchas gracias por la aclaración y por la rapidez.
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.
ResponderBorrarOjala 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!!!!
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í:
ResponderBorrarPrivate 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
Estimado, se te agradece la respuesta. Por si a alguien más le sirve, lo solucione de la siguiente manera:
ResponderBorrarPrivate 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!
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.
ResponderBorrarHay varias técnicas para hacerlo. Por ejemplo, usando al función DESREF o la función INDICE.
ResponderBorrarUna vez definido el rango din[amco se puede asignar a un nombre definido y usar este en las propiedades del combobox.
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?
ResponderBorrarSaludos,
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.
ResponderBorrarHola, 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?
ResponderBorrarTienes 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.
ResponderBorrarHola 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?
ResponderBorrarPrivate 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
Hola, en lugar de
ResponderBorrarIf 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.