Listas desplegables dependientes – otra versión (Vba)

Uno de los temas más populares en este blog es el de las listas desplegables, en particular el de las listas desplegables dependientes (ver esta nota y también esta otra).

Las técnicas que mostrado hasta ahora se basan en la creación de nombres definidos que se refieren a rangos dinámicos. La ventaja de esta técnica es que no requiere conocimientos de Vba (el lenguaje de programación de Excel); todo lo hacemos usando rangos en hojas y fórmulas. La desventaja es que a medida que nuestro modelo va creciendo en variables debemos agregar nombres y definir nuevos rangos dinámicos.

Por ejemplo, en la nota mencionada manejamos nombres de ciudades para cinco países

Para lo que debemos crear seis nombres definidos. Si decidimos agregar otro país, debemos crear el nombre correspondiente. Esto nos limita si creamos un modelo que debe ser usado por un usuario común o si no queremos que nuestros usuarios modifiquen el modelo.

La solución es usar Vba para crear las listas que alimentan las listas desplegables.

A los efectos supongamos que queremos crear un modelo donde el usuario elige un país, de acuerdo a su elección una lista desplegable le muestra sólo las ciudades de ese país y en una tercera celda se muestra la población de esa ciudad

El modelo funciona de la siguiente manera:

En la hoja “lista” tenemos una tabla de Países-Ciudades-Población

En la columna E de la hoja ponemos los países que aparecen en la tabla Países-Ciudades-Población. Esta lista debe contener valores únicos, cosa que podemos hacer con Filtro Avanzado. Pero en este modelo usaremos un poco de código Vba con el objeto Collection

Sub insertarPaises()
    Dim arrListaPaises As New Collection, pais
    Dim iR As Long

    On Error Resume Next
    Range("lstPais").ClearContents
    On Error GoTo 0

    With Sheets("lista")
        On Error Resume Next
            For Each pais In Range("tblPaises")
                arrListaPaises.Add pais, pais
            Next pais
        On Error GoTo 0
        For iR = 1 To arrListaPaises.Count
            .Cells(iR + 1, 5) = arrListaPaises(iR)
        Next iR
    End With
        
End Sub

No entraremos en los detalles técnicos, pero podemos ver que usamos un rango definido por el nombre “tblPaises”. Este es un rango dinámico que se refiere al rango en uso en la columna A de la hoja “lista”

tblPaises=lista!$A$2:INDICE(lista!$A:$A,CONTARA(lista!$A:$A))

Con este código creamos una nueva “colección” de valores (países) haciendo un rizo a lo largo del rango de los países. Como no se puede agregar un valor ya existente en la colección, usamos On Error Resume Next para que el código no se interrumpa al tratar de agregar un país existente. De esta manera obtenemos una lista de valores únicos. Luego la copiamos al rango lstPais con el loop For…Next

La celda D3 de la hoja “reporte” contiene una validación de datos con la opción Lista usando el nombre definido “lstPais”

El nombre definido “lstPais” se refiere a al fórmula =DESREF(lista!$E$1,1,0,CONTARA(lista!$E:$E)-1,1), que es el rango donde hemos puesto los países con el código mostrado anteriormente.

Para que este rango se actualice automáticamente cuando agregamos o quitamos países y ciudades en la tabla, usamos un evento Deactivate de la hoja “lista”. De esta manera, cuando el usuario vuelve a la hoja reporte luego de efectuar un cambio en la tabla, el evento dispara el código de actualización

En la celda D5 de la hoja “reporte” creamos una lista desplegable con la opción Lista y el nombre
“lstCiudad”

El nombre “lstCiudad” se refiere al rango usado en la columna G que contiene las ciudades del país elegido

Para agregar los nombres de las ciudades del país elegido a este rango, usamos este código

Sub insertarCiudades()
    Dim lCounter As Long
    Dim rngCel As Range

    On Error Resume Next
    Range("lstCiudad").ClearContents
    On Error GoTo 0

    lCounter = 2

    With Sheets("lista")
        For Each rngCel In Range("tblPaises")
            If rngCel.Value = Range("Pais_Elegido").Value Then
                .Cells(lCounter, 7) = rngCel.Offset(0, 1)
                lCounter = lCounter + 1
            End If
        Next rngCel
    End With
 
End Sub

En este código usamos nuevamente el rango “tblPaises” para comparar el país elegido (la celda D3 de la hoja “reporte” a la que le hemos definido el nomber “Pais_Elegido”); cuando el país de la tabla Paises-Ciudades-Población coincide con el país elegido, la ciudad es agregada al rango de la columna G.

Este código es disparado por un evento Worksheet_Change de la hoja “reporte”, de manera que cuando se produce un cambio en D3, se dispara el código y la lista de ciudades es actualizada.
El código del evento es el siguiente:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Union(Target, Range("Pais_Elegido")).Address = Range("Pais_Elegido").Address Then
        Range("Ciudad_Elegida").ClearContents
        Call insertarCiudades
    End If
End Sub

Finalmente, en la celda D7 usamos una función BUSCARV combinada con SI.ERROR para extraer la población de la ciudad elegida.

El archivo con el ejemplo se puede descargar aquí.

Actualización: nota que explica cómo crear un modelo ampliado (continente-país-ciudad)

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.