El Cuadro combinado, también conocido como ComboBox, es un control ActiveX que nos permite crear una lista desplegable de la cual podremos seleccionar alguno de sus elementos. Pero antes de poder utilizar este control, es necesario saber cómo cargar datos a un ComboBox a partir de una lista de valores en una hoja de Excel.
Para este ejemplo consideraré dos posibles escenarios. El primero supone que el control ha sido agregado directamente a la hoja de Excel y el segundo escenario supondrá que el ComboBox se ha insertado en un Formulario VBA. Para ambos casos tomaré los valores de la columna A y los insertaré como los elementos del cuadro combinado.
Cargar datos al ComboBox en una hoja de Excel
El caso más simple es cuando has agregado el Cuadro combinado directamente a la hoja de Excel ya que para agregar sus elementos bastará con hacer clic derecho sobre el control ActiveX y seleccionar la opción Propiedades. Se mostrará el listado de propiedades y en la propiedad ListFillRange deberás indicar el rango donde se encuentran los datos.
Después de aceptar los cambios tendremos listo nuestro ComboBox:
Cargar datos al ComboBox en un formulario VBA
En el caso donde el control está dentro de un formulario VBA, es necesario indicar por código los elementos que serán agregados al ComboBox de la siguiente manera:
Private Sub UserForm_Initialize()
Dim rango, celda As Range
Set rango = Worksheets("Ejemplo1").Range("A1:A7")
For Each celda In rango
ComboBox1.AddItem celda.Value
Next celda
End Sub
Con este código recorremos todas las celdas del rango A1:A7 y por cada celda agregamos un nuevo elemento al ComboBox. Observa que el código está dentro del evento UserForm_Initialize lo que ocasionará que la carga de datos se haga en el momento en que se inicialice el formulario.
Si la lista de elementos de la columna A fuera fija, con cualquiera de los ejemplos anteriores será suficiente para cargar datos al ComboBox, pero si dicha lista crece continuamente y necesitas que los nuevos elementos sean considerados dentro del ComboBox, entonces debemos cambiar un poco el enfoque de la solución para considerar el uso de un nombre de rango dinámico.
Rango dinámico para cargar datos al ComboBox
Para crear un rango dinámico es necesario utilizar la función DESREF y la función CONTARA la cual nos ayudará a conocer las celdas con valores dentro de la columna que contiene los datos. Para crear el nombre debemos ir a la ficha Fórmulas > Nombres definidos > Asignar nombre.
Creamos el nombre MiLista que nos servirá para hacer referencia a este rango dinámico de celdas. En el último cuadro de texto, donde generalmente colocamos una referencia a un rango de celdas, colocaremos la siguiente fórmula:
=DESREF(Ejemplo2!$A$1,0,0,CONTARA(Ejemplo2!$A:$A))
Esta fórmula devolverá una referencia a un rango que inicia en la celda A1 (de la hoja Ejemplo2) y termina N filas por debajo. La cantidad de filas a desplazarse hacia abajo será el resultado de la función CONTARA que cuenta las celdas no vacías de la columna A.
De esta manera queda creado nuestro nombre de rango dinámico que utilizaremos para cargar datos al ComboBox. Si quieres saber un poco más sobre la creación de este tipo de nombres te recomiendo leer el artículo Nombres de rango dinámicos. En las siguientes secciones haremos uso de este rango dinámico recién creado.
ComboBox en hoja de Excel con rango dinámico
Si nuestro ComboBox se encuentra en la hoja de Excel, podemos hacer referencia al rango dinámico abriendo la ventana de propiedades y para la propiedad ListFillRange colocar el nombre del rango dinámico:
Sin embargo, con esta adecuación no es suficiente para que la actualización del ComboBox sea automática. Si dejamos las cosas así, los datos se refrescarán solamente hasta que cerremos y volvamos a abrir el libro, así que lo mejor será agregar código adicional a nuestra hoja para asegurarnos de que la actualización se hace en cuanto modificamos los elementos de la columna A.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
With ComboBox1
.Value = ""
.ListFillRange = "=MiLista"
End With
End If
End Sub
Con este código nos aseguramos de que, al momento de agregar o eliminar un elemento de la columna A, el ComboBox refrescará automáticamente sus elementos.
ComboBox en Formulario VBA con rango dinámico
Para hacer que un ComboBox que está ubicado dentro de un formulario cargue sus datos desde el rango dinámico, debemos utilizar el siguiente código:
Private Sub UserForm_Initialize()
Dim rango, celda As Range
Set rango = Range("MiLista")
For Each celda In rango
ComboBox1.AddItem celda.Value
Next celda
End Sub
La única diferencia con el primer formulario es que el rango indicado es precisamente el nombre del rango dinámico. Al momento de abrir el formulario se cargará el ComboBox con todos los elementos de la columna A.
En este caso no es necesario agregar código adicional ya que cada vez que abramos el formulario se actualizarán los elementos del ComboBox.
Solo resta que descargues el libro de trabajo para seguir probando con los ejemplos mostrados en este artículo. En el archivo descargado encontrarás dos hojas, la primera contiene los primeros dos ejemplos del artículo y la segunda hoja contiene los ejemplos que hacen uso del rango dinámico.