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()
