Agregar controles en una hoja de Excel usando Vba

lunes, noviembre 24, 2014

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





3 comments:

Mauricio Montaño 17 marzo, 2015 15:46  

Hola Jorge. Un gran saludo desde Colombia.

Te felicito por esta nota. Respecto de los controles, quiero preguntarte cómo hago para limpiar una serie de controles tipo Drop Down(lista desplegable, no activex) ya insertados en la hoja. El tema es que tengo una macro que copia una hoja ya diligenciada y borra cierto contenido, pero quiero que limpie el contenido de las listas.

Este es el código:

Sub nueva_hoja()
Application.ScreenUpdating = False
ActiveSheet.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Select
Range("D13") = Sheets.Count
ActiveSheet.Name = Range("D13")
Range("D15:H34").ClearContents
Application.ScreenUpdating = True
End Sub


De nuevo, muchas gracias por tu aporte tan importante.

Jorge Dunkelman 17 marzo, 2015 19:36  

Hola Mauricio,
los controles Formulario, a diferencia de los ActiveX, no pueden programarse. Por eso sólo podemos borrar el contenido de las celdas que contienen los valores de las lista desplegable (el "rango de entrada"). Al hacerlo, la lista desplegable se abre sin mostrar ningún valor.

Mauricio Montaño 17 marzo, 2015 19:41  

Jorge, muchas gracias. Entiendo tu idea y me parece genial.....

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP