jueves, marzo 28, 2013

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.

19 comentarios:

  1. Hola, excelente articulo, el único detalle es que solo soporta datos únicos, por ejemplo si existieran dos ciudades o países con el mismo nombre, no obtendremos los filtros correctos.

    ResponderBorrar
  2. Víctor,
    pueden existir dos ciudades , pero no creo que existan dos países con el mismo nombre. Así que el método funciona también si los nombres de las ciudades se repiten (al elegir un país, todas las ciudades que le pertenecen aparecen en la lista).

    ResponderBorrar
  3. buen articulo, pero tengo un proyecto en el cual las listas desplegables dependientes deben ser con los userform de vsa y ademas que al seleccionar un item de la ultima dependencia me permita cargar un archivo de excel o una imagen a una nueva hoja.
    gracias

    ResponderBorrar
  4. Estimado, ¿qué es un userform de vsa?
    En cuanto a las acciones tienes que usar eventos (en mi blog hay varias notas bajo la etiqueta "eventos").
    Obviamente la respuesta a tu consulta no puede encuadrarse en el marco de un comentario.

    ResponderBorrar
  5. hola gran articulo.

    he intentado agregar un nivel mas pero no se que error estoy cometiendo ya que no he logrado mi objetivo. ojala fuera posible tener ese codigo con un nivel mas, seria una gran ayuda.

    ResponderBorrar
  6. ¿Podrías describir qué sucede cuando tratás de adaptar el código (se puede descargar, hay un enlacce en la nota)?

    ResponderBorrar
  7. Hola buenas tardes.
    me quedo una duda, por ejemplo, si solo tengo un país por continente (como en el caso de seleccionar América del norte y solo tengo EUA) ¿hay un modo para que me aparezca solo, como en el caso de #población?
    Gracias

    ResponderBorrar
  8. Se que este orden mas idóneo para entender las listas dependientes con Vba, pero
    ¿y si primero fuera, elegir la ciudad, #población (aparece automáticamente);
    elegir país y continente (aparece automáticamente)?

    ResponderBorrar
  9. Anne, en lo que respecta a la primer consulta habría que modificar el código de manera que si en la lista del continente hay un solo país éste aparezca en la celda sin nevesidad de elegirlo de la lista.
    En cuant a la segunda consulta, de un punto de vista lógico sólo podrías hacerlo si cada ciudad es un valor único. Es decir, no se de el caso de dos ciudades con el mismo nombre en distinto países. SI existieran dos ciudades con el mismo nombre en distintos países, no podríamos insertar la población antes de definir ca uál de las dos ciudades se refiere la elección.

    ResponderBorrar
  10. Hola Jorge, disculpa que te moleste pero vi una macro que hiciste en vba referente a las lista desplegables
    crees que me podrias ayudar, he tratado de usarla pero tengo problemas para filtrar un segundo nivel

    ResponderBorrar
  11. Te sugiero qie me hagas la consulta por mail privado, enviando un archivo con el ejemplo de lo que quieres hacer . Fijate en el enlace Ayuda, en la parte superior del .

    ResponderBorrar
  12. ¿cómo hace para vaciar las listas desplegables al elegir una superior?
    muchas gracias

    y ¿cómo arranca las macros?

    ResponderBorrar
  13. Lo que vacía las celdas dependientes es el evento Worksheet_Change (el último código de la nota). Para una explicación más básica te sugiero ver esta nota.

    ResponderBorrar
  14. Necesito saber como puedo vaciar celdas contiguas dentro de la misma linea.
    Por ejemplo si yo me posiciono en la celda $C$4, poder vaciar el contenido la celda $D$4, $E$4, etc, pero solamente esas, y
    si me posiciono en la celda $C$5, poder vaciar el contenido la celda $D$5, $E$5, etc, pero solamente esas
    Muchas gracias de antemano

    ResponderBorrar
  15. Joaquín, no me queda claro. ¿Por qué no seleccionar las celdas y apretar Borrar o Eliminar contenido, sencillamente?

    ResponderBorrar
  16. Hola que tal, por casualidad encontre este articulo de listas desplegables y lo he adaptado a mi proyecto y funciona. Pero al crear un nuevo campo de lista desplegable con valores unicos no consigo que funcione del todo bien no me da los valores reales. Y queria saber si me podrias echar una mano para solucionarlo.
    Lo que hice fue crear un nuevo campo lista con sus respectivos nombres definidos y duplicar el campo paises del proyecto VBA, cambiando los valores para la nueva lista. Y el erros es que me da valores unico pero no los del rango que tiene que ser.

    Muchas Gracias

    Jose I.

    ResponderBorrar
  17. Hola José, por favor fijate lo quepongo en el enlace Ayuda (en la parte superior de blog) y ponete en contacto conmigo por mail privado.

    ResponderBorrar
  18. Excelente aporte, funciono muy bien inclusive cuando cambie la posición de las celdas de vertical a horizontal pero, como podría hacer si lo quiero utilizar dentro de una tabla, es decir, si quiero que se repita todo el proceso en N número de filas

    ResponderBorrar
  19. Aldo, no me que da claro. ¿Que en cada fila haya listas desplegables?

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.