7 - Listas dependientes

Seguramente todos en algún momento han utilizado alguna lista desplegable en Excel.

El objetivo de esta entrada será entonces 'relacionar' 2 o más listas de modo de hacerlas 'dependientes'. Es decir que según el elemento que se seleccione en una primera lista será la información que se presente en la segunda.

Pueden ser 2 listas validadas, 2 controles ComboBox o 1 control Combobx + 1 control ListBox.

NOTA: del mismo modo que relacionamos 2 listas podemos hacerlo con 3 o más.

Primero veamos qué tipo de herramientas podemos utilizar para el uso de listas. Muchas veces nos comentan en los foros que tienen un desplegable dibujado en la hoja.... ahora veremos que hay más de un tipo de desplegable y por lo tanto tienen distinto tratamiento.

- En hojas Excel:

• Validación de datos (desde el menú Datos).
• Controles Cuadro combinado (desplegables) y Cuadro de Lista (en modo lista) de la barra Formularios.
• Controles ComboBox y ListBox de la barra ActiveX.

Estos controles se encuentran en menú Desarrollador (o Programador según la versión Excel), ficha Controles.

- En Userforms:

• Controles ComboBox y ListBox (los mismos de la barra ActiveX).

La información que se presentará en cada lista la tendremos en un rango de la misma hoja, por ejemplo M1:M2 conteniendo los meses del año.

NOTA 1: Este paso será el mismo para la primer lista en caso de Listas Dependientes.

NOTA 2: Es posible trabajar con datos ubicados en otras hojas..... pero eso será tema para otra entrada. Aquí nos ocuparemos de relacionar 2 o más listas y los datos los tendremos en la misma hoja.

1-  Validación: Desde menú Datos, Validación, las opciones son Lista y en el campo Origen ingresaremos el rango donde se encuentren, en este caso, los meses del año.

2-  Controles Barra Formulario: Dibujamos un control con esta herramienta (Cuadro combinado o Cuadro de lista). 

Al seleccionarlo con clic derecho tendremos la opción de asignarle Formato (rango, vincularlo con una celda, cantidad de elementos a mostrar al desplegarlo)  y Asignar macro si hiciera falta.

3-  Controles ActiveX: hay que pasar desde la misma ficha Controles a Modo Diseño para poder insertarlos en la hoja.

Luego de dibujarlos y haciendo clic derecho sobre el control se accede a una lista de opciones, entre las que encontramos Propiedades y Ver Código si necesitamos asociarlo a una macro.

Este tipo de controles (ActiveX) son los mismos que utilizamos en un Userform. Las propiedades que utilizaremos en este ejemplo son:

     LinkedCell : celda donde guardaremos el valor elegido.

     ListFillRange: rango de donde se toman los valores

     ListRows: cantidad de elementos que se mostrará al desplegar la lista.

Hecha esta introducción, veremos ahora cómo las hacemos dependientes unas de otras.

En todos los casos trabajaré con un rango de meses (M1:M2)  y una lista de días, que pueden ser vencimientos, cumpleaños, días de reunión, etc.

Modelo 1: con 2 listas validadas.

Como he mencionado anteriormente, la primer lista se confecciona según lo ya explicado por lo que la celda C4 tendrá asignado el rango M1:M12.

Para que se modifique el rango de la celda D4 necesitamos una macro que controle la selección en la celda C4. Esta macro se colocará en la hoja correspondiente, en mi ejemplo se llama Modelo1.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$C$4" Then Exit Sub

'si la celda C4 queda vacía se quita la lista en D4

If Target.Value = "" Then

    [D4].Validation.Delete

    Exit Sub

End If

'se arma el rango para mostrar en D4

Set busco = Range("M:M").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)

If Not busco Is Nothing Then

    'fila y columnas del mes buscado

    filx = busco.Row

    colx = Range("AZ" & filx).End(xlToLeft).Column

    'si no hay datos más allá de la col M el rango quedará vacío

    If colx > 13 Then

        rgo = Range(Cells(filx, 14), Cells(filx, colx)).Address

    Else

        rgo = Cells(filx, 14).Address   'celda vacía

    End If

    With [D4].Validation

        .Delete

        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

        xlBetween, Formula1:="=" & rgo

    End With

End If

End Sub

NOTA: En próxima entrada publicaré otro ejemplo donde las listas se relacionan mediante fórmulas. Pero esto requiere conocer el uso de las funciones INDIRECTO y DESREF además de conocer la herramienta 'Nombres de rango', por lo que dejaré como tema aparte.

Modelo 2: Cuadro combinado y Cuadro de lista de la herramienta Formulario.

Este modelo es válido tanto para mostrar la lista dependiente en otro cuadro combinado o en un cuadro lista. En mi ejemplo utilicé la segunda opción.

Como ya hemos visto al definir los controles de la barra Formulario, el primer control tendrá asignado el rango M1:M12 y la celda A5 para guardar el índice del elemento seleccionado. Este índice nos indicará la fila del mes elegido ya que al mes enero le corresponde la celda M1 y así con cada mes.

Lo que se intenta es mostrar en la lista los días que corresponden al mes elegido. 

Como el rango para asociarlo a una lista debe mostrarse de modo vertical, lo que haremos una vez identificado el rango de días, es transponerlo en una columna auxiliar y de allí asociarlo al cuadro de lista.

NOTA: la columna auxiliar L puede mantenerse oculta.

Para este tipo de controles las macros se ubican en un módulo. Luego se la asignamos con clic derecho sobre el primer control, opción Asignar Macro.

Sub CuadroCombinado1()

'fila del mes y últ col con días

filx = [A5]

colx = Range("AZ" & filx).End(xlToLeft).Column

'se limpia la col auxiliary se pasa el rango de días allí de modo vertical

Range("L:L").Clear

Range(Cells(filx, 14), Cells(filx, colx)).Copy

Range("L1").Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _

    False, Transpose:=True

Application.CutCopyMode = False

'se ubica la última celda ocupada. También puede ser utilizada la últ col -14

fini = Range("L" & Rows.Count).End(xlUp).Row

'se asigna el rango obtenido al control ListBox

ActiveSheet.Shapes("List Box 2").Select

Selection.ListFillRange = "Modelo2!L1:L" & fini

'opcional: seleccionar alguna celda

Range("F5").Select

End Sub

Para limpiar el Cuadro combinado alcanza con borrar el contenido de la celda A5. Entonces necesitaremos controlar esta acción para limpiar también el Cuadro de lista.

Para ello colocaremos un código en la misma hoja Modelo2:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$5" Then

    If Target = "" Then

        ActiveSheet.Shapes("List Box 2").Select

        Selection.ListFillRange = ""

        'opcional: seleccionar alguna celda

        Range("F5").Select

    End If

End If

End Sub

NOTA: observar el nombre que se incluye en las macros para identificar el Control de Lista. Esto puede variar según las distintas versiones de Excel.

Para obtener el nombre, dibujar el control en la hoja. A continuación encender la grabadora y ubicar el control en la posición adecuada o ajustarlo en tamaño. Detener la grabadora y en un módulo se encontrará un código de donde se puede obtener el nombre correcto para las macros.
Por ejemplo:

                          ActiveSheet.Shapes.Range(Array("List Box 3")).Select

Modelo 2: Combobox y ListBox de la barra ActiveX

Este modelo es válido tanto para mostrar la lista dependiente en otro ComboBox o en un ListBox. En mi ejemplo utilicé la segunda opción.

Se dibuja el primer control y se asignan las Propiedades de rango y celda asociada tal como hemos visto anteriormente en el punto 3.

Dibujamos el ListBox y dejamos vacía la propiedad ListFillRange.

NOTA: Recordar que para acceder a las distintas opciones haciendo clic derecho sobre estos controles hay que pasar previamente a Modo Diseño desde el menú Desarrollador (o Programador).

Las macros para este tipo de controles se ubican en la hoja donde se encuentran los controles.

Clic derecho sobre el ComboBox, opción Ver Código nos introduce en el Editor, en la hoja correspondiente. Y allí colocaremos una macro como la de este ejemplo:

Private Sub ComboBox1_Click()

'se limpia la lista de datos anteriores

ListBox1.Clear

If ComboBox1 = "" Then Exit Sub

'fila del mes elegido ...depende de la ubicación del elemento seleccionado

filx = ComboBox1.ListIndex + 1

'ultima col con nros de días

colx = Range("AZ" & filx).End(xlToLeft).Column

'si colx es 13 es la col M (del mes) y no tiene días

If colx > 13 Then

    'se recorre el rango desde la col 14 (N) agregando al Listbox

    For x = 14 To colx

        ListBox1.AddItem Cells(filx, x)

    Next x

End If

End Sub

Aquí necesitaremos también la macro que nos limpie los controles y eso lo haremos desde el evento Change de la hoja:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$6" Then

    If Target = "" Then ListBox1.Clear

End If

End Sub

NOTA: observar que aquí utilicé otro modo de llenar el ListBox. En lugar de asignarle un rango se recorre la fila de días agregándolos al listado. Pero bien podría haber utilizado el modelo 2.

Modelo 4: Controles en Userforms.

Para trabajar con este tipo de controles dentro de un Userform se siguen las mismas instrucciones dejadas en el Modelo 3.

Los controles son los mismos. Lo que puede cambiar es el evento desde donde los limpiaremos, que puede ser desde un botón Cancelar o al finalizar algún otro proceso. 

La instrucción, ajustando el nombre del control, siempre será:

                    ListBox1.Clear 

Acceso al  VIDEO N° 7.

Descargar el ejemplo completo desde aquí.