viernes, marzo 22, 2013

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.

12 comentarios:

  1. Espectacular. Gracias Jorge.

    ResponderBorrar
  2. Esta muy bien. Como se haría para que tenga más niveles, por ejemplo 5 listas desplegables dependientes?

    Saludos

    ResponderBorrar
  3. Como se haría para que tenga más niveles...
    Irá en un próxima nota.

    ResponderBorrar
  4. Hola, llevo dias pensando en tu post y me va de coña pero en mi caso necesitaria poder hacer varias listas desplegables independientes sobre los mismos datos. Sabes como lo podria hacer?

    ResponderBorrar
  5. Moises, si haces un clic sobre la etiqueta "Listas Desplegables" en la nube de etiquetas, podrás leer todas las notas sobre el tema. Hay una variedad de técnicas, con o sin Vba.

    ResponderBorrar
  6. Muy, muy bueno Jorge, gracias.

    ResponderBorrar
  7. Muy, muy bueno, Jorge. Gracias.

    ResponderBorrar
  8. Muy bueno y bien explicado al igual que tus otras notas felicitaciones solo tengo una duda esto es aplicable a conbobox con listas dinamicas?desref(dato!,0,0,Contara$A:$A) y muchas gracias por tu tiempo.

    ResponderBorrar
  9. Si, es posible. Personalmente prefiero usar INDICE con CONTARA, pero cualquier técnica que cree un rango dinámico funcionará.

    ResponderBorrar
  10. Gracias por responder tan rápido de verdad que tu blog me ayuda un montón ,me encontré esta nota" Listas desplegables dependientes con combobox" y es lo que mas se acerca a la pequeña aplicación que me estoy creando para una propiedad agrícola y es lo que quiero hacerlo dinámico a los nombres todavía sin éxito pero ya vere como me va hoy.
    Gracias otra ves por tu tiempo.

    att Cesar

    ResponderBorrar
  11. Buenas,

    No me puedo bajar el ejemplo

    Gracias

    ResponderBorrar
  12. Acabo de corregir el enlace. Avisame si persisten los problemas.

    ResponderBorrar

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