Ambas fueron muy populares, con más de 120000 vistas y 250 comentarios. Hasta hoy en día sigo recibiendo comentarios y consultas relacionados con el tema de las notas. Una de las consultas más corrientes es como construir el modelo pero usando el control combobox en lugar de validación de datos, que es la técnica que muestro en esas notas.
Hay varias razones para usar el control combobox en lugar de listas desplegables de validación de datos. Una de ellas es la posibilidad de usar la propiedad de autocompletar del combobox. Otra es el hecho de que con validación de datos, la opción se hace evidente sólo cuando se elige la celda que la contiene. Sólo cuando seleccionamos la celda que contiene la lista veremos la flecha que nos permite desplegar la lista de opciones. En cambio el cuadro combinado (combobox) es un objeto visible permanentemente.
Empecemos por recordar que Excel cuenta con dos colecciones de objetos que pueden ser incluidos en una hoja: controles de formulario y controles ActiveX
Los controles de Formulario son más fáciles de implementar, pero no nos sirven para nuestro modelo ya que no aceptan nombres que se refieren a rangos y tampoco se pueden programar.
El primer paso es crear los nombres definidos que alimentan las listas desplegables. A los efectos de este ejemplo, los rangos serán estáticos. En un modelo más avanzado crearíamos nombres definidos con rango dinámicos.
La forma más práctica de crear nombres definidos con rangos estáticos es usar la funcionalidad “Crear desde la selección”
En la hoja “Ciudades” los rangos tienen tamaños distintos por lo que usaremos un pequeño truco para no tener que definir cada nombre por separado. En la hoja “Ciudades” tenemos de hecho una tabla donde los nombres de los países figuran en la primer fila. El proceso es el siguiente:
- Elegimos una de las celdas de la tabla (en nuestro ejemplo A1)
- Apretamos Ctrl + * para seleccionar todo el rango de la tabla
- Accionamos F5 (Ir A), apretamos el botón Especial y seleccionamos la opción Constantes-Texto. Esto hace que sólo las celdas que contienen los nombres de las ciudades sean seleccionadas.
- Finalmente usamos “Crear desde la selección-Fila superior”
Este video muestra el proceso
Ahora tenemos que insertar los controles en la hoja. Elegimos el control cuadro combinado (combobox) de la colección ActiveX y lo insertamos en la hoja
Al insertar el objeto en la hoja se activa al modo Diseño; en este estado, con el sontrol seleccionado, abrimos el menú Propiedades para definir dos propiedades el control: Linked Cell y ListFIllRange
LinkedCell es la celda donde aparecerá el resultado de la elección; en este control ponemos A2. ListFillRange es el rango que contiene los valores de la lista desplegable (en nuestro caso el nombre definido Continentes)
Una vez definidas las propiedades apretamos el icono Diseño para pasar a la situación normal de uso del control. Ahora podemos elegir un contienente y éste se registrara en la celda A2
Como explicamos en las notas anteriores, los nombres definidos no aceptan espacios entre las palabras. Por lo tanto usamos la función SUSTITUIR para transformar el resultado del control al nombre definido. En la celda A4 ponemos
=SUSTITUIR(A2," ","_")
Antes de agregar un nuevo control para la lista desplegable de los países definimos el nombre “continente elegido” que se refiere a esta fórmula:
=INDIRECTO(eleccion!$A$4)
En las propiedades del control definimos:
LinkedCell: A6
ListFillRange: continente_elegido
De esta manera la lista de las ciudades depende del continente elegido y el país elegido se registra en la celda A6
Nos resta insertar el control para elegir las ciudades. Empezamos por poner la fórmula
=SUSTITUIR(A6," ","_")
en la celda A8 para transformar la elección del cuadro combinado en el nombre definido que contiene las ciudades del país elegido.
Luego creamos el nombre definido “país_elegido” que se refiere a la fórmula:
=INDIRECTO(eleccion!$A$8)
Ahora definimos las propiedades del control:
LinkedCell: A10 (o cualquier otra celda donde queramos que aparezca la ciudad)
ListFillRange: pais_elegido
Un último toque es programar un evento que limpie el contenido de las combobox cuando se cambia la elección del continente. En el módulo de la hoja del editor Vbe ponemos este código
Private Sub ComboBox1_Change()
ComboBox2.Value = ""
ComboBox3.Value = ""
End Sub
Este evento hace que cuando se cambia el valor en la Combobox1 (continents), se borran los valores de las dos restantes combobox.
El archivo del ejemplo se puede descargar aquí.
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.