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.

Agregar controles en una hoja de Excel usando Vba

Los controles (casillas de verificación, cuadros combinados, botones de opción, etc.) dan un "toque profesional" a la hoja pero no siempre son la mejor opción. En  general podemos encontrar soluciones más prácticas usando, por ejemplo, validación de datos y/o funciones SI.

Lo más corriente es agregar controles en la hoja en forma manual, usando el menú Desarrollador-Controles-Insertar. Existen dos colecciones de controles: Formulario y ActiveX.
En esta nota veremos como insertar controles ActiveX usando Vba.

Supongamos esta tabla de facturas con sus fechas de vencmientos

En el campo "Pagada" (columna E) anotamos "SI" cuando la factura ha sido pagada. Esto nos permite crear el informe que nos muestra los totales de facturas atrasadas, pagadas y a vencer.
Las fórmulas en el informe son:

celda H4:

=SUMAPRODUCTO((tblFacturas[Fecha Vencimiento]<H3)*(tblFacturas[Pagada]<>"SI")*tblFacturas[Importe])

celda H5:

=SUMAR.SI(tblFacturas[Pagada],"SI",tblFacturas[Importe])

celda H6: 

=SUMAPRODUCTO((tblFacturas[Fecha Vencimiento]>=H3)*(tblFacturas[Pagada]<>"SI")*tblFacturas[Importe])

la Tabla "tblFacturas" se refiere al rango B2:E16 (supongo que la mayoría de mis lectores ya hayan adoptado la sana costumbre de usar Tablas para organizar matrices de datos).

Y ahora vayamos a la cuestión de los controles incrustados en hojas de Excel. En nuestro ejemplo queremos usar casillas de verificación para señalar que una factura ha sido pagada  en lugar de un "plebeyo" SI.

Queremos que nuestro informe se vea así:

Si nuestra tabla tiene pocas filas podemos simplemente agregar los controles manualmente. Las casillas están definidas sin texto, ligadas a ka celda sobre la cual están ubicadas y el valor es FALSO. Todo esto tenemos que definirlo cambiando las propiedades por defecto de la casilla. Para ahorrarnos el trabajo de hacerlo cada vez que queremos agregar una casilla podemos usar esta macro:

Sub insert_one()
   
    With ActiveSheet.OLEObjects.Add(classtype:="Forms.Checkbox.1", _
         Top:=ActiveCell.Top + 1, Left:=ActiveCell.Left + 15, _
         Height:=ActiveCell.Height, Width:=ActiveCell.Width * 0.5)
         .Object.Caption = ""
         .LinkedCell = ActiveCell.Address
         .Object.Value = False
         .Object.BackStyle = fmBackStyleTransparent
     End With
    
End Sub

El código agrega la casilla de verificación en la celda (con el método Add); luego definimos algunas propiedades:
Caption = "" para que la casilla no contenga ningún texto;

LinkedCell = Activecell.Address para ligar la casilla a la celda; esto es necesario para poder luego utilizar el valor de la casilla en nuestras fórmulas.

Value = False para que éste sea el valor por defecto en la celda vinculada a la casilla de verificación.

En la tabla de las facturas cambiamos el color de la fuente en las celdas de la columna Pagadas a blanco, para que el valor de la casilla en la celda vinculada no sea visible.

Dado que en el campo Pagada tenemos ahora valores FALSO o VERDADERO (cuando la casilla a sido marcada), tenemos que modificar nuestras fórmulas

celda H4:

=SUMAPRODUCTO((tblFacturas3[Fecha Vencimiento]<H3)* (tblFacturas3[Pagada]=FALSO())*tblFacturas3[Importe])

celda H5:

=SUMAR.SI(tblFacturas3[Pagada],"VERDADERO",tblFacturas3[Importe])

celda H6: 

=SUMAPRODUCTO((tblFacturas3[Fecha Vencimiento]>=H3)*(tblFacturas3[Pagada]=FALSO())*tblFacturas3[Importe])

Si tenemos una tabla con muchas filas podemos y queremos agregar las casillas en un única operación podemos usar esta macro (igual a la anterior a la que le hemos agregado un loop):

Sub insert_check()
    Dim rngCell As Range
 
    Application.ScreenUpdating = False
 
    For Each rngCell In Selection
        rngCell.Select
        With ActiveSheet.OLEObjects.Add(classtype:="Forms.Checkbox.1", _
             Top:=ActiveCell.Top + 1, Left:=ActiveCell.Left + 15, _
             Height:=ActiveCell.Height, Width:=ActiveCell.Width * 0.5)
             .Object.Caption = ""
             .LinkedCell = ActiveCell.Address
             .Object.Value = False
         End With
     Next rngCell
    
     Application.ScreenUpdating = True
    
End Sub
Sub del_all_cb()

Uso de controles en hojas de Excel - Spin Button con valores no enteros

En la nota anterior vimos como superar la limitación del uso de números negativos en los controles Spin Button y Scroll Bar. En esta nota veremos un rodeo para usar números no enteros en estos controles.

Supongamos que queremos crear un Control de Número que vaya de 0 a 5, pero en saltos de 0.5 (en algunos países se usa la coma en lugar del punto para separar la parte decimal del número).

Excel nos deja ingresar un número no entero en la casilla Incremento,

pero al apretar Aceptar cambia la definición dejando sólo la parte entera del número. En  nuestro ejemplo, la casilla Incremento mostrará 0.

Como en la nota anterior, el rodeo consiste en usar una celda auxiliar. Siguiendo con nuestro ejemplo, el valor mínimo es 0; el máximo será 10 (resulta de dividir 5 por 0.5) y el incremento 1. En nuestro ejemplo la celda vinculada es A3 y en la celda A4 ponemos la fórmula =A3/2 o su equivalente =A3*0.5

En resumen, la norma es:

• mínimo: 0
• máximo: máximo deseado dividido por el incremento deseado
• incremento: 1
• en la celda auxiliar: celda vinculada multiplicada por el  incremento deseado (o dividida por el inverso del incremento deseado)

Asi que si quisiéramos ir de 0 a 10 con incrementos de 1/3, ponemos 30 en la casilla del máximo y en la celda auxiliar (A4) =A3/3

Si usamos el Control de Número (Spin Button) de la colección ActiveX podemos dar una solución sin usar una celda auxiliar programando eventos para definir el control dinámicamente de acuerdo a valores que ingresemos en celdas de la hoja.

Siguiendo con nuestro ejemplo, ponemos el valor máximo deseado (10) en la celda E3 y el valor de incremento enla celda E4. En las propiedades del control dejamos el valor de la propiedad LinkedCell (celda vinculada) en blanco

Tal como indicamos en la nota anerior, abrimos el editor de Vb en el módulo de la hoja que contiene el control seleccionando el control y apreando Ver Código. En el módulo de la hoja ponemos estos dos eventos:

El evento GotFocus define el valor máximo del control de acuerdo a los valores que ingresamos en las celda E3 y E4 y el evento Change pasa el valor calculado a la celda vinculada con cada cambio del control

Uso de controles en hojas de Excel - Spin Button con valores negativos

Desde casi los primeros días de este blog he escrito sobre el uso de controles en hojas de cálculos. Los usos son casi ilimitados: dashboards, gráficos dinámicos, listas desplegables, etc.
Excel cuenta con dos colecciones de controles: Formulario y Activex. Ya hemos escrito sobre las ventajas y desventajas de cada una de estas colecciones. En esta nota veremos como sobreponerse a las limitaciónes de los controles Control de Número (Spin Button) y Barra de Desplazamiento (Scroll Bar)

• aceptan sólo números enteros
• no aceptan números negativos

En esta nota veremos un rodeo para poder usar números negativos en estos controles.

Por ejemplo, si queremos usar el control de números (spin button) de la colección Formlarios con valores que vayan de -10 a 10, al tratar de definir el valor mínimo veremos lo siguiente

Para superar esta limitación, siguiendo con nuestro ejemplo,usamos las siguientes definiciones:

Definimos 0 en el valor mínimo y el doble del máximo deseado para el Valor Máximo. En nuestro ejemplo, el valor del control está ligado a la celda A3; en la celda A4 ponemos la fórmula =A3-10. La celda A4 mostrará los valores deseado al accionar el control

Si usamos el control Activex la situación es diferente. Aparentemente Excel acepta el número negativo en la definición del mínimo

Pero al tratar de usar el control veremos lo siguiente:

al descender de 0, en lugar de -1 Excel poner en la celda ligada 65535!! (suena familiar, no es cierto? 65536 es el número máximo de filas en las versiones anteriores a Excel 2007).

En el caso del control ActiveX, la solución consiste en programar un evento para el objeto. En el menú Desarrollador apretamos el botón Modo de Diseñño, seleccionamos el control y activamos la opción Ver Código

Al apretar Ver Código, el editor de Vb se abre en el módulo de la hoja; allí ponemos este código

Con este código el control pasa los números deseados, también los negativos. La ventaja de usar el control ActiveX es que no necesitamos agregar una celda auxiliar.

En la próxima nota veremos como usar estos controles con valores no enteros.

Una alternativa al control Calendario en Excel 2010 – Monthview

Como mencionamos en el pasado el control Calendar (mscal.ocx) fue removido del paquete Office 2010. Esto genera problemas en aplicaciones y modelos de Excel que fueron desarrollados en versiones anteriores y queremos usarla en Excel 2010.

En la nota mencionada sugerimos tres alternativas posibles: usar el Data Picker, el complemento desarrollado por Sam Radakovitz o descargar e instalar el control. Esta última alternativa implica tener que registrar el control para poder usarlo. El problema con esta última solución es que no podemos registrar el control en Windows 8.

Para los que, como a mí, no les gusta el Data Picker, existe la posibilidad de usar el control Monthview (para las versiones 2007 y 2010 de Excel).

¿Cuándo queremos usar el control? Cuando necesitamos asegurarnos que el usuario ingrese fechas en el rango y en el formato necesarios, librándolo al mismo tiempo de tener que ingresar los separadores (”/” o “-“) de la fecha.

En esta nota mostraré como implementar un modelo sencillo: al elegir una celda determinada aparece el calendario y al elegir una fecha en él, ésta aparece en la celda y el calendario desaparece

Paso 1 – Crear un Userform

En el editor de VB (Alt+F11) creamos un userform que contendrá el control

Cambiamos la propiedad Name a “UfFecha” (no es indispensable pero es una buena práctica) y cambiamos Caption a “Elija una fecha”.

Paso 2 - Insertar el control Monthview

Apuntamos al cuadro ToolBox y abrimos el menú contextual con un clic en el botón derecho del mouse

Elegimos al opción Aditional Controls y marcamos el Microsoft Monthview Control

Este aparecerá ahora en la parte inferior del cuadro ToolBox. Con un clic lo seleccionamos y lo insertamos en el Userform

Paso 3 – Definiciones del control

Al seleccionar el control podemos ver el cuadro de propiedades en el editor. Esto nos permite definir su apariencia y comportamiento. Por ejemplo, definimos la propiedad ShowTodat como True, de manera que al abrirse el control muestre la fecha corriente.

Paso 4 – Códigos de los controles.

Ahora necesitamos escribir los códigos que manejen el uso del control. En nuestro modelo queremos que cuando el usuario seleccione la celda C3 (hemos creado un nombre definido que se refiere a la celda: “clFecha”) aparezca el calendario. Esto lo hacemos usando el evento Worksheet_SelectionChange de la hoja

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Union(Target, Range("clFecha")).Address = Range("clFecha").Address Then
        ufFecha.Show
    End If

End Sub

El código lo ponemos en el módulo de la hoja (abrimos el módulo de la hoja con la opción Ver Código del menú contextual que se abre al hacer clic derecho en la pestaña de la hoja o con un doble clic sobre el icono de la hoja en el editor de Vb)

Ahora necesitamos un código para pasar el valor elegido en el calendario a la celda

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
    ActiveCell.Value = DateClicked
    Unload ufFecha
End Sub

Este código va en el módulo del Userform que se abre haciendo un doble clic en el Userform (o apretando Ctrl+Tab hasta llegar al módulo)

Para que el control aparezca junto a la celda de la fecha y no en el centro de la hoja, agregamos el evento UserForm_Activate

Private Sub UserForm_Activate()
    With Me
        .Left = ActiveCell.Left + ActiveCell.Width + 25
        .Top = ActiveCell.Top + 150
    End With
End Sub

El modulo del Userform se verá así

Un tip para el final: si hacemos unn clic sobrel el nombre del mes en el control aparecerán unas flechas que nos permiten saltar de año en año

El archivo con el ejemplo puede descargarse aquí.

Usos del panel de selección en Excel

Una de las tareas más extenuantes cuando construimos reportes dinámicos o dashboards, es ordenar los objetos gráficos (cuadros de texto, formas, imágenes, gráficos, etc.).

Para ordenar los objetos debemos seleccionarlos, cosa que hasta Excel 2007 hacíamos seleccionando uno de los objetos y luego, apretando el botón Ctrl, seleccionando los restantes.

A partir de Excel 2007 disponemos de una nueva herramienta: el panel de selección

El panel aparece cuando seleccionamos un objeto, en la ficha “Herramientas de dibujo”, o en cuando seleccionamos un gráfico, en la ficha “Herramientas de gráficos”

El panel de selección tiene muchos usos prácticos

Volver visibles formas ocultas

En la imagen vemos que existe el objeto “Flecha izquierda y derecha” pero no es visible (el cuadro a la derecha del nombre del objeto en el panel indica si está visible, se ve un ojo en el cuadro, o no). Un simple clic en el cuadro al lado del nombre del objeto lo descubre o lo oculta



Uno de los usos de esta propiedad es hacer visible objetos que pueden contener enlaces a otros cuadernos o cambiar logos de facturas hechas en hojas de Excel.

Selección objetos

Podemos seleccionar los objetos en el panel haciendo un clic sobre el nombre del objeto elegido; podemos seleccionar varios objetos manteniendo apretada la tecla Ctrl mientras los seleccionamos. Una vez seleccionados podemos cambiar reordenarlos usando las flechas de reordenar.

Con los objetos seleccionados podemos hacer varias operaciones como:

Agrupar

Agrupando hacemos que varios objetos se comporten como si fueran un único objeto

Ajustar a la cuadrícula

Al activar esta propiedad, al mover o cambiar el tamaño de los objetos, éstos se alinean al borde de celda más cercano

Ajustar a la forma

En forma similar, esta propiedad permite alinear las formar a los bordes de las otras formas.

Otras posibilidades pueden verse son alinear en la parte superior o inferior y distribuir vertical u horizontalmente

En este ejemplo hemos agrupado un gráfico (ventas de dos años por meses) que incluye controles (la barra de desplazamiento y las casillas de verificación) lo que nos permite mover todo el grupo en la hoja o cambiar el tamaño sin necesidad de tener que tratar cada objeto por separado



El archive con el ejemplo se puede descargar aquí.