Simular un CheckBox

Los controles CheckBox o casillas de verificación, cuando los dibujamos en la hoja, presentan un tamaño fijo y generalmente demasiado pequeños. 

Para no tener que acudir a alguna api podemos resolver el problema del tamaño con algunos trucos que simulen una casilla.

Ejemplo 1:

A una celda donde querramos presentar una casilla de verificación le vamos a asignar estos formatos: tamaño de fuente mayor, negrita y centrada en alto y ancho.

En el VIDEO pueden observar que a continuación inserté una forma (rectángulo) y la coloqué por encima de esa celda. Dejé el objeto con borde y sin relleno.  

En el Editor de macros insertamos un módulo y escribimos el siguiente código que corresponde a la celda B3:

          Sub marcando()

          If [B3] = "" Then

              [B3] = "X"

          Else

              [B3] = ""

          End If

          End Sub

Solo nos resta asignar esa macro al objeto dibujado en la hoja. Haremos clic derecho sobre el mismo, opción 'Asignar macro' y seleccionamos la llamada 'marcando'

Y así lo repetiremos para cada cuadro dibujado que simule una casilla. Cada uno tendrá su macro donde debe hacerse mención a la celda que se encuentra debajo de cada cuadro. 

NOTA: en el código se le asigna la letra X a la celda, pero cada usuario puede elegir qué texto mostrar.

Ejemplo 2:

El ejemplo anterior presenta el inconveniente de que se necesita un objeto o forma por cada casilla que necesitemos.

Entonces, otro truco es simular o formatear directamente la celda como una casilla o checkbox.

Seleccionamos todas las casillas necesarias y le damos el formato: negrita, centrada en alto y ancho, con borde.

La fuente elegida para simular el tilde es Webdings dándole un tamaño de 16 o mayor. La letra 'a' corresponde al tilde típico de un checkbox.

Y en este caso, la macro se coloca en el objeto HOJA donde estamos trabajando, en el evento Selection_Change:

          Private Sub Worksheet_SelectionChange(ByVal Target As Range)

         'se controla el rango H3:H11

          If Intersect(Target, Range("H3:H11")) Is Nothing Then Exit Sub

         'al seleccionar más de 1 celda no se ejecuta

          If Target.Count > 1 Then Exit Sub

          If Target.Value = "" Then

               Target.Value = "a"       'fuente webdings

          Else

              Target.Value = ""

          End If

          Target.Offset(0, 1).Select

          End Sub

Este modo nos sirve para gran cantidad de checkbox simulados, donde solo le indicaremos el rango donde se encuentran las celdas asignadas para esta tarea.

Descargar ejemplo desde aquí.

VIDEO N° 14.

Textbox (u otro control) flotante

Ya en una entrada anterior comenté cómo inmovilizar paneles para dejar rangos siempre visibles a medida que nos vamos moviendo por la hoja Excel.

El caso de hoy no tiene que ver con rangos o celdas sino con algunos controles, como puede ser un TextBox (o algún otro control) que necesitamos tenerlos visibles a medida que avanzamos por la hoja.

Ejemplo 1:
Veamos la siguiente imagen: a medida que vamos completando la columna se nos oculta el control donde ingresamos la información.

Una solución sería colocar el control por encima de una línea que inmovilice las primeras filas. En la siguiente imagen notamos que la fila 1 está inmovilizada y debiéramos colocar entonces allí el control.

Y la otra solución, que es el motivo de este tema, es que el control 'acompañe' el avance por las filas de la hoja. O sea que el control sea flotante.

Lo que haremos entonces es indicar que la propiedad Top del control es la misma que la de la celda que recibe el dato.

                 ActiveSheet.TextBox1.Top = Range("A" & x).Top

Nota: estos ejemplos presentan controles dibujados con la barra ActiveX.

Ejemplo 2:

En este segundo ejemplo contamos con 2 controles: un TextBox que nos permite ingresar la información y un Label que nos va mostrando la sumatoria del rango ocupado.

El código completo presenta estas instrucciones:

      Private Sub TextBox1_Change()

      'al limpiar el control no se ejecuta

      If TextBox1 = "" Then Exit Sub

     'al llegar a 11 caracteres vuelca el contenido a la hoja

      largo = Len(TextBox1.Text)

     If largo = 10 Then

         'busca la primer fila libre

          x = Range("A" & Rows.Count).End(xlUp).Row + 1

         Range("A" & x) = TextBox1.Text

        ' se acumula en el label el total

         totx = totx + Range("B" & x).Value

         Label1.Caption = Format(totx, "0.00")

        'limpia el control y permite una nueva entrada

        TextBox1 = "": TextBox1.Activate

        'ubicar el control en la línea de la celda

        ActiveSheet.TextBox1.Top = Range("A" & x).Top

        ActiveSheet.Label1.Top = Range("A" & x).Top

       'mover también el scroll

       ActiveWindow.SmallScroll Down:=1

    End If

    End Sub

Descargar el ejemplo desde aquí.

Ver VIDEO N° 13,

El control FRAME

Los Userforms nos permiten desarrollar aplicaciones evaluando contenidos (evitando o controlando posibles errores de tipeo o de ingreso de datos duplicados y tantos otros errores).
Además reduce el uso de fórmulas. O si las necesitamos, podemos reducir el tiempo de proceso programando que se pase el modo de cálculo a manual mientras ingresamos registros a una base y pasarlo a modo manual al finalizar con los registros.

En un Userform podemos utilizar varios tipos de controles: Label, TextBox, ComboBox y ListBox son los más frecuentes. Y son los mismos que podemos utilizar en una hoja Excel desde la barra de controles ActiveX.

Pero hay varios controles más: Frame, OptionButton, CheckBox, SpinButton y más.

En esta entrada vamos a ver un caso relacionado al control Frame.

Imaginemos que tenemos una cantidad de controles TextBox, CheckBox u otros que no necesariamente se llenarán todos sino que será opcional según algún otro criterio.
Entonces, podemos dibujarlos a todos dentro de un marco (Frame) e ir agrandando el frame a medida que necesitemos llenar más controles.

En las imágenes siguientes se observa el tamaño predeterminado y a continuación a medida que completamos los campos.

Esto requiere de una instrucción en el evento Enter de cada control para que a medida que lo utilizemos ya nos amplíe el marco para habilitar el siguiente control.

         Private Sub TextBox2_Enter()
           Frame3.Height = Frame3.Height + 20
           ' el tamaño que necesites para que se vea el otro box
          End Sub

          Private Sub TextBox3_Enter()
          Frame3.Height = Frame3.Height + 20
          End Sub

Pero como se puede observar en la segunda imagen, luego nos impide continuar con el resto del formulario. Por tal razón necesitamos otro código que nos vuelva nuevamente al tamaño original.
Ese código podría ser colocado al salir del último control:
         
           Private Sub TextBox5_AfterUpdate()
          'un modo: descontando la cantidad incrementada hasta llegar al último control
          Frame3.Height = Frame3.Height - 80
          'otro modo: dejando el marco del tamaño predeterminado
          If Frame3.Height > 54 Then Frame3.Height = 54
          End Sub

Ahora, programar este evento es válido si vamos a rellenar hasta el último control.

Sino debiéramos colocar un botón con la instrucción para reducir el Frame no importa cuántos controles tengamos desplegados.

            Private Sub CommandButton3_Click()
           If Frame3.Height > 54 Then Frame3.Height = 54
           End Sub

Ver VIDEO N° 12.

Cómo mejorar u optimizar nuestra programación.

Generalmente comento que un código, si resuelve la tarea que necesitamos, es correcto..... pero en realidad hay algunas situaciones en que no alcanza con que se resuelva la tarea, sino cómo se la resuelve.

Como gran cantidad de usuarios copian y pegan códigos encontrados en la web, veamos a continuación algunas situaciones donde es mejor desecharlo y buscar otras macros mejores.

1- Mirar la fecha de la publicación, del video o la respuesta en algún foro.
En ocasiones he recibido solicitud para ajustar alguna macro que dejé en alguna respuesta de años anteriores como, algunas del año 2004 !
Pero ya estamos en el 2018 y seguramente hoy habrá otro modo mejor de resolverlo.

La fecha nos da una idea de si se trata de códigos desarrollados para versiones xls.
Cuando Excel no contaba con alguna herramienta para resolver una situación recurríamos a una macro. Un caso concreto es el de 'eliminar duplicados' . Se necesitaban varias líneas de programación. Hoy encontramos esa herramienta en menú Datos.

Por lo que programar esta tarea ahora solo requiere de una instrucción, donde se indica el rango y la (o las) columnas a analizar:

          ActiveSheet.Range("$A$2:$F$5").RemoveDuplicates Columns:=1, Header:=xlYes

Como mucho le indicaré con una variable el fin de rango si se trata de una tabla de largo variable:

          Sub sinDuplica()
          x = Range("A" & Rows.Count).End(xlUp).Row
         ActiveSheet.Range("$A$2:$F$" & x).RemoveDuplicates Columns:=1, Header:=xlYes
         End Sub

2 - Observar cómo se busca el final de rango. 

Todavía se encuentran ejemplos donde para encontrar la primer fila libre o última celda ocupada se recurre a un bucle del tipo:

        fila = 2

          Do While Hoja5.Cells(fila, 1) <> ""

            fila = fila + 1

         Loop

Aquí se está recorriendo la col A desde la fila 2 controlando si la celda está vacía o no. Si consideramos que hoy una hoja Excel puede contener datos hasta fila más allá del millón, evidentemente no es el mejor modo de encontrar el fin de rango.... mejor desechar este código.

Algunas instrucciones posibles:

         Range("A" & Rows.Count).End(xlup).Row            'devolverá la última celda con texto en col A

           Range("B" & Rows.Count).End(xlup).Row + 1       'devolverá la primer celda vacía en col B

           Range("A2").CurrentRegion.Rows.Count               'devolverá la última fila del rango.

3 - Observar cómo se nombran las hojas.

Si toman parte de alguna aplicación (propia o ajena) pero la misma menciona a las hojas como Hoja1, Hoja3, etc presentará bastante dificultad la adaptación de esas macros a nuestro proyecto.

Por ejemplo, teníamos una aplicación con varias hojas y de allí tomamos algunas macros de este tipo:

Observamos que el código hace mención al indice de hojas (no a su nombre) y nuestro libro no tiene esa cantidad de hojas lo que lleva a realizar un seguimiento y muchas correcciones.

Para que un código pueda ser utilizado en varios proyectos lo conveniente es declarar las hojas en alguna variable. Así con solo modifcar esa instrucción ya nos servirá no importa como se llamen nuestras hojas en otros libros.

        Private Sub UserForm_Initialize()

          Set hop = Sheets("Productos")

          Set hoe = Sheets("Existencias")

          Set hom = Sheets("Movimientos")

          End Sub

Nota: Estas variables se declaran al inicio del Userform.

4 - No seleccionar-copiar..... seleccionar -pegar.

Cuando creamos una macro con la grabadora, ésta registra cada una de nuestras acciones: seleccionar una celda, copiarla, avanzar con el scroll o pasar a otra hoja, seleccionar la celda y pegar.

              Sheets("Compras").Select

               Range("R2").Select

               Range(Selection, Selection.End(xlDown)).Select

              Application.CutCopyMode = False

              Selection.Copy

              Sheets("Productos").Select

              Range("B2").Select

              ActiveSheet.Paste

Si esta tarea la tenemos que realizar con gran cantidad de información que iremos moviendo nos llevará muchas líneas innecesarias de código, pudiendo simplificarlo del siguiente modo:

          Range("R2:R" & Range("R" & Rows.Count).End(xlUp).Row).Copy

          Sheets("Productos").[B2].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

                   :=False, Transpose:=False

5 - Que las macros vengan con explicaciones. 

Un código explicado siempre será más fácil de ajustar y adaptar a nuestro requerimiento. 

Ver VIDEO N° 11,