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

Combobox dependientes

Hace poco me preguntaba un usuario, en un comentario del blog, como aplicar las listas de validación dependientes, pero en lugar de con listas de validación, con combobox. En una respuesta que le di, le colgué un ejemplo, que es el que utilizaré en este artículo, explicando su funcionamiento con detalle.

Lo primero que tendremos que preparar, son los datos de origen, y que se cargarán en los diferentes combobox. En este caso, usaremos dos combobox, aunque su funcionamiento es el mismo, si anidamos más de dos combobx dependientes.

Para explicar el funcionamiento, supondremos que tenemos una lista de países, con sus respectivas ciudades. En este ejemplo hay tres países, y tres ciudades por cada país, aunque si lo deseamos podemos añadir más países, y también más ciudades (no tienen porqué tener todos los países el mismo número de ciudades, ya que si lo deseamos, podemos poner en un país 5, 6, o 20 ciudades, y en otro 2, 3, o las que deseemos). Vamos a trabajar con el siguiente ejemplo:


A continuación, crearemos un UserForm, para lo cual accederemos al modo VBA (Alt+F11), y desde el menú Insertar, seleccionaremos la opción UserForm. Se nos presentará un UserForm en blanco, y lo que tendremos que añadir son dos etiquetas (label), y dos combobox (cuadros combinados). Si no nos apareciera el Cuadro de herramientas, para poder añadir esos controles, desde el menú Ver, seleccionaremos Cuadro de herramientas.

Tendremos que montar un UserForm como este que muestra la imagen:


A continuación, añadiremos este código a UserForm, haciendo doble clic en cualquier control del mismo (o en el propio UserForm), que nos servirá para que al cargarse el UserForm, se añadan al combobox1 (al combobox donde se cargan los nombres de los países) los datos que tenemos en la hoja1:


Private Sub UserForm_Initialize()
'Al inciar el formulario,
'seleccionamos la hoja con los datos

Hoja1.Select
'Seleccionamos la celda A1
Range("A1").Select
'Hasta que no encuentre una columna vacía
'que llene todo con datos

Do While ActiveCell <> Empty
ComboBox1.AddItem ActiveCell.Value
'nos desplazamos una columna a la derecha
ActiveCell.Offset(0, 1).Select
Loop
End Sub

Añadiremos también este otro código, que hará que al cambiar el combobox1 (el de los países), se carguen las ciudades en el combobox2, correspondientes al país elegido:

Private Sub ComboBox1_Change()
'Llenamos el combobox2 al recibir el foco,
'pero antes lo limpiamos

ComboBox2.Clear
'seleccionamos la hoja1
Hoja1.Select
'miramos lo que hay seleccionado en el combobox1
'Listindex + 1 nos devuelve la columna del país,
'porque el listindex empieza siempre por cero

columna = ComboBox1.ListIndex + 1
'seleccionamos la celda que proceda,
'a partir de la segunda fila

Cells(2, columna).Select
'vamos a llenar dinámicamente el combobox
'dependiendo del país elegido

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
End Sub

Insertaremos también un módulo VBA, y añadiremos este código para que al cliquear en un botón que añadiremos a nuestra hoja de cálculo, se cargue el formulario (el UserForm):

Sub UserForm()
'llamamos al userform
UserForm1.Show
End Sub

Una vez hayamos añadido un botón, para que al cliquear sobre él, se cargue el UserForm, obtendremos algo parecido a lo que aparece en la siguiente imagen (podéis cliquear sobre ella, para verla ampliada), donde hemos seleccionado un país (en este caso España), y donde podemos elegir una de las tres ciudades correspondientes a este país:


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



26 comentarios:

Chinso dijo...

Hola Javier,

He estado viendo tu blog y he pensado que tal vez tu podrías ayudarme con una duda a la que llevo tiempo dando vueltas y no consigo dar con la solución concreta.
El tema es que tengo una gran cantidad de archivos excel todos con un formato establecido y necesitaria es extaer valores de determinadas celdas salteadas de cada .xls y colocarlos en un txt, de forma que cada en cada linea tenga los valores que contienen esas celdas separados por ; por ejemplo.
¿Se te ocurre algo sencillo?


Muchas gracias,
Saludos.

El pilt®afilla - www.3piesalgato.com dijo...

Mírate este artículo, para saber como generar un fichero de texto plano a partir de una hoja de cálculo. Aunque lo que quieres hacer se puede simplificar, sin necesidad de macros.

Lo ideal es que tengas una hoja de cálculo (por ejemplo la hoja1 de un libro nuevo), y que "chupe" los datos que necesitas (solo los que necesitas) del resto de libros de excel. Esto seguro que lo sabes hacer, es muy sencillo, pues es básico de excel (vincular celdas, hojas, y libros).

Una vez tengas en la hoja1 los datos tal y como te gustaría exportarlos a un fichero de texto plano, vas a Archivo --> Guardar como --> y seleccionas el formato CSV (MS-DOS) (*.csv) y guardas el fichero. Ojo no confundas ese formato con otro que hay parecido y que se llama Texto (MS-DOS) (*.txt)

Una vez hecho eso, te encontrarás que tienes un fichero con extensión csv, pero que cumple con todas las características que necesitas. Tan solo tendrás que cambiarle la extensión .csv por .txt (sí, así, tal y como te lo digo), y listo, problema resuelto.

Saludos.

Anónimo dijo...

Hola,necesito hacer combobox dependientes, pero como lo hago sin usar UserForm?....lo que pasa es que necesito hacer 2 combobox dependientes y ademas dinamicas, y en tus ejemplos sale como hacer dependientes con UserForm y dinamicas sin UserForm, entonces no se como convinarlas, para hacer lo que necesito.......me ayudas plis?..te lo agradeceria mucho!!

pd: me encanti tu pagina

andrea

El pilt®afilla - www.3piesalgato.com dijo...

¿Has probado a adaptar el código de los combobox dependientes con userform, al ejemplo de combobox sin userform?. Pruébalo.

Igual lo que necesitas no son combobox dependientes, sino validación de listas dependientes.

Saludos.

Anónimo dijo...

validacion de datos no me sirve por que necesito que sea dinamico, ademas que en tu ejemplo tienes españa y francia en las columnas A y B por lo que es facil de seleccionar pero en mi caso españa y francia son las hojas 1 y 2, entonces no puedo........En resumen, combobox dependientes con UserForm es lo que me sirve, pero no se como hacerle ademas (a esas mismas combobox) un llenado dinamico con Userform.....eso es lo que me piden.

Se te ocurre como??...llevo hartos dias tratando

andrea

El pilt®afilla - www.3piesalgato.com dijo...

Hola Andrea.

Básicamente con lo que pone en este mismo artículo, se puede conseguir. Solo hay que añadir una línea para determinar que hoja de cálculo hemos seleccionado.

Descárgate el ejemplo desde aquí:

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

Saludos.

Anónimo dijo...

muchas muchas pero muchas gracias!!!!!!!!!!............en verdad me sirvio demasiado el ejemplo que pusiste para descargar, eso era lo que necesitaba hacer. Asi que me alegraste el dia, ya que ya estaba un poco bajoneada de que no pudiera hacerlo, ahora no me van a despedir del trabajo jajajja y todo gracias a ti, te pasaste, eres muy seco para macros (yo estoy recien aprendiendo, de echo 3 dias)....y encuentro ademas increible que tengas tan buena voluntad para ayudar.

saludos
chao
andrea

El pilt®afilla - www.3piesalgato.com dijo...

Gracias por tu comentario Andrea. Como ves, Papa Noel pasó por tu casa, aunque fuera con retraso :-)

No, sinceramente te dejé el ejemplo de lo que buscabas, porque me pareció que podría ser de utilidad para los lectores del blog.

Mario Dominguez dijo...

Saludos Javier:
Primero filicitarte por tu blog y tu espiritu de ayuda y quiero hacerte una pregunta, yo llevo los gastos y produccion de una pequeña compañia de construccion, yo elaboro los programas en excel para agilizar mi trabajo, no compro oos programa grandes porque a veces son mas complicado que lo que necesito, ahora quiero hacer un programa donde tengo tanto las labores como los materiales, tengo varias hojas con el listado de las labores segun la construccion y en otras los materiales cada uno con su precio, solo les falta ponerle la cantidad, y me calcula el gasto y costo diario, pero quiero que se me resuma en una sola pagina solo los trabajos y materiales del dia, es decir que al darle la cantida este automaticamente se coloque con toda la descripcion de la celda en otra hoja, como podri hacer esto, ya he probado con formulas y funciones y nada me sale, podrias ayudarme.
Gracias de antemano y mucho exito

Anónimo dijo...

Felicitarte por tú blog, muy bueno.Estoy tratando de hacer un listado de mis hojas de un libro excel (2007), pero que tenga una columna adicional que la pueda editar para ingresar una reseña o sumilla del contenido o de que es lo que hay en esa página. De igual manera que al seleccionar la hoja automáticamente la aperture. No sé si será una macro un combobox o una validación o userforms, pero la cuestiós es que tiene que ser dinámica y que se actualice sola en el caso de quitar o aumentar una hoja, pero que permita en cada caso modificar la reseña o nombre de la página.
Gracias

Anónimo dijo...

HOLA JAVIER, MUY BUIENA TU PAGINA EXELENTE ME FUNCIONO TODO, ME ESTOY HACIENDO UNA PAGINA CONTROL DE GASTOS PERSONALES, NECESITABA EL COMOBOX DEPENDITES, SÓLO TENGO UN PROBLEMA QUE NO ME APARECE EL BOTON PARA LLAMAR AL USEFORM

ME PODRIAS AYUDAR

GRACIAS
SALUDOS

El pilt®afilla - www.3piesalgato.com dijo...

Mírate este artículo donde explico como asociar un macro a un botón. El macro lo que haría, sería llamar al userform, tal y como se indica en el último código que aparece en el artículo donde publico este comentario (este mismo).

Un saludo.

Anónimo dijo...

Hola Javier, en esta misma encontraras que el día 16 te hice una consulta como "Anónimo", tal vez no he sido lo suficientemente claro, lo que trato de hacer es un "explorer" para excel, que primero me ubique todos los archivos excel en una carpeta determinada que yo elija, luego con un click abrir ese archivo, y que me muestre o lea y las indique las páginas que tiene. Como te decía debe haber una alternativa en ambos casos para poder editar un campo que permita agragar algunas referencias para saber de que se trata el archibro, libro excel, y luego en sus páginas. Te agradeceré tengas por bien ayudarme si está a tú alcance dándome a conocer los pasos que debo seguir
Saludos desde Perú
Jorge

Anónimo dijo...

Hola Javier, estoy haciendo un control de gastos con Combobox dependientes,Items (servicios, gastos Corrientes, Extras y No presupuestado) y cada item tiene sub items, además de un cuadro de texto para agregar valor me ha sevido mucho tu pagima, pero tengo una duda como hago para que al hacer click sobre el ok del combobox en la hoja siguiente se gabrabe el gasto separado por columna, ya logre que al pinchar se pusiera la fecha del gasto pero no me resulta agregarlos en columnas por por item

Saludos

Excelente blog

Anónimo dijo...

Hola, ya realize el ejemplo del combobox combinado si selecciono españa me arroja las ciudades, pero quisiera que me ayudaran ya que quier segun el ejmeplo colocar ademas de tener las ciudades algunos sitios de interes, creo que serian tres combobox uno dependiente del otro, ademas el tercer combobox me debe extraer un consecutivo de cierto rango de celdas dentro de una columna

Gracias

Anónimo dijo...

Hola. Javier muy buena tu pagina.

Quiero hacer el siguiente macro, quiero colocar dos combobox uno dependiente del otro, en donde tengo en la Columna A2(Nombre del Empleado), y en celda Combinada (B1:F1) tengo escrito ENERO y en la Celdas de abajo tengo: H. Extras(B2), Vacaciones(C2), Regalia(D2), Seg. Social(E3), ISR(F2). entonces la parte que esta desde (B1:F2) se repite en las demas columnas para cada mes. Ahora bien lo que quiero es lo siguiente que por medio a un combobox pueda escoger el empleado a trabajar y que en el segundo combobox escoja el mes que voy a trabajar, o sea, si estoy en ENERO y tengo al empleado 23 que esta en (A25) y le quiero dar entrada a las H. Extras y a las Vacaciones darle entrada en ENERO en sus respectivas Columnas(B2 y C2) y que si quiero trabajar en AGOSTO que se vaya a la columna de agosto. Tengo un macro que me hace la funcion de buscar la celda en que esta el empleado pero no se como hacerle referencia si pertenece al mes de AGOSTO que esta en la columna (AC25:AG25). Y necesito un buscador que segun yo digite las primeras letras me vaya descartando los que no tengan coincidencias (Ej. tengo a Jose Ramirez, Maria Sanchez, Josefina Baez, Mariano Rivera que si escribo JO que solo me muestre Jose Ramirez y a Josefina Baez que son las coincidencias en un Listbox).

Espero que me puedas ayudar.

Gracias Anticipadas

P.D. Me pueden enviar la repuesta a mi E-mail Ing.Enrique.Hernandez@hotmail.com

Anónimo dijo...

hola Javier:

He visitado tu blog y se me hace muy interesante que me ha ayudado a resolver unas dudas que tenia sobre los combobox y me ha ayudado bastaste... solo tengo una pregunta con los ejemplo de "combobox dependientes" o "combobox independientes" como puedo ejecutar una macro desde una de las opcion del combobox2

El pilt®afilla - www.3piesalgato.com dijo...

Aquí lo tienes explicado (da igual que sea un elemento del combo dependiente o no: ejecutar un macro al seleccionar un elemento de un combobox.

Anónimo dijo...

Hola Javier:

Gracias por tu respuesta tan rapido pero aun tengo dudas pues la verdad no soy muy ducho con esto de las macros... mira este es tu codigo para los combobox


Private Sub ComboBox1_Change()
ComboBox2.Clear
Hoja1.Select
columna = ComboBox1.ListIndex + 1
Cells(2, columna).Select
Do While Not IsEmpty(ActiveCell)
ComboBox2.AddItem ActiveCell
ActiveCell.Offset(1, 0).Select
Loop
End Sub


la duda es como quedaria si tengo en el segundo combobox varias opciones madrid, bilbao, tarragona y cada opcion tiene que ejecutar una macro distinta... espero me puedas ayudar de antemano muchas gracias

killis dijo...

Hola, visto todos tus ejemplos y son magnificos, soy nuevo en esto de los combobox por lo que tengo un problema en tu ejemplo de los paises con los estados. como le hago para que una vez que ya escogi un pais y un estado me los copie celdas distintas, y ademas si no encontre el pais, que lo pueda yo generar, y obvio van a estar vacio el combobox de los estados, por lo que quisiera llenarlos.

gracias

Anónimo dijo...

Estimado Javier muy buena tu pagina de mucha ayuda y luz para nosotros los principiantes, mi consulta es la siguiente :
que codigo seria para que solo me permita elegir un dato del cambo que si introduzco por teclado otro dato no me acepte, un millon de gracias por tu tiempo

Anónimo dijo...

Muchas gracias por tu conocimiento maestro del Excel. Tus codigos comentados hacen que todo sea tan fácil!!! Un abrazo desde Colombia.

Aliacos dijo...

Muuchas gracias Javier... la verdad es que me ha venido de maravilla en artículo.. el problema está en que he intentado añadir dos cuadros de inserción de texto para volcar los comentarios insertados en determinadas hojas mediante un botón y tengo varios errores... uno de ejecución y no logro vaciar los cuadros de texto del userform... podrías ayudarme por favor :-(
Te dejo un enlace al archivo: www.aacoram.es/prueba.xls
Graciasssssssss

El pilt®afilla - www.3piesalgato.com dijo...

Aliacos, me lo he mirado un poco por encima, y el error se produce cuando insertas la materia y el comentario. Al darle al botón para insertar, lo que hace es incluir la materia y el comentario, en la hoja elegida, y en la primera fila vacía. Hasta ahí, todo bien. El problema viene luego, cuando "limpias" el combobox1 (fíjate que después de incluir todo eso en la hoja, limpias el combobox) de esta forma:

ComboBox1 = ""

¿Qué pasa entonces?, pues que hay un evento llamado ComboBox1_Change() que vuelve a "dispararse". Y claro, da error, porque la variable "columna" no se ha inicializado, es decir, no contiene nada, pues ComboBox1.ListIndex + 1 está vacío.

Para solucionarlo, añade esta primera línea justo después de Private Sub ComboBox1_Change():

On Error Resume Next

Esa línea viene a decir: "si encuentras un error, omítelo, continúa, y sigue haciendo lo que tengas que hacer".

Saludos.

Anónimo dijo...

Años fecha nombre
2003 29 0ctu x1
2001 34 dici x2
2003 24 nob x1
2001 29 Octu x1

lo que necesito es que en un formulario de exel en un combobox1 se me carguen (2001,2003)es decir no cargar dos veces valor repetido, luego al seleccionar año, en el segundo combo box me cague (29 0ct, 24 nob)y al haber selecciona combobo1 y combobox2 un tercer combobox se cargue con (x1) por favor ayudemme hay un ejemplo ya lo mire pero carga por columnas es que no se mucho gracias att luis_diejtt@hotmail.com

Anónimo dijo...

como se hace un buscador con 3 combobox dependientes de la base de datos en la hoja1 luego copiar los datos de esa fila buscada en la hoja2 ingresando el estado del activo (bueno malo regular) y justificacion luego en esa misma fila de la hoja1 donde se copiaron los datos se cambie el estado y se agregue la fecha de modificacion teniendo en cuenta que el estado es la ultima columna y la de fecha de modificacion esta antes sea una o dos columnas atras se podria hacer?