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

Combobox: sacándoles provecho

Como son varias las consultas que he recibido esta pasada semana, sobre el uso de los combobox, y a pesar de que ya son varios los artículos que tratan el tema, vamos a ver algunas cosas que creo que pueden ser de utilidad a muchos de los lectores del blog.

Vamos a ver varias cosillas sobre los combobox, para sacarles el máximo provecho. En concreto, en este artículo explicaré como hacer las siguientes cosas:

  • Ejemplo 1: Cómo crear combobox dependientes, donde los ítems sean las diferentes hojas de cálculo que componen el libro (esto ya lo vimos en otro artículo, pero para un combobox no insertado en un formulario), y como el segundo combobox (el dependiente) muestra los datos que hay en la hoja seleccionada en el primer combobox.

  • Ejemplo 2: Cómo crear un combobox con valores únicos a partir de una lista donde se repiten varios de los valores. Es decir, solo se mostrarán los ítems de tal forma que no estén repetidos, a pesar de que tengamos una lista con valores repetidos.

  • Ejemplo 3: Cómo acceder a un elemento seleccionado en un combobox dependiente (aunque la técnica también nos servirá si lo que queremos es acceder simplemente a un elemento de un único combobox), o lo que es lo mismo, como saber en qué celda está el elemento del combobox que hemos seleccionado.


Ejemplo 1:

Comencemos por el principio. Vamos a ver como crear combobox dependientes dentro de un Userform, con los nombres de las hojas que componen el libro, y con los datos de la hoja seleccionada, que cargarán en el combobox secundario.

Una vez tengamos creado el userform, con sus dos combobox, tan solo tendremos que añadir estos códigos. El código del primer combobox será este, suponiendo que lo hayamos llamado Combobox1:

Private Sub ComboBox1_Enter()
'En caso de error, que continúe
On Error Resume Next
'Esto se producirá cuando nos
'situemos sobre 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


El código del segundo combobox (el dependiente), será este, suponiendo que lo hayamos llamado Combobox2:

Private Sub ComboBox2_Enter()
'En caso de error, que continúe
On Error Resume Next
'Ocultamos el procedimiento
Application.ScreenUpdating = False
'Limpiamos lo que haya
ComboBox2.Clear
'Pasamos el dato de la hoja, a una variable
hoja_elegida = ComboBox1.List(ComboBox1.ListIndex)
'seleccionamos esa hoja
Sheets(hoja_elegida).Select
'seleccionamos la cela A1
Range("A1").Select
'Llenamos el combo con los datos del combobox1
'para lo cual supondremos que los datos de cada
'hoja están a partir desde A1 hacia abajo, y
'hasta encontrar una fila vacía

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 a la hoja1 que es donde
'tenemos el botón para lanzar el userform

Hoja1.Select
'Mostramos el procedimiento
Application.ScreenUpdating = True
End Sub


Para lanzar el userform, nos bastará con incluir este código en un módulo VBA (suponiendo que el userform se llame userform1):

Sub lanzar_formulario()
'Lanzamos el userform
UserForm1.Show
End Sub



Ejemplo 2:

Para el caso de que deseemos crear un combobox con valores únicos (vamos a suponer que también cargamos las hojas que componen el libro, como primer combobox, y que los datos de cada hoja, se cargarán en el segundo combobox), lo que tendremos que hacer es añadir este código en el combobox2 (el código del combobox1 será el mismo que en el ejemplo 1 que acabamos de ver hace unos instantes):

Private Sub ComboBox2_Enter()
'En caso de error, que continúe
On Error Resume Next
'Ocultamos el procedimiento
Application.ScreenUpdating = False
'Limpiamos lo que haya
ComboBox2.Clear
'Pasamos el dato de la hoja, a una variable
hoja_elegida = ComboBox1.List(ComboBox1.ListIndex)
'seleccionamos esa hoja
Sheets(hoja_elegida).Select
'seleccionamos la cela A1
Range("A1").Select
'Llenamos el combo con los datos del combobox1
'para lo cual supondremos que los datos de cada
'hoja están a partir desde A1 hacia abajo, y
'hasta encontrar una fila vacía

Do While Not IsEmpty(ActiveCell)
'si el dato no está repetido, lo añadimos
'a la variable "datos", separando los
'elementos por comas

If InStr(datos, ActiveCell) = 0 Then
'añadimos el dato
datos = datos & "," & ActiveCell
End If
'bajamos una fila
ActiveCell.Offset(1, 0).Select
Loop
'eliminamos la primera coma
datos = Right(datos, Len(datos) - 1)
'separamos cada elemento por la coma
dato_individual = Split(datos, ",")
'recorremos todos los elementos
For i = 0 To UBound(dato_individual)
'Añadimos los elementos
ComboBox2.AddItem dato_individual(i)
Next
'Volvemos a la hoja1 que es donde
'tenemos el botón para lanzar el userform

Hoja1.Select
'Mostramos el procedimiento
Application.ScreenUpdating = True
End Sub


Si os fijáis bien, lo que hemos hecho en ese combobox2, es crear un array (o un vector), donde vamos añadiendo los valores que nos vamos encontrando en cada hoja de cálculo, siempre y cuando no estén ya en el array. Estos elementos los vamos separando por comas. Luego al final, lo que hacemos es separar los elementos del array nuevamente por ese carácter (la coma), para escribirlos en el combobox2.


Ejemplo 3:

Finalmente, vamos a ver como acceder a uno de los elementos del combobox dependiente. Es decir, vamos a acceder al elemento seleccionado del combobox2. Para que se entienda mejor, si hemos seleccionado un item (un elemento del combobox), que está en la hoja3, y en la celda A2, nos situaremos encima de ella, una vez pulsado el botón de aceptar que crearemos en el formulario, expresamente para tal fin.

Tanto el código del combobox1, como el del combobox2, serán los mismos que los que vimos en el ejemplo 1 anterior, así que nos ahorraremos el volverlos a poner aquí. La única diferencia que encontraremos entres aquel primer ejemplo y este tercer ejemplo, será que en este ejemplo de ahora, vamos a añadir un botón al userform (dejaremos el nombre que tiene por defecto, es decir, CommandButton1), y su código será el siguiente:

Private Sub CommandButton1_Click()
'En caso de error, que continúe
On Error Resume Next
'leemos lo que nos devuelven los combobox
hoja_elegida = ComboBox1.List(ComboBox1.ListIndex)
dato_elegido = ComboBox2.List(ComboBox2.ListIndex)
'vamos a la hoja elegida
Sheets(hoja_elegida).Select
'vamos a la celda con el valor elegido en el combobox2
Cells.Find(What:=dato_elegido).Activate
'Eliminamos el userform de la memoria
Unload UserForm1
End Sub


Al pulsar sobre el botón "Aceptar", lo que estamos haciendo, es seleccionar el elemento del combobox1, es decir, la hoja seleccionada, y seleccionar también el elemento del combobox2. Luego buscará ese item o elemento del segundo combobox, en la hoja seleccionada en el combobox1, para situarnos sobre él. Una vez hecho todo eso, descargaremos el userform de memoria, para liberar recursos del sistema.


Desde aquí podéis descargar un fichero comprimido en formato zip, con los tres ejemplos que hemos visto en este artículo.



16 comentarios:

Anónimo dijo...

son muy buenos y utiles tus ejemplos!!!!.........pero tengo una duda ojala me puedas ayudar, tengo una macro donde el 1° combobox selecciona un fichero y lo abre, y el 2° se carga con las hojas de éste, y como resultado final copia en otro fichero solo la hoja que se selccionó y cierra el fichero original, el punto es que si selecciono un fichero y luego cambio de opinion y eligo otro el programa no cierra el primero que se abrio......he provado de varias formas, pero aun no puedo lograr que se cierre, sólo cierra el último fichero elegido....me ayudas?

saludos
y gracias

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

Se me ocurre que puedes poner en el evento change del 1er combobox, que se cierren todos los libros abiertos, excepto el libro activo (el del macro). Así cada vez que cliqueas en él, se cerrarán los libros que se hayan abierto por error. ¿Cómo cerrar todos los libros, excepto el activo?. Hummmm, se me ocurre que con un bucle for, desde 1 hasta el nº total de libros (Workbooks.count). Si el libro activo tiene el mismo nombre que el libro i, entonces no lo cierras. En caso contrario lo cierras.

Anónimo dijo...

Felicidades por el Blog, de lo mejor en excel que vi.

Se que puede que vayas agobiado a preguntas pero yo tengo una que no vi por mucho que busque.

Incluso lo intente por conexiones SQL.

Tengo una BD en una tabla en una hoja xls.

Mi intención era hacer un UserForm con 3 combos para que me filtrara entre Familia,Subfamilia y Tipo, y un boton que una vez elegido el campo o fila en este caso me pegue una serie de datos donde diga(esto ultimo simple).Los combos han de ser datos no repetidos ya que en familia y subfamilia se repiten.Lleno el primer combo y alli me pierdo. Te agradeceria una mano, pista, enlace....

Saludos y felicidades nuevamente por tu Blog.

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

Muchas gracias por tu comentario.

En este artículo que acbas de leer, hay un ejemplo de como cargar elementos en un combobox de forma que no se repitan. Eso ya lo tienes. Ahora el problema lo tienes en como cargar el segundo combobox en función de los datos cargados en el primero. ¿Verdad?. Pues la solución la tienes aquí: como trabajar con combobox dependientes.

Saludos.

Anónimo dijo...

Gracias por la ayuda, ya esta solucionado aunque de otra forma (puede menos elegante y no se si muy efectiva con Tablas muy grandes), coloco el rellenado de los combos 2 y 3 por si a alguien le hace falta:

Private Sub ComboBox1_Change()

Dim TodaslasCeldas2 As Range, Celda2 As Range, Celda2x As Range
Dim ElementosUnicos2 As New Collection
Dim Item2 As Variant

On Error Resume Next
ComboBox2.Clear
For Each Celda2 In Worksheets(3).Range("B2:B100")

If Celda2.Offset(0, -1).Value = ComboBox1.Value Then

ElementosUnicos2.Add Celda2.Value, CStr(Celda2.Value)
End If

Next Celda2
On Error GoTo 0

'Añadir elementos no duplicados a un Cuadro de lista.
For Each Item In ElementosUnicos2
UserForm1.ComboBox2.AddItem Item

Next Item

'Mostramos el procedimiento
Application.ScreenUpdating = True

End Sub

Private Sub ComboBox2_Change()

Dim TodaslasCeldas3 As Range, Celda3 As Range
Dim ElementosUnicos3 As New Collection
Dim Item3 As Variant

On Error Resume Next
ComboBox3.Clear
For Each Celda3 In Worksheets(3).Range("C2:C100")

If Celda3.Offset(0, -1).Value = ComboBox2.Value Then

ElementosUnicos3.Add Celda3.Value, CStr(Celda3.Value)
End If

Next Celda3
On Error GoTo 0

'Añadir elementos no duplicados a un Cuadro de lista.
For Each Item In ElementosUnicos3
UserForm1.ComboBox3.AddItem Item

Next Item

'Mostramos el procedimiento
Application.ScreenUpdating = True
End Sub

Jordi dijo...

Muchas gracias por los consejos, estaba buscando como desplazarme a diferentes hojas de un libro mediante los datos introducidos en un combobox y aunque no lo explicas en el articulo explicitamente, si que he podido deducirlo.

Gracias otra vez y sigue así.

Anónimo dijo...

Hola buen dia.

Alguien me puede decir si es tan amable, como lleno un combobox con un rango pero que se encuentra en otro archivo de excel ??

Gracias.

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

Pues con la información de este artículo, más la de este otro sobre como leer datos de otro fichero de excel, tendrás la solución.

Saludos.

alexxxxxo dijo...

Apreciable amigo
Espero me puedaS ayudar:
Tengo un formualrio en el cual tengo un textbox que me permite capturar un dato de una lista que esta en otra hoja, para colocarlo en una columna de una hoja de excel, ejemplo en A2:A tengo que introducir "001/001/2010", "001/004/2010",003/004/2010", etc, esto lo hice asignandole el rango de validación en ROWSOURCE que es mi lista de datos, pero al mismo tiempo quiero impedir que se pueda capturar un dato dos veces en la misma columna, ¿como puedo lograrlo?
Gracias de antemano

Huderney dijo...

hola no soy experto en el tema pero estoy necesitando que el valor que escoja de un combobox lo ingrese en la celda activa ActiveCell.Offset(0, 1).Select
no he podido con esto por favor me ayuda... mil gracias

JoaoM dijo...

Hola.
Estuve visualisando el trabajo y quise hacerlo con las 3 opciones en un solo libro.

En el iuserform coloque 3 ComboBox y un boton.
En el ComboBox1 le integre el codigo 1, en el ComboBox2 le integre el codigo2, EN EL COMBOBOX3 le integre el codigo de la 2ª opcion y en el boton integre el codigo para el Commadbotton1.
Resulta que me funciona el comboBox2 y no el 3, si hago el intercambio de codigo entre los ComboBox2 y 3 solo funciona el 2 y no el 3.
Lo otro es que al abrir el form YA el ComboBox2 contiene datos SIN QUE SE SELECIONE NADA en el ComboBox1.
Podria arreglarse?

Anónimo dijo...

Hola una pregunta como podria crear un cuadro combinado solo que solo tenga 2 opciones por ejemplo necesito hacer una macro e la cual en el cuadro despegable solo tenga las opciones de deposito y retiro pero estos no estan en ninguna lista de excel solo se deben seleccionar para realizar una funcion determinada. tengo k hacerlo con un cuadro despegable noc con el de las opciones de punto.

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

Prueba esto:

Private Sub UserForm_Activate()
ComboBox1.AddItem "Depósito"
ComboBox1.AddItem "Retiro"
End Sub

Saludos.

Anónimo dijo...

Nuevamente muchas felicidades por tu blog, tengo una consulta , como se puede bloquear para que el combobox no acepte otro dato que no este entre los cargados en el combobox? tengo un formulario con tres combobox y estan cargados, solo que cuando me situo en el combobox puedo introducir otros datos desde teclado, pero yo quiero que solo acepte los datos que se cargo en el comboboox desde ya muchas gracias un abrazo Esteban Plantarosa

Anónimo dijo...

HOLA, TENGO UN FORMULARIO CON UN COMBOBOX Y TRES TEXTBOX Y QUIERO JALAR DATOS DE UNA HOJA EXCEL A MIS TEXTBOX A TGRAVES DEL COMBOBOX,
GRACIAS

Anónimo dijo...

Hey exelente trabajo el segundo ejemplo ,el de valores unicos en el combobox es lo que necesito.. pero surge un pequeño inconveniente en mi excel tengo 247087 registros al hacer ese ejemplo que colocaste en el blog simplemente la maquina se vuelve loca recorriendo cada casilla abria alguna manera mejor de hacerlo ??? tal vez como lo hace un filtro de excel que de inmediato me muestra cuales valores unicos hay en una columna ... de antemano muchas gracias si sabes como