Mostrando las entradas con la etiqueta Listas Desplegables. Mostrar todas las entradas
Mostrando las entradas con la etiqueta Listas Desplegables. Mostrar todas las entradas

lunes, octubre 02, 2017

Reducción de valores mostrados en lista desplegable con criterio de búsqueda

Como ya hemos mostrado en este blog, hay muchas formas de crear listas desplegables en Excel:
  • con Validación de Datos, Lista;
  • incrustando en la hoja un cuadro combinado (combobox) o un cuadro de lista (listbox) de la colección de formularios;
  • programando un cuadro combinado o un cuadro de lista en un Userform en el editor de Vba (macros).
Todas las técnicas pueden verse en mi e-book "Listas Desplegables - la guía JLD".

Uno de los requerimientos de los usuarios es la posibilidad de reducir los valores que aparecen en la lista de acuerdo a algún criterio. Supongamos que tenemos una lista de varios cientos de productos y queremos ver sólo aquellos que contengan la palabra "aceite" antes de seleccionar los valores deseados.

En este post voy a mostrar cómo hacerlo usando un ListBox para crear la lista desplegable y un cuadro de texto para introducir el texto del criterio de búsqueda. Todo ésto lo armamos en un Userform y, por supuesto, usaremos código de Vb para activar el modelo.

Esta captura de pantalla  muestra como funciona nuestro modelo. Cada vez que ingresamos un valor en la casilla de textos, la lista desplegable se reduce a los valores que contienen ese texto




El origen de los datos de la lista desplegable es una lista de precios que se encuentra en una tabla que llamaremos "tbl_Productos"


No me voy a extender aquí sobre las bondades de usar Tablas para organizar nuestros datos en las hojas de Excel, tema que he tocado varias veces en este blog.

Creamos un Userform con un cuadro de lista (Listbox), un cuadro de texto (Textbox) y dos botones de comando


Cuando incrustamos el cuadro de lista (Listbox) en el formulario, definimos ciertas propiedades en la ventana de propiedades


Como puede apreciarse RowSource (la fuente de los datos de la lista) se refiere directamente a la tabla con su nombre.
Para facilitar el código que mostramos más adelante, creamos también un nombre definido que se refiere a la tabla


Los códigos detrás de los objetos (que van en el módulo del Userform) son los siguientes:

# - un evento Change para el ListBox. Este evento se dispara cada vez que tecleamos algún valor en la casilla de texto; cuando esta vacía vemos todos los valores de la lista de precios


 Private Sub tboxCriterio_Change()  
   Dim v As Variant, i As Long  
   v = Range("lstProductos").Value  
   With Me.lbxProductos  
   If Len(Me.tboxCriterio.Value) = 0 Then  
     .RowSource = "lstProductos"  
   Else  
     .RowSource = ""  
     For i = LBound(v, 1) To UBound(v, 1)  
       If LCase(v(i, 1)) Like "*" & LCase(tboxCriterio.Value) & "*" Then  
         .AddItem v(i, 1)  
         .List(.ListCount - 1, 1) = v(i, 2)  
       End If  
     Next i  
   End If  
   End With  
 End Sub  


# - un evento Click para el botón Cancelar

 Private Sub cbtCancelar_Click()  
   Unload ufProductos  
 End Sub  

Si estuviéramos usando este formulario en un modelo real tendríamos que escribir código para el botón Aceptar. Aquí estamos mostrando solamente cómo crear la lista desplegable así que dejaremos ese código para algún post en el futuro (en caso que algunos de mis lectores quieran ver como aplicar esta técnica a un ejemplo).

Para activar el Userform usamos el botón "Lista de productos" al cua tiene asociado este código

 Sub listaProductos()  
   ufProductos.Show  
 End Sub  


jueves, febrero 23, 2017

Listas Desplegables con valores únicos con Power Query

En el pasado remoto de este blog publiqué dos posts sobre cómo crear una lista desplegable con valores únicos a partir de una tabla de datos. El escenario habitual es una tabla de datos, supongamos de ventas, a partir de la cual queremos crear una validación de datos de los nombres de los clientes.
El procedimiento habitual sería crear un nombre definido que se refiera a la columna que contiene los nombres de los clientes. El problema es que los nombres se repiten y lo queremos es una liksta de valores únicos.

El primer post, del año 2006, presentaba una solución compleja con fórmulas. Hasta tal punto compleja que publiqué un post adicional para explicar el funcionamiento de esta fórmulas.

El segundo post, del año 2011, presentaba una solución tal vez más elegante pero también compleja, incluyendo el uso de Vba (macros).

Usando Power Query  podemos construir una solución más sencilla y ésto es lo que mostraré en este post.

El escenario:

  • una hoja que contiene una tabla con datos de ventas;

  • una hoja donde queremos queremos construir una reporte sencillo: una celda que contiene el nombre del cliente, que se elige de una validación de datos y un celda donde calculamos las ventas del cliente com SUMAR.SI, basándonos en el nombre del cliente seleccionado.

donde la celda C3 contiene la fórmula 

=SUMAR.SI(tbl_Sales2016[Cliente],B3,tbl_Sales2016[Monto])

Primer paso: crear una consulta en el Power Query para generar una lista de valores únicos ordenados alfabéticamente.



Segundo paso: crear un nombre definido que se refiera al rango de los nombres de los clientes y crear la lista desplegable con validación de datos.




La gran ventaja de este modelo sobre las soluciones anteriores es que una vez construida la consulta sólo tendremos que procuparnos de actualizar la consulta cada vez que agreguemos o quitemos filas en la tabla de datos (clientes nuevos o clientes que dejan de estar en a lista).
La desventaja es que tenemos que actualizar la consulta! Es decir, tendremos que confiar que el usuario no se olvide de hacerlo.
Podemos evitar este inconveniente creando un evento de manera que cada vez que el usuario sale de la hoja "bd" la consulta se actualice. En el módulo del editor de Vb de la hoja "bd" creamos este evento Worksheet_Deactivate



 De esta manera nos aseguramos que la lista deplegable se actualizará con cada cambio en la tabla de datos.


lunes, agosto 03, 2015

Rangos con Tablas en listas desplegables y comboboxes

No me avergüenzo de decir que soy un fanático de las Tablas. Una de las mejores herramientas de Excel, la mejor, tal vez, después de las tablas dinámicas, el Power Query y el PowerPivot.
Una de las mejores características de las tablas es que crean rangos dinámicos en todo objeto que dependa de ellas. Por ejemplo, si creamos un gráfico basado en una tabla cada cambio se reflejará automáticamente en el gráfico



Al crear una tabla Excel le asigna un nombre, por defecto Tabla1, que podemos cambiar para usar algo más significativo. Por ejemplo, rebautizamos a nuestra tabla de ventas con  "tblVentas"

También veremos que Excel la incluye en administrador de nombres como un nombre definido que se refiere al rango de la tabla

Esto nos lleva a concluir que podemos crear rangos dinámicos, como aquellos que usamos en listas desplegables, sin necesidad de echar mano a fórmulas con las funciones DESREF o INDICE. Pero para poder usar las tablas o las columnas de una tabla como rangos dinámicos tendremos primero que crear nombres definidos que se refieran a esos rangos.

A los efectos del ejemplo supongamos dos tablas de datos. Una contiene nombres de continentes y la otra contiene una columna por cada continente donde se encuentran los países del continente

A la tabla de los continentes le damos el nombre "Continente"; a la segunda tabla le damos el nombre "Paises". Para poder usar la columna de los continentes en una lista desplegable con validación de datos tenemos que crear un nombre definido que se refiera al rango de la columna


Hemos creado el nombre definido "lstContinente" que se refiere a la tabla Continente usando el lenguaje estructural de las tablas: =Continente[Continente] (en este caso el nombre de la tabla y el de la única columna coinciden).
Ahora para definir la lista desplegable con validación de datos en la celda B2 usamos el nombre definido "lstContinente"

Para crear la lista desplegable dependiente tendremos que referirnos a la columna de la tabla Paises que coincide con el continente elegido en B2. Para eso creamos el nombre definido "PaisSelec" que se refiere a esta fórmula
=INDIRECTO("Paises["&valdat!$B$2&"]")
donde "valdat" es el nombre de la hoja; es decir, creamos una cadena de texto con el operador & que la función INDIRECTO convierte en rango.




El archivo se puede descargar aquí.

Si queremos evitar los espacios en blanco al final de algunas de las listas (el rango se determina según el tamaño de la tabla, no de una columna en particular), tendremos que crear una Tabla para cada continente. En este caso sólo necesitamos crear el nombre definido que se refiere al rango de la tabla de continentes.


La lista desplegable en la celda B2 la creamos como en el caso anterior. Para la validación de datos en la celda B3 usamos la fórmula =INDIRECTO(B2).

El ejemplo puede descargarse aquí.

También podemos usar esta técnica para poblar comboboxes y listboxes. En este ejemplo creamos un Userform con dos combobox, una para los continentes y el segundo combobox para los países cuyos valores dependerán del continente elegido. Como base vamos a usar el modelo con tablas separadas por continentes.

Creamos el Userform y agregamos dos comboboxes. La lista de valores del primer combobox  (el que muestra los continentes) lo definimos directamente en el cuadro de propiedades del objeto



Como puede verse, sencillamente ponemos el nombre definido que se refiere a la tabla de continentes.
La lista de valores del segundo combobox debe depender del valor seleccionado en el combobox de continentes para lo cual debemos definir un evento Change del combo de continentes.
Hacemos un doble clic al combobox de los continentes lo que abre el módulo del userform y agrega, por defecto, el evento Change del objeto donde ponemos este código

Private Sub cbxContinentes_Change()
    With Me
        .cbxPaises.RowSource = .cbxContinentes.Value
    End With
End Sub


Ahora podemos probar el funcionamiento del Userform y las cos comboboxes seleccionando el Userform en el editor de VB y apretando F5



Ahora que vemos que nuestro código funciona vamos a mejorarlo agregando una línea para limpiar el valor del combobox de países si el usuario cambia el continente antes de cerrar el Userform

Private Sub cbxContinentes_Change()
    With Me
        .cbxPaises.Value = ""
        .cbxPaises.RowSource = .cbxContinentes.Value
    End With
End Sub


Descargar el archivo del ejemplo.

viernes, octubre 17, 2014

Lista desplegable con contenido condicional - versión con Vba

Supongamos este escenario:

tenemos una lista que asocia nombres con valores

donde los nombres se repiten.

Queremos crear una lista desplegable que muestre los valores asociados al nombre que eliljamos en la celda G2 de este ejemplo


Vamos a mostrar como está construido este modelo.

Comenzamos por mostrar la columna A que están oculta. Esta columna contiene un rango dinámico donde ponemos los valores asociados al nombre que aparece en la celda G2.


El rango D1:E14 que contiene los nombres y sus valores está definido como Tabla.
La columna A contiene los valores asociados al nombre introducido en la celda G2.
Para poner estos valores usamos un evento de tipo Change

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$G$2" Then
        Range("tblNombreValor[#All]").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("G1:G2"), CopyToRange:=Range("A1"), Unique:=False
    End If
 
End Sub


El código usa la funcionalidad Filtro Avanzado (Datos - Ordenar y Filtrar - Avanzadas) que nos permite filtrar una tabla y copiar los resultados a otro rango.

Definimos un nombre que se refiere a este rango dinámicamente con esta fórmula

=Hoja1!$A$2:INDICE(Hoja1!$A:$A,CONTARA(Hoja1!$A:$A))


Ahora definimos una validación de datos en la celda H2 con la opción Lista y el nombre definido ListValores en la casilla Origen


Con esto concluimos la construcción del modelo. El archivo del ejemplo se puede descargar aquí.

En cuanto al formato condicional para señalar las filas del nombre introducido en G2, usamos la opción Formula con esta definición



jueves, agosto 22, 2013

Cuando las flechas de validación de datos en Excel no aparecen

Cuando definimos en una celda validación de datos con la opción Lista, al seleccionarla aparece una flecha que permite desplegar la lista



¿Qué hacer cuando a pesar de haber definido todo correctamente la flecha no aparece al seleccionar la celda, como le sucedió a una de mis lectoras?

El "primer sospechoso" es que hayamos quitado la marca en "Celda con lista desplegable" en la definición de la validación de datos



Otra posibilidad es que hayamos activado la opción de ocultar objetos. Para comprobar esto podemos activar el "Panel de selección" en Diseño de página—Activar. En el panel podemos ver el estado del objeto (en esta animación "Drop Down 2)


También podemos revisar las definiciones de Excel en Archivo—Avanzadas



Si se ha seleccionado la opción Nada, todos los objetos serán invisibles, incluidas las flechas de validación de datos.

Finalmente, como con algunos gobiernos, existe la posibilidad de que se trate de un archivo corrupto. En este caso después de cerrar el archivo podemos abrirlo usando la opción "Abrir y reparar"


martes, julio 16, 2013

Fechas en combobox

Ya hemos tratado en este blog sobre la posibilidad de incrustar controles directamente en hojas de Excel. En particular hemos mostrado las bondades de usar cuadros combinados de la colección de controles ActiveX (combobox) para crear listas desplegables.

Ciertos problemas surgen cuando queremos usar una combobox incrustada en la hoja para desplegar fechas. Veamos este ejemplo: en la hoja tenemos un rango con fechas al que le hemos asignado un nombre (fechas); hemos incrustado un cuadro combinado (combobox) para que el usuario elija una de esas fechas y ésta aparezca en la celda E5. También nos hemos preocupado de darle a E5 el formato de fecha


Al desplegar las fechas esto es lo que veremos


pero al elegir la fecha las cosas se complican



El formato de fecha se ha perdido tanto en la celda ligada como en el cuadro combinado. Lo que vemos ahora es el número de serie que representa la fecha.

Pero si miramos con un poco más de atención veremos que hay un segundo problema. El valor aparece alineado a la izquierda, lo que nos sugiere que se trata de un valor de texto, no numérico. Efectivamente, el valor que pasa de la combobox a la celda es textual. Para remediar esta situación tendremos que programar un evento de la combobox.

El código del evento debe ir en el módulo de la hoja que contiene el cuadro combinado. Podemos acceder al módulo desde el editor de Vba seleccionando el objeto Sheet correspondiente


o seleccionando la combobox en la hoja y seleccionando Ver Código en el menú contextual (para poder seleccionar el objeto debemos activar la opción Modo Diseño en Programador-Controles)



En el módulo ponemos este código

Private Sub ComboBox1_Change()
    ComboBox1.Value = CDate(ComboBox1.Text)
End Sub


Ahora veremos el formato adecuado en la celda y en el cuadro combinado. Pero si prestamos atención veremos que el valor sigue siendo texto.



Si no queremos realizar ninguna operación con el valor que pasamos a la celda ligada, podemos terminar aquí nuestra tarea. Pero en caso contrario tendremos que convertir el texto en valor numérico. Recordemos que la celda ligada ya tiene formato de fecha.

Para que esto suceda agregamos una línea de código en el evento

Private Sub ComboBox1_Change()
    ComboBox1.Value = CDate(ComboBox1.Text)
    Range(ComboBox1.LinkedCell).FormulaR1C1 = CDate(ComboBox1.Text)
End Sub


Con este código el valor en la celda ligada será numérico.

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.

sábado, mayo 11, 2013

Lista desplegable con combobox dinámico

En el pasado mostramos que una de las limitaciones de crear listas desplegables con Validación de Datos es la falta de la propiedad "autocompletar". En una nota del año 2008 mostramos como usar un cuadro combinado (combobox) de la colección de controles ActiveX para superar esta limitación.

Desde entonces he recibido muchas consultas sobre el tema. La más común es cómo hacer que el control aparezca en la celda activa y desaparezca después de haber elegido el valor.

Es decir, queremos mimetizar el comportamiento de las listas creadas con validación de datos, pero usando el cuadro combinado para disfrutar de la propiedad "autocompletar".

La técnica para hacerlo incluye, obviamente programar eventos (Vba). Mostraré aquí un ejemplo sencillo, que puede aplicarse a todo modelo. En nuestro ejemplo definimos un rango que contiene los nombres de los días de la semana, que será la fuente de los valores del combobox, y un rango en la hoja donde queremos utilizar el control.

Los pasos son los siguientes:

1 – Creamos un nombre definido que se refiere al rango que contiene los días de la semana



En este ejemplo usamos el cuadro de nombres para crear el nombre con facilidad. El rango está en la hoja "valores".

2 – En la hoja "lista" incrustamos un control cuadro combinado (combobox) de la colección de controles ActiveX



Ubicamos el control en el lugar deseado (en nuestro caso sobre la celda B2) asegurándonos que ocupe toda la celda.
En la propiedad ListFillRange ponemos el nombre definido que se refiere a la lista de valores (dia_semana).



De ser necesario cambiamos la definición de la fuente a un tamaño adecuado cambiando la definición de la propiedad Font.

Al finalizar el proceso desactivamos el botón "Modo Diseño" (lo controles no funcionan cuando Excel se encuentra en modo de diseño).

3 – Definimos el rango de la hoja "Lista"", donde queremos que aparezcan los controles cuando seleccionamos alguna celda del rango. En nuestro ejemplo el rango es B2:B20 ("rngDia")



4 – Programamos el evento Worksheet_SelectionChange en la hoja "lista" de manera que cuando el usuario seleccione alguna celda del rango "rngDia", el cuadro combinado aparezca sobre esa celda. El código, que va en el módulo de la hoja, es el siguiente

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Union(Target, Range("rngDia")).Address = Range("rngDia").Address Then
        With ComboBox1
            .Visible = True
            .Top = ActiveCell.Top
            .LinkedCell = ActiveCell.Address
        End With
    Else
        ComboBox1.Visible = False
    End If
End Sub


Lo que hace este código lo siguientes:
- Vuelve visible el control (cuando la celda activa no está en el rango deseado, el control es invisible, lo que hacemos definiendo la propiedad Visible como False)
- Definimos la propiedad Top del control de manera que coincida con el ángulo superior izquierdo de la celda activa
- Definimos que celda debe recibir el valor elegido (la celda activa).
La última línea del código oculta el control si la celda activa no pertenece al rango donde queremos que aparezca la lista desplegable.

El modelo funciona así



Podemos mejorar este código de manera que al alto y ancho del control se adapten dinámicamente el alto y ancho de la celda, definiendo las propiedades Height y Wide en el código del evento

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Union(Target, Range("rngDia")).Address = Range("rngDia").Address Then
        With ComboBox1
            .Visible = True
            .Top = ActiveCell.Top
            .Height = ActiveCell.Height
            .Width = ActiveCell.Width

            .LinkedCell = ActiveCell.Address
        End With
    Else
        ComboBox1.Visible = False
    End If
End Sub


De la misma manera podemos definer dinámicamente otras propiedades como el contenido de la lista (ListFillRange), el número de valores a mostrar en el cuadro (ListRows), etc.


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.

martes, abril 16, 2013

Listas desplegables en Excel – La guía de JLD

Las listas desplegables son una de las herramientas indispensables en nuestros dashboards (tableros de comandos), informes y/o gráficos dinámicos y toda plantilla Excel donde se requiera manejar la interacción del usuario con el modelo.

Después de varios meses de preparación y correcciones (gracias a los correctores voluntarios por sus aportes, en particular a Javi) pongo a disposición de mis lectores la primer guía de la serie "Caja de herramientas Excel": Listas Desplegables.



Esta guía, dirigida tanto al usuario experimentado como al principiante, expone de forma didáctica y sistemática las distintas técnicas con las cuales podemos crear listas desplegables en Excel, desde el uso sencillo de Validación de Datos hasta técnicas con Vba (Visual Basic for Applications).



Cada capítulo va acompañado de ejemplos que pueden descargarse (sin costo adicional).

La guía tiene un costo de 7.99 Euros y puede descargarse aquí.

Actualización: si estás interesado en recibir la guía contactame por mail privado (ver el enlace Ayuda en la barra superior del blog).

jueves, abril 04, 2013

Guías JLD, Listas desplegables – Muchas gracias

La respuesta a mi pedido de colaboradores para la corrección del borrador de la lista ha superado todas mis expectativas.

Muchas gracias a todos los que se han ofrecido a ayudar.

Dada la cantidad de voluntarios he decidido cambiar de política. En lugar de enviar el borrador a los primeros cinco mails recibidos, haré un sorteo para seleccionar los cinco colaboradores.

Por supuesto, usaré Excel para el sorteo con el modelo que muestro en esta nota.

En las próximas horas estaré enviando el borrador a los voluntarios elegidos.

Nuevamente, muchas gracias a todos.

17/04/2013 - La versión final está disponible.

martes, abril 02, 2013

Guías JLD – Listas desplegables, primer borrador

17/04/2013 - La versión final está disponible.

Después de un largo proceso de preparación, acabo de completar el primer borrador de la primer guía de la serie "Cajas de Herramientas Excel JLD".

Esta primer guía desarrolla en forma sistemática y didáctica las técnicas para crear listas desplegables en Excel.

Los temas tratados van desde la creación de listas desplegables sencillas con validación de datos hasta el uso de Vba (macros) para la creación de listas desplegables dependientes con autocompletado de valores. Cada tema va acompañado por archivos con los ejemplos.



El índice de los temas tratados



Como en el pasado, recurro a mis lectores en búsqueda de editores/correctores. Los primeros cinco lectores que me envíen su dirección de mail (por favor, no ponerla en un comentario; enviarlo a la dirección que aparece en el enlace Ayuda), recibirán como recompensa la versión final en forma gratuita.

Desde ya muchas gracias.

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.

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.