Hojas de cálculo en Excel - página principal

Llenar dinámicamente un combobox

Para alimentar con datos, un combobox, tenemos varias opciones:

a) Llenar el combo con los datos de determinado rango, y cuyas filas son siempre las mismas (por ejemplo, con los datos del rango E1:E10, con los datos de B12:B15, etc.). En este caso, sabremos que nuestro rango siempre tiene 10 filas, o 4 filas, o las que sean, pero sabremos que siempre es una cantidad fija en invariable.

b) Llenar el combo con los datos de un rango que puede crecer o decrecer, es decir, que en un momento dado el combo puede tener 3 elementos, y al cabo de cinco segundos puede tener 20 elementos. En este caso, estamos hablando de que nuestro combobox se alimenta de un rango dinámico.

Para insertar un combobox directamente en nuestra hoja de cálculo, sin tener que insertarlo en un UserForm, haremos lo siguiente: Desde excel, seleccionaremos Ver, a continuación Barras de herramientas, y seguidamente Cuadro de controles, tal y como aparece en la figura de la izquierda.

Seleccionaremos el control llamado Cuadro combinado (acercando el ratón a cada control, nos aparecerá el nombre de cada uno de ellos). Una vez seleccionado, dibujaremos el combobox en la hoja de cálculo, dándole el tamaño y posición que deseemos. Arriba a la izquierda, en la barra de fórmulas, nos aparecerá su nombre, …por defecto ComboBoxY, donde Y es un número correlativo, de tal forma que si es nuestro primer combobox, se llamará ComboBox1, si es el 2º, se llamará ComboBox2, y así sucesivamente.

Para el primer caso, es decir, si queremos llenar el combobox con los datos de un rango fijo, entonces lo tenemos bien fácil. Imaginemos que tenemos un rango con datos que va de I8 a I10, y queremos que esos valores almacenados en esas celdas (en las 3 celdas: I8, I9, e I10), sean los que aparezcan en nuestro combobox. Supongamos que estamos trabajando con un combobox que se llama ComboBox3. En ese caso, el código que podríamos utilizar sería este (estoy suponiendo que el combobox no está insertado dentro de un UserForm, porque en ese caso, el código sería algo distinto, ya que utilizaríamos RowSource, en lugar de usar ListFillRange), y desde la pantalla para VBA, lo pegaríamos en la hoja1, que es donde tenemos nuestro combobox, y no en un módulo:


Private Sub ComboBox3_GotFocus()
'Este código se ejecutará cada vez que
'nos situemos sobre el combobox

ComboBox3.ListFillRange = "I8:I10"
ComboBox.RowSource
End Sub

Si por el contrario, el rango que alimenta el combobox es dinámico, deberemos añadir los elementos al combobox, mediante el método AddItem.

Supongamos por ejemplo que tenemos un libro con 3 hojas y queremos que en nuestro combobox nos aparezcan los nombres de esas tres hojas. En ese caso, el código del combobox (en este caso, nuestro ComboBox1) sería el siguiente (estamos haciendo un combobox directamente sobre nuestra hoja de cálculo, y no insertado dentro de un formulario), y que tendríamos que pegar desde VBA, en la hoja1, que es donde tenemos nuestro combobox, y no en un módulo:

Private Sub ComboBox1_GotFocus()
'Esto se producirá cuando nos
'situemos sobre el combobox1
'********* Llenamos el combobox1 *********
'antes quitaremos todo lo que haya en el combobox1

ComboBox1.Clear
'Vamos a llenar dinámicamente el combobox
'con los nombres de las hojas

For i = 1 To Sheets.Count
'Añadimos los nombres de las hojas al combobox
ComboBox1.AddItem Sheets(i).Name
Next
End Sub

Con ese código, recorremos los nombres de las hojas, y se cargarán en el combobox, cada vez que éste reciba el foco (cada vez que nos situemos sobre él).

Si deseamos que se ejecute cierta acción, cada vez que elijamos un elemento de la lista de ese ComboBox2, añadiremos el código que proceda, también dentro de la hoja1. En nuestro caso, cada elemento corresponde a una hoja de cálculo del libro, y eso será lo que nos aparezca en el desplegable del combobox. Nosotros mostraremos un mensaje, aunque podríamos haber hecho cualquier cosa: ir a la hoja en cuestión, eliminar la hoja, copiarla, etc:

Private Sub ComboBox1_Click()
'Mostramos un mensaje
MsgBox ("Has hecho clic sobre: " & ComboBox1.List(ComboBox1.ListIndex))
End Sub

Si lo que deseamos es llenar dinámicamente un combobox a través de los datos que hay en un rango de celdas continuo, entonces haremos lo siguiente (suponemos que nuestro combobox se llama ComboBox2, y que está insertado directamente en la hoja de cálculo, y no en un formulario), pegándolo en la hoja1 que es donde tenemos nuestro combobox:

Private Sub combobox2_gotfocus()
'Ocultamos el procedimiento
Application.ScreenUpdating = False
'fichamos la celda donde estamos, para volver a ella
celda = ActiveCell.Address
'Esto se producirá cuando nos
'situemos sobre el combobox2
'********* Llenamos el combobox2 *********
'antes quitaremos todo lo que haya en el combobox2

ComboBox2.Clear
'Vamos a llenar dinámicamente el combobox
'con los datos de la celda F8 en adelante
'(siempre que los datos sean continuos)

Range("F8").Select
Do While Not IsEmpty(ActiveCell)
'Añadimos los nombres de las hojas al combobox
ComboBox2.AddItem ActiveCell
'bajamos una fila
ActiveCell.Offset(1, 0).Select
Loop
'volvemos donde estábamos
Range(celda).Select
'Mostramos el procedimiento
Application.ScreenUpdating = True
End Sub

Si deseamos que se ejecute cierta acción, cada vez que elijamos un elemento de la lista de ese ComboBox3, añadiremos el código que proceda, también dentro de la hoja1. En nuestro caso, cada elemento corresponde a los datos que hay desde F8 en adelante (rango continuo de datos), y eso será lo que nos aparezca en el desplegable del combobox. Nosotros mostraremos un mensaje, aunque podríamos haber hecho cualquier otra cosa:

Private Sub ComboBox2_Click()
'Mostramos un mensaje
MsgBox ("Has hecho clic sobre: " & ComboBox1.List(ComboBox2.ListIndex))
End Sub


A modo de resumen, hoy hemos visto varias cosas nuevas:

a) Cómo crear un combobox, sin necesidad de insertarlo dentro de un UserForm.
b) Cómo llenar un combobox de forma dinámica, sin tener que tocar el código cada vez que cambien las dimensiones del rango de datos que lo alimentan.

Desde aquí podéis descargar el fichero de excel, con el ejemplo que os presento en este artículo.



39 comentarios:

jose dijo...

Hola! Estoy haciendo un ejemplo parecido a este. Pero en el combobox en vez de elejir una hoja del libro,me saldria una hoja de calculo. Todas las hojas de calculo estan metidas en el mismo directorio. Ya que de esta hoja necesito guardar una celda en la hoja de calculo que llama a la macro.

Javier Marco dijo...

Para hacer eso, necesitarás saber como funciona el objeto filesystemobject. En este mismo blog hay un artículo donde explico como crear carpetas desde excel, usando el objeto filesystemobject (fso).

Usando el objeto fso, puedes recorrer un directorio, pasando a una matriz o a una variable, las hojas de cálculo que encuentre en el directorio de que se trate, y luego puedes volcarlas como items en el combobox.

Googlea sobre el objeto filesystemobject.

Un saludo.

Anónimo dijo...

Jose yo quier hacer algo mas o menos como tu. Pero en vez de abrir la pagina quiero que solo me la lea. E Importar tres celdas a la hoja de calculo desde donde llamo la macro. Cuando lo tengas a ver si puedes colgar una parte del codigo. Gracias. Esta pagina esta muy bien.

Javier Marco dijo...

En el artículo sobre como listar los ficheros de un directorio, tenéis una pista valiosísima.

Entre este artículo, y el del enlace que os acabo de dejar, lo tenéis facilito, pues ahí está todo lo imprescindible para desarrollar el macro.

Un saludo.

Anónimo dijo...

y que hago si lo que quiero es llenar dinamicamente un combobox pero de una UserForm?....necesito llenar 2, una con el nombre de las hojas y otra con una listado cosas

Javier Marco dijo...

Pues hay varias entradas relativas al uso de los combobox. Échales un vistazo, aunque este artículo que has leído es el que más te va a servir. Las diferencias no son muchas (mirando el código de los otros artículos, seguro que lo sacas).

Anónimo dijo...

Hola javier, tu pagina es lo maximo y quiero pedir tu ayuda...tengo un combobox dinamico (en Userform) y este es el comando para llenarlo:

Range("C2").Select
Do While Not IsEmpty(ActiveCell)
ComboBox2.AddItem ActiveCell

el problema es que llena todos los nombres que aparecen desde C2 hasta que la celda esta vacia, pero hay muchos nombres y hay algunos que se repiten como 15 veces, entonces yo necesito que en la lista del combobox aparezcan sólo 1 vez los nombres que se repiten...por que no necesito saber cuantas veces esta ese nombre, sino cuales son los nombres de la lista..............me explique bien?, se puede hacer eso?

te agradezco de antemano

Javier Marco dijo...

Aquí tienes un ejemplo de lo que buscas, para un combobox con valores únicos (que no se repitan):

http://www.megaupload.com/es/?d=HIJ853KD

Saludos.

Anónimo dijo...

más confirmo que tu página es lo máximo y tu un fenómeno!!!

gracias

saludos

Anónimo dijo...

HOLA! COMO LO HAGO SI DESPUES DE SELECCIONAR UN ELEMENTO DE LA LISTA DE LA COMBOBOX VAYA A LA HOJA O CELDA DONDE ESTA EL ELEMENTO EN CUESTION?

Javier Marco dijo...

Tenéis que trabajar un poco, y usar el sentido común. Leyendo un par de artículos del blog, se pueden sacar todas estas cosillas, muy fácilmente.

Aquí tienes el ejemplo:

http://www.megaupload.com/es/?d=C06S5SXY

Saludos.

Anónimo dijo...

Hola Javier, he aprendido nuevas cosas con cada uno de los procedimientos que mencionas en tu blog.

Quisiera ver si me pudieras ayudar en lo siguiente.

Estoy creando un archivo para declaración de produccion, en el cual tengo varios commandbutton.

Lo que necesito es que una vez que seleccionan un commandbutton los demas se bloquean, ademas de cambiar los colores de los commandbutton no seleccionados, para diferenciarlos del seleccionado.

Esto ya lo he hecho, pero me resulta muy problematico el estar modificando los parametros de color y deshabilitar los commandbutton por cada uno de los botones.

Y lo que quisiera es solamente con poner el nombre CommandButton(X).BackColor = RGB(146, 156, 154), donde X es el numero de boton se cambie hasta completar un ciclo de 20 ó más botones, como lo podria hacer para no generarlo como lo estoy haciendo:

Te mando como lo estoy haciendo:


Sub color()
' Limpiar todos los botones de los colores
Defecto.CommandButton4.BackColor = RGB(146, 156, 154)
Defecto.CommandButton5.BackColor = RGB(146, 156, 154)
Defecto.CommandButton6.BackColor = RGB(146, 156, 154)
Defecto.CommandButton7.BackColor = RGB(146, 156, 154)
Defecto.CommandButton8.BackColor = RGB(146, 156, 154)
Defecto.CommandButton9.BackColor = RGB(146, 156, 154)
Defecto.CommandButton10.BackColor = RGB(146, 156, 154)
Defecto.CommandButton11.BackColor = RGB(146, 156, 154)
Defecto.CommandButton12.BackColor = RGB(146, 156, 154)
Defecto.CommandButton13.BackColor = RGB(146, 156, 154)
Defecto.CommandButton14.BackColor = RGB(146, 156, 154)
Defecto.CommandButton15.BackColor = RGB(146, 156, 154)
Defecto.CommandButton16.BackColor = RGB(146, 156, 154)
Defecto.CommandButton17.BackColor = RGB(146, 156, 154)
Defecto.CommandButton18.BackColor = RGB(146, 156, 154)
Defecto.CommandButton19.BackColor = RGB(146, 156, 154)
Defecto.CommandButton20.BackColor = RGB(146, 156, 154)
Defecto.CommandButton21.BackColor = RGB(146, 156, 154)
Defecto.CommandButton22.BackColor = RGB(146, 156, 154)
Defecto.CommandButton23.BackColor = RGB(146, 156, 154)
Defecto.CommandButton24.BackColor = RGB(146, 156, 154)
Defecto.CommandButton25.BackColor = RGB(146, 156, 154)
Defecto.CommandButton26.BackColor = RGB(146, 156, 154)
Defecto.CommandButton27.BackColor = RGB(146, 156, 154)
Defecto.CommandButton28.BackColor = RGB(146, 156, 154)
Defecto.CommandButton29.BackColor = RGB(146, 156, 154)
Defecto.CommandButton30.BackColor = RGB(146, 156, 154)
Defecto.CommandButton31.BackColor = RGB(146, 156, 154)
Defecto.CommandButton32.BackColor = RGB(146, 156, 154)
Defecto.CommandButton33.BackColor = RGB(146, 156, 154)
End Sub
Sub desactivar()
' Bloquear todos los botones para que no se pueda seleccionar otra pieza
Defecto.CommandButton4.Enabled = False
Defecto.CommandButton5.Enabled = False
Defecto.CommandButton6.Enabled = False
Defecto.CommandButton7.Enabled = False
Defecto.CommandButton8.Enabled = False
Defecto.CommandButton9.Enabled = False
Defecto.CommandButton10.Enabled = False
Defecto.CommandButton11.Enabled = False
Defecto.CommandButton12.Enabled = False
Defecto.CommandButton13.Enabled = False
Defecto.CommandButton14.Enabled = False
Defecto.CommandButton15.Enabled = False
Defecto.CommandButton16.Enabled = False
Defecto.CommandButton17.Enabled = False
Defecto.CommandButton18.Enabled = False
Defecto.CommandButton19.Enabled = False
Defecto.CommandButton20.Enabled = False
Defecto.CommandButton21.Enabled = False
Defecto.CommandButton22.Enabled = False
Defecto.CommandButton23.Enabled = False
Defecto.CommandButton24.Enabled = False
Defecto.CommandButton25.Enabled = False
Defecto.CommandButton26.Enabled = False
Defecto.CommandButton27.Enabled = False
Defecto.CommandButton28.Enabled = False
Defecto.CommandButton29.Enabled = False
Defecto.CommandButton30.Enabled = False
Defecto.CommandButton31.Enabled = False
Defecto.CommandButton32.Enabled = False
Defecto.CommandButton33.Enabled = False
Defecto.CommandButton36.Enabled = True
End Sub

Frank dijo...

Hola Javier, necesito que me ayudes tengo un userform donde tengo un combobox y necesito llenarlo de datos que estan en otra pagina.
te explico mas a detalle, el userform se cargara un libro llamado cuadre de corte mediante un checkbox, ahora los datos que necesito que muestre el combobox estan en otro libro de excel llamando Retazos supervisores vs consumos, he estado viendo tus foros pero no he encontrado la manera de hacerlo funcionar, me podrias ayudar...

Frank dijo...

ya puede solucionar el problema que tenia aquí les pongo el codigo por si alguien tiene algún problema con algo parecido...

Private Sub UserForm_Initialize()
Windows("Retazos Consumos Vs Supervisores.xls").Activate
With ActiveWorkbook.Worksheets(1).Range("B5").Select
Do While ActiveCell <> ""
UserForm2.ComboBox1.AddItem ActiveCell
ActiveCell.Offset(1, 0).Select
Loop
End With
Windows("Cuadre de Cortes.xls").Activate
End Sub

De Todas Maneras Gracias...!

Gustavo dijo...

Saludos Compañero

Estoy tratando de copiar tu macro, se ejecuta la accion y pero no se llena el ComboBox.

Ademas baje el archivo con el codigo y pasa lo mismo.

que podre esta haciendo mal

saludos y felicidades por el foro

Javier Marco dijo...

Prueba este otro ejemplo, para llenar combobox (es para hacer combobox dependientes, pero te servirá igualmente): combobox dependientes.

gogo dijo...

Hola, necesito hacer un combobox dinámico en excel que se llene con los datos presentes en una tabla en access que tengo en el mismo directorio. ¿Como puedo hacerlo?

Javier Marco dijo...

Pues en este artículo tienes información, para recuperar la información de una base de datos, y traerla directamente a Excel: leer una base de datos Access desde Excel.

Para llenar dinámicamente u combobox, tienes muchos ejemplos en el blog. Este te servirá, porque es muy sencillo: Ejecutar macro al seleccionar un elemento del combobox. Aunque creas que no es lo que buscas, ahí tienes toda la info para llenar un combobox, explicado con varios ejemplos.

Un saludo.

Anónimo dijo...

Hola
Necesito saber como hago para tomar el nombre de los elemntos del combobox y asi visualiarlo en un listbox

Javier Marco dijo...

En este artículo tienes muchos ejemplos: sacándoles provecho a los combobox

Anónimo dijo...

Hola, coincido con los comentarios anteriores de lo buena y provechosa que está la información contenida en la pagina. Muchas gracias.
A parte del comentario quisiera hacerte una consulta, estoy tratando de utilizar un combobox para llamar una lista de productos que se encuentra en otra hoja dentro del mismo libro, sería una lista dinamica por si se quiere agregar mas productos a ésta, es algo similar al ejemplo de llamar los nombres de las hojas con la diferencia de querer llamar la información (lista de productos) contenida en las celdas de una hoja específica.
De antemano muchas gracias por la colaboración.
Saludos.

Frank dijo...

Hola, necesito ayuda con lo siguiente:

Tengo una hoja de excel en la que tengo 3 columnas Categoria, Marca, Modelo:
Categoria Marca Modelo
Carros Toyota Tundra
Motos Yamaha 250 CC
Carros Nissan Sentra
Motos Honda 150 CC
Televisores Sony LCD
Televisores Samsung Slim

lo que necesito es que al seleccionar en el combobox1 la categoria carros en el combobox2 solo se llenen los datos de las marcas de carro y asi sucesivamente

Gracias.

Javier Marco dijo...

Mírate este artículo: Combobox dependientes

Saludos.

The Other One dijo...

Hola, Quisiera hacer un combobox en word que muestre los datos de una lista de excel y al ejecutar un comando (no se cual) se llene un formulario en word, se puede hacer eso?

Pedro dijo...

Hola Javier,
Te felicito por tu excelente blog, que ya me ha ayudado en varias ocasiones.
Mi duda:
Tengo una combobox en una hoja (no es un userform) con todos los días desde 01-01-2010, hasta 31-12-2025. Es una lista interminable...
Lo que necesito es que por defecto me aparezca siempre la fecha del día actual.
Si la fecha que necesito es algunos días antes o después, utilizo la combo. Nunca son muchos, por eso me vale este sistema.
Se puede hacer?
Un saludo

Jks jaramillo dijo...

Hola felicidades por el blog... muy bueno... tengo una pregunta que espero me puedas ayudar... por ejemplo tengo 5 hojas en un libro, y tengo un formulario que enlista esas 5 hojas mediante un combobox, al seleccionar una de esas 5 hojas y hacer el proceso, quiero que al cargar nuevamente el formulario me aparezca la ultima seleccion por defecto, me podrias ayudar en eso... gracias....

Javier Marco dijo...

Jks, supongo que se podría solucionar con una variable global que te almacene el último dato seleccionado.

Este es un ejemplo que te pide el nombre en un macro. Almacena ese dato en una variable global, y luego puedes usarla en el otro macro más tarde, y recordará el nombre que has escrito:

Public mi_nombre As Variant

Sub nombre()
mi_nombre = InputBox("Escribe tu nombre")
End Sub

Sub escribimos_el_nombre()
Range("A1") = mi_nombre
End Sub

Saludos.

Pipe dijo...

Hola Javier! Necesito saber una macro para que al colocar un combobox en un formulario, el usuario no pueda ingresar datos desde el teclado sino que sólo pueda elegir de las opciones que aparecen en el combobox.

Mil gracias.

joel caballero dijo...

javier, creeds que sea posible realizar un macro para filtrar un periodo de fechas en cuatro tablas dinamicas al mismo tiempo, es decir colocar el periodo de fechas en una sola casilla y me filtre 4 tabals simultaneamente. por cierto el dominio y la genialidad que demuestras en esta pagina es realmente excepcional, saludos

Anónimo dijo...

Muchas Gracias Javier Marco por tus comentarios, es de agradecer. Me estan ayudando un monton.

Anónimo dijo...

necesito hacer k apartir de a medida k lleno un formulario dichos datos sean almacenados en otra hoja del mismo lubro, y que me permita ingresar varios usuarios. Son datos personales entonces cada uno debe ir en un renglon diferente. Como lo logro?

Javier Marco dijo...

Mírate este artículo: Introducir datos utilizando un formulario.

Saludos.

Danilo Sanchez dijo...

hola javier mi duda es la siguiente como hago para que el combobox cargue los datos de otra hoja en el mismo excel trate con esto

Sheets("B.D. PROVEEDORES").Select
Range("A11").Select

pero no funciono me podrias orientar porfavor

Javier Marco dijo...

Si vas a implementar el Combobox en un formulario, mírate este artículo donde hay 3 ejemplos: Combobox: sacándoles provecho. Ahí te funcionará.

DANILO SANCHEZ dijo...

EL COMBOBOX ESTA IMPLEMENTADO DIRECTAMENTE EN LA HOJA NO EN UN FORMULARIO
ESPERO QUE ME SIRVA EL CODIGO DE TODAS MANERAS

Anónimo dijo...

Hola Javier es algo que estaba buscando, solo que como puedo llenar al combobox1 los nombres de las hojas de otro libro ? o sea con el codigo sheets.count carga al combobox1 los nombres de la hoja del libro actual, y como seria para cargar los nombres del libro2 ?
Gracias por tus valiosos ejemplos

Jesus

Javier Marco dijo...

Tendrás que conectarte al otro libro, como se explica en este artículo leer otro fichero Excel, y aplicar lo explicado en este mismo artículo que acabas de leer.

Saludos.

Alanya dijo...

Hola Javier, te felicito! es un excelente Blog en el cual haces posible enseñar a los que estamos iniciando en el mundo del vba y asi aprendamos en conjunto.Felicidades!

Quisiera si no fuera la molestia hacerte 1 comentario y una pregunta, el comentario es que baje tu ejemplo y funciona bien excepto el combo 2 ya que no despliega trate de verlo y no le encuentro manera, a ver si lo pudieras revisar.
Lo otro es que he visto muchos ejemplos de combobox dinamicos y todos funcionan pero el detalle es que solo funcionan siempre y cuando los datos a extraer esten en la misma hoja o si no fuera asi llamar a la hoja donde estan dichos datos primeramente Hoja(x).select, pregunta: ¿hay manera de extraer los datos de otra hoja sin llamar a la hoja? sea valido para combobox insertado en 1 hoja como en un form.
Disculpas si me extendi, espero que me puedas dar una mano con ello, un abrazo desde Perú.

Javier Marco dijo...

Pásate por este artículo, que seguro que no te da problemas con el desplegable: sacándoles provecho a los combobox.

Respecto a tu cuestión, siempre vas a tener que informar de la hoja de la cual quieres obtener los datos para el combobox. Si no lo hicieras, por defecto siempre te tomará la hoja activa en el momento en el que ejecutas el macro, es decir, aquell hoja que tienes en primer plano.

Saludos.