Listas desplegables dependientes múltiples con Vba

Esta es una extensión de mi nota anterior sobre listas desplegables dependientes. Uno de mis lectores pregunta

Como se haría para que tenga más niveles, por ejemplo 5 listas desplegables dependientes

Podemos extender nuestro modelo a casi todo número de niveles de dependencia introduciendo ciertos cambios. A los efectos del ejemplo extenderemos el modelo a tres niveles: continente – país – ciudad.

El modelo extendido puede descargarse aquí.

Como en el modelo de la nota anterior, creamos una lista de continentes a partir de la base de datos, usando el objeto Collection e insertando la lista de valores únicos en un rango auxiliar. Este rango es el origen de la lista desplegable creada con Validación de datos

La lista la creamos con este código

Sub insertarContinentes()
    Dim arrListaContinentes As New Collection, continente
    Dim iR As Long
 
    On Error Resume Next
    Range("lstContinentes").ClearContents
    On Error GoTo 0
 
    With Sheets("lista")
        On Error Resume Next
            For Each continente In Range("tblContinentes")
                arrListaContinentes.Add continente, continente
            Next continente
        On Error GoTo 0
        For iR = 1 To arrListaContinentes.Count
            .Cells(iR + 1, 6) = arrListaContinentes(iR)
        Next iR
    End With
        
End Sub

Para que esta lista esté permanentemente actualizada, programamos un evento Worksheet_Deactivate de la hoja que contiene los datos (la hoja “lista”)

Private Sub Worksheet_Deactivate()
    Call insertarContinentes
End Sub

Para crear la lista de los países correspondientes a los continentes, creamos un código similar al de los continentes, pero con una condición para que los países agregados a la colección sean los que corresponden al continente

Sub insertarPaises()
    Dim rngCell As Range
    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")
                If pais.Offset(0, -1).Value = Range("Continente_Elegido") Then
                arrListaPaises.Add pais, pais
            End If
            Next pais
        On Error GoTo 0
        For iR = 1 To arrListaPaises.Count
            .Cells(iR + 1, 8) = arrListaPaises(iR)
        Next iR
    End With
        
End Sub
El código de la rutina para generar la lista de ciudades queda igual a la del modelo anterior.

El último toque es agregar código en el evento Worksheet_Change de la hoja “reporte” para disparar las rutinas que crean las listas de países y ciudades

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

Por supuesto, hemos creados nombres definidos que se refieren a los rangos del modelo.

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.