Listas desplegables dependientes múltiples con Vba

jueves, marzo 28, 2013

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 comments:

Victor Perdomo 11 abril, 2013 19:12  

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.

Jorge Dunkelman 12 abril, 2013 15:34  

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).

Anónimo,  03 marzo, 2014 17:07  

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

Jorge Dunkelman 03 marzo, 2014 19:54  

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.

Anónimo,  07 marzo, 2014 18:51  

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.

Jorge Dunkelman 08 marzo, 2014 19:12  

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

Anne 27 mayo, 2014 00:14  

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

Anne 27 mayo, 2014 00:23  

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)?

Jorge Dunkelman 27 mayo, 2014 06:52  

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.

Jesús Sanchez Leyva 06 agosto, 2014 20:12  

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

Jorge Dunkelman 07 agosto, 2014 07:42  

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 .

Anónimo,  05 marzo, 2015 14:36  

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

y ¿cómo arranca las macros?

Jorge Dunkelman 06 marzo, 2015 09:02  

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.

Joaquín María de Aguinaga Cano 07 marzo, 2015 20:34  

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

Jorge Dunkelman 08 marzo, 2015 07:13  

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

Anónimo,  11 diciembre, 2015 12:19  

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.

Jorge Dunkelman 12 diciembre, 2015 09:25  

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.

Aldo Ivan Casos Gonzales 05 octubre, 2016 21:24  

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

Jorge Dunkelman 07 octubre, 2016 14:01  

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

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP