Lista desplegable con actualización automática

Sigo saldando las deudas con mis lectores y en esta oportunidad cómo crear dinámicamente una lista desplegable con valores únicos.

La situación es la siguiente: creamos una tabla de facturas en una hoja de Excel con los campos “fecha”, “Cliente” y “Total”.

El campo “Cliente” contiene una lista desplegable creada con Validación de Datos. El objetivo es que el usuario introduzca el nombre del cliente eligiéndolo de la lista desplegable.

Para eso necesitamos crear una lista desplegable que se actualice automáticamente cada vez que agregamos un registro con un nuevo cliente en la base de datos.

Supongamos que la los registros únicos de la lista desplegable los ponemos en la columna H (en un modelo real el rango de la lista desplegable estaría en una hoja oculta). Nuestro objetivo es actualizar la lista cada vez que ingresemos un cliente nuevo.

Para actualizar la lista desplegable vamos a programar un evento. Como verán a continuación el código es muy sencillo ya que vamos a usar la herramienta Filtro Avanzado para crear una lista de valores únicos.
Empecemos por señalar que el rango de la base de datos lo definimos como Tabla (Lista en Excel 2003), de manera que la validación de datos del campo Cliente (la columna B) se agregue en forma automática cada vez que agregamos un registro en la base de datos.

Veamos como funciona el modelo:



1 - Creamos un nombre que se refiere a un rango dinámico con la fórmula

=DESREF('base de datos'!$H$1,1,,CONTARA('base de datos'!$H:$H))

Esta fórmula genera un rango dinámico donde el último valor del rango es una celda vacía. Esto es necesario para evitar que la validación de datos nos impida ingresar un cliente que aún no encuentra en la lista de clientes. Obviamente, esto cancela la validación de datos, ya que al haber una celda vacía todo valor que ingresemos será valido, pero nos permite crear el efecto de actualización automática.

2 - Programamos un evento de hoja Worksheet_SelectionChange con este código

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Union(Target, Range("C:C")).Address = Range("C:C").Address Then
        Range("H:H").ClearContents
        Range("B:B").AdvancedFilter Action:=xlFilterCopy, _
                            CopyToRange:=Range("H1"), Unique:=True
    End If

End Sub

En este código el evento se produce cuando seleccionamos una celda en la columna C, es decir, después de haber ingresado el cliente en la columna B.

Si queremos que la lista desplegable aparezca ordenada alfabéticamente podemos en el evento una llamada a este código (que va en un módulo común del Vbe)

Sub ordenar_clientes()

    Range("H1").CurrentRegion.Sort Key1:=Range("H2"), _
                Order1:=xlAscending, Header:=xlYes _
                , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                 DataOption1:=xlSortNormal

End Sub

El código del evento en el módulo de la hoja Base de datos quedaría

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Union(Target, Range("C:C")).Address = Range("C:C").Address Then
        Range("H:H").ClearContents
        Range("B:B").AdvancedFilter Action:=xlFilterCopy, _
                            CopyToRange:=Range("H1"), Unique:=True
    End If

    Call ordenar_clientes
  
End Sub

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.