Aporte al foro: Lista desplegable inteligente.

YoelMonsalve · el 24 de febrero

Buenos días comunidad, estoy pensando además de responder las consultas por aquí presentadas, en escribir posts sobre temas diversos pero útiles que por ejemplo a mí me han surgido en trabajos realizados con anterioridad. Esperando dichos recursos puedan serle útiles a alguien más o incluso profundizar en el estudio de ellos (hay bastantes en VBA).

En esta ocasión quiero presentar un ListBox (o más precisamente DropDown) que se rellena "inteligentemente". El problema es básicamente el siguiente: tenemos un conjunto de datos en una hoja de Excel, y queremos que al escribir en una celda de otra hoja, los datos puedan ser seleccionados de entre el conjunto definido en la primera hoja, y por medio de un DropDown, o lista desplegable.

Nota: En este punto quiero aclarar que en lo persona aún me queda clara la diferencia entre controles "DropDown" y "ListBox" de Microsoft, lo que puedo ver es que ListBox es el nombre que reciben cuando son insertados en objetos Userform (formularios), y DropDown cuando se insertan en la hoja de cálculo. Además aclaro a los lectores que no utilicé controls ActiveX porque tampoco tengo totalmente claro sus ventajas con respecto a los controles de formulario tradicionales, o qué ventaja puedan tener los ActiveX sobre estos.

Siguiendo con el tema, una opción sería asociar un rango fijo o estático de datos con el control DropDown pero esta no es la solución satisfactoria si el rango crece o decrece, o cambia completamente como veremos en ampliaciones posteriores de este mismo trabajo. Entonces podemos mediante el uso de macros cargar automáticamente la lista desplegable con datos en forma dinámica. Concretamente, suponga que tenemos un conjunto de valores en la columna A de Hoja1, y queremos que estos sean mostrados como opciones en el DropDown de la Hoja2. Adicionalmente, al clicar en el DropDown, el valor elegido sea transferido a la celda F3 de Hoja 2 (ver el ejemplo del archivo adjunto).

El primer paso sería escribir los datos en la Hoja1, y en la Hoja2 crear un DropDown en este caso llamado "Lst_Nombres". Luego necesitamos dos procedimientos, uno SET_Lst y otro LOAD_Lst. El primero recibe como argumento un objeto de la clase DropDown y se utiliza como para crear una referencia a la lista desplegable que queremos. Básicamente busca entre todos los DropDowns de la hoja activa uno cuyo nombre sea "Lst_Nombres", y si lo encuentra usa una instrucción "Set" de Visual Basic para asociar el argumento recibido con el objeto encontrado. Si no encuentra un DropDown con dicho nombre, entonces asigna Nothing al parámetro pasado.

Sub SET_Lst(ByRef Lst As DropDown)
	    Dim i As Integer, _
        N As Integer
	    Dim LstName As String
    Dim LstExists As Boolean
        
    ' buscando el objeto con el nombre dado, y si lo encuentra lo
    ' asocia a Lst
    LstName = "Lst_Nombres"
    LstExists = False
    For i = 1 To ActiveSheet.DropDowns.Count
        If LCase(ActiveSheet.DropDowns(i).Name) = LCase(LstName) Then
            Set Lst = ActiveSheet.DropDowns(i)
            LstExists = True
            Exit For
        End If
    Next
    If LstExists = False Then
        Set Lst = Nothing
        Exit Sub
    End If
	End Sub

Este proceso puede parecer incómodo pero es necesario porque más adelante haremos un llamado a nuestro objeto de lista desplegable desde un módulo de código externo, concretamente del evento Worksheet_Activate(). Entonces, este otro proceso necesita referenciar el objeto DropDown apropiado (la otra otra alternativa sería declarar variables globales con visibilidad desde todos los módulos del proyecto de VBA, pero como se sabe todo enfoque basado en variables globales es básicamente una idea riesgosa).

El otro procedimiento es el que carga los valores en la lista desplegable, y recibe de argumento un objeto DropDown (obtenido justamente con el SET_Lst anterior) cuyos elementos serán rellenados dinámicamente, o sea, en cuánto sea invocado el procedimiento.

	Sub LOAD_Lst(Lst As DropDown)
	    Dim SheetName As String
    Dim S2 As Worksheet
    Dim i As Long, _
        N As Long
    
    ' Nombre de la hoja donde buscar los datos. Puede cambiarlo si es
    ' necesario.
    SheetName = "Hoja1"
    Set S2 = Sheets(SheetName)
    
    ' calculando el número de elementos a cargar
    If S2.[a1] = "" Then
        N = 0
    Else
        If S2.[a2] = "" Then
            N = 1
        Else
            N = S2.[a1].End(xlDown).Row - 1
        End If
    End If
    If N = 0 Then Exit Sub      ' sale, si no hay elementos
    
    ' y rellena la lista
    Lst.RemoveAllItems
    For i = 1 To N
        Lst.AddItem S2.Range("a" & i + 1)
    Next
End Sub

Como se puede ver en los códigos anteriormente mostrados, prefiero declarar objetos Worksheet y asignarles una hoja específica a partir de su nombre, que usar a cada rato instrucciones tan largas como: [tt]Sheets("Hoja1")[/tt]. Si la hoja con dicho nombre no existe, se generará un error en tiempo de ejecución, lo cual se puede corregir pero complicaríamos demasiado el ejemplo (yo sí suelo usar rutinas de gestión de error y comprabación de las variables en todo mi código).

Ahora, si queremos que el ítem seleccionado del DropDown sea pasado a la ceda F3 de Hoja2 simplemente tenemos que añadir un Callback a la lista:

' Callback para del DropDowm.
' Pasa el valor del ítem seleccionado a la celda F3 de "Hoja2"
Sub Lst_Click()
    
    Dim SheetName As String
    Dim S1 As Worksheet
    Dim i As Integer
    
    Dim myLst As DropDown
    
    ' Nombre de la hoja donde buscar los datos. Puede cambiarlo si es
    ' necesario.
    SheetName = "Hoja2"
    Set S1 = Sheets(SheetName)
    
    ' obtiene la lista
    SET_Lst myLst
    
    If Not myLst Is Nothing Then
        i = myLst.ListIndex
        If i > 0 Then
            S1.[f3] = myLst.List(i)
        End If
    End If
    
End Sub

Anteriormente habíamos dicho que si [tt]SET_Lst[/tt] no encuentra el objeto DropDown (porque el usuario lo borró accidentalmente por ejemplo) entones asignará [tt]myLst = Nothing[/tt]. Esta precaución le permite saber al código de [tt]Lst_Click()[/tt] que el myLst no ha sido asignado y por lo tanto no intentará rellenarlo. Aquí vemos otro ejemplo de una buena práctica de programación.

Y finalmente, para que la lista sea rellenadamente automáticamente cada vez que entremos en la Hoja2, hace falta programar el evento [tt]Worksheet_Activate()[/tt] de dicha hoja:

	Private Sub Worksheet_Activate()
	    Dim myLst As DropDown
	    ' fija el objeto myLst de la clase DropDown
    SET_Lst myLst
    
    ' y si tuvo éxito, rellena la lista
    If Not myLst Is Nothing Then
        LOAD_Lst myLst
        ActiveSheet.[f3] = ""
    End If
End Sub

IntelligentList_by_Yoel_v.1.xlsm

Antoni · el 25 de febrero

Lo mismo, pero con un control ComboBox ActiveX.

En la hoja Hoja2:

Private Sub Worksheet_Activate()
ComboBox1.List = Sheets("Hoja1").Range("A2:A" & Hoja1.Range("A" & Rows.Count).End(xlUp).Row).Value
End Sub

Private Sub ComboBox1_Change()
[F3] = ""
If ComboBox1.ListIndex > -1 Then [F3] = ComboBox1
End Sub

IntelligentList.xlsm

marcolios · el 1 de marzo

Interesante

YoelMonsalve · el 3 de marzo

Muchas gracias marcolios, y la idea es que lo puedas usar este recurso en tus proyectos, si consideras que te puede resultar útil. Y como realmente lleva bastante código, en un post posterior publicaré cómo podemos encapsularlo en una "clase" para mayor comodidad.

Por último, si te resultó interesante, puedes ayudarme con un "me gusta"

marcolios · el 3 de marzo

Hola YoelMonsalve...

Tengo un problema y no se como resolverlo, quisiera unas ideas de como hacerlo, publique en mi usuario el problema, crees que podrias verlo por favor y darme tus comentarios?

Gracias y cordial saludo