59 - Userform 'flotante'

Siguiendo con el tema de los objetos o controles que se moverán acompañando a la celda o fila activa, hoy dejo un ejemplo con 2 hojas para mostrar el comportamiento de los Userforms.

Debemos recordar que tanto los objetos insertados desde la ficha Programador/Desarrollador (ActiveX o de Formularios) como así también los insertados desde menú Insertar-Ilustraciones (imágenes, autoformas, iconos) tienen dos propiedades que tendremos en cuenta: TOP, o sea el margen superior y LEFT que será el margen izquierdo.

Pero los Userforms se manejan diferente. Apelamos a la propiedad StartUpPosition que nos permite elegir entre 4 valores: 0 (Manual), 1 (Centrado en la ventana Excel), 2 (Centrado en pantalla) y 3 (predeterminado de Windows).

Para modificar estos valores y asignarle las propiedades Top y Left correspondientes a una celda, recurrimos a programación. En libro adjunto se encuentra en un módulo la macro principal, y en el formulario las que corresponden al evento Initialize del formulario.

En este evento realizaremos los ajustes a los cálculos devueltos por la macro principal. Esta macro coloca el objeto 'sobre' la celda activa. Podemos modificar esto y ajustar esta posición según el ancho de la columna activa.

Descargar el libro de ejemplo desde aquí.

Ver video N° 59 desde aquí.

Otros videos relacionados con este tema: N° 13 y N° 58.

58 - Objetos flotantes - Uso de ICONOS

 Ya hemos visto en videos anteriores, como en el N° 13, la posibilidad de mantener en la hoja controles de modo flotante. Es decir, que se irán moviendo a medida que avanzamos en las filas de una hoja.

Por ejemplo, si tenemos una tabla y deseamos ver el acumulado a medida que registramos filas, tendríamos un control dibujado desde la ficha Programador/Desarrollador, donde mostraremos la suma de los registros hasta ese momento. Esto será de utilidad considerando que las Tablas permiten agregar una fila de Totales pero al final de la misma.

La macro que utilizaremos se colocará en el Editor, en el objeto Hoja donde se encuentre la tabla. El evento a controlar será Worksheet_Change, o sea al cambio en la hoja.

Private Sub Worksheet_Change(ByVal Target As Range)

'x Elsamatilde

'se controla que la celda modificada se encuentre en col B, a partir de fila 3.

If Target.Column <> 2 Or Target.Row < 3 Or Target.Count > 1 Then Exit Sub

' se muestra en el label el total que se va acumulando

totx = Application.WorksheetFunction.Sum(Range("C3:C" & Target.Row))

Label1.Caption = Format(totx, "#,000.00")

'se ubica el control en la línea de la celda

ActiveSheet.Label1.Top = Range("B" & Target.Row).Top

End Sub

Hoy vamos a ver que también podemos utilizar 'OBJETOS' de modo 'flotante'.  Pueden ser imágenes, formas o ICONOS, que es la última novedad en las nuevas versiones Excel.

En el siguiente ejemplo, necesitamos mostrar diferentes iconos, según el valor ingresado en la col F con respecto a un valor de referencia que se encuentra en celda I2.

Primero insertaremos 3 objetos desde menú Insertar, Ilustraciones, Iconos. 

Estos objetos se llamarán 'Gráfico' más un índice y se activará un nuevo menú: Formato de gráfico. Podemos trabajarlos individualmente asignándole tamaño y color. 

También podemos 'Convertir a forma' el objeto seleccionado, activándose en ese caso el menú Formato de formas. Y trabajarlo por partes (modificar o quitar partes, con colores diferentes o no, etc).

Una vez terminada la tarea de formatear los objetos, tomaremos nota de sus nombres para ajustarlos en la siguiente macro. Como también se trata de controlar la modificación de una celda se colocará en el Editor, objeto Hoja donde se esté trabajando.

Aquí tendremos 2 eventos: 

   - Activate, para volver a colocar los objetos en fila 1 de un rango auxiliar.

   - WorkSheet_Change, o sea al cambio de una celda de la col F a partir de fila 3.

Dim grafico()       'declaración de una matriz que será utilizada en varios procesos.

 

Private Sub Worksheet_Activate()

'cada vez que se activa la hoja se muestran todos los iconos en fila 1 de un rango auxiliar.

grafico = Array("Graphic 5", "Group 15", "Graphic 4")

For i = 0 To 2

    With ActiveSheet.Shapes.Range(Array(grafico(i)))

        .Visible = True

       'se pueden ubicar todos encima de la celda L1

        '.Top = [L1]: .Left [L1] 

       'o se colocan a 12 columnas más allá del índice i, lo que resultará en col L, M y N

        .Top = Cells(1, i + 12).Top: .Left = Cells(1, i + 12).Left

    End With

Next i

End Sub

 

Private Sub Worksheet_Change(ByVal Target As Range)

'se controla el cambio en la col F a partir de fila 3

If Target.Column <> 6 Or Target.Row < 3 Then Exit Sub

'se guarda en una variable el resultado de comparar el valor ingresado con respecto al valor de referencia.

If Target.Value < [I2] Then x = 0

If Target.Value = [I2] Then x = 1

If Target.Value > [I2] Then x = 2

'llamada a una subrutina que será común a los 3 casos, indicando el nro de gráfico a mostrar y la celda donde ubicarlo.

Call mueveGraf(x, Target.Row)     

End Sub

 

Sub mueveGraf(x, filx)

    'se recorre la colección de los objetos de la hoja mostrando u ocultando según el valor de x

For i = 0 To 2

    If i = x Then

'si se trata del objeto que corresponde, se lo muestra

        With ActiveSheet.Shapes.Range(Array(grafico(i)))

            .Visible = True

            'y se lo coloca haciendo coincidir el tope y margen izquierdo con la celda que ha sido modificada.

            .Top = Cells(filx, 8).Top: .Left = Cells(filx, 8).Left

        End With

    Else

        'si no se trata del objeto correspondiente, se lo oculta

        ActiveSheet.Shapes.Range(Array(grafico(i))).Visible = False

    End If

Next i

End Sub

Ver video N° 58 desde aquí.

57 - ¡Fuera Ceros ! Cómo ocultar ceros en hojas Excel

Hay trabajos donde nos quedan mejor los informes o las planillas en general, mostrando las celdas vacías cuando tienen por resultado un cero. Esto puede resolverse de modo parcial o total y para ello veremos 4 escenarios a continuación.

Escenario N° 1: formular correctamente la función SI.ERROR

En la siguiente imagen se observa la función con el último argumento en 0. Esto no será correcto en casos de facturas, presupuestos o algún otro tipo de documento donde, al no encontrarse el producto, el valor a devolver debiera ser vacío en lugar de presentarlo con un precio de 0.

Otro ejemplo sería al mostrar una tabla de stock de productos, donde puede haber productos con stock = 0, pero en caso de no encontrarse algún item debiera mostrarlo vacío.

Por lo tanto, la función SI.ERROR debiera tener su último argumento con comillas (vacío) o con algún texto, pero no con ceros.

Escenario N° 2: quitar ceros solo a una tabla en una hoja donde se pueden encontrar otro tipo de tablas que no deseamos modificar.

En este caso utilizaremos una macro para limpiar solamente el rango de la tabla deseada. Desde el Editor de macros, insertaremos un módulo y allí copiaremos el siguiente código:

Sub quitaCeros()

Dim x As Integer

x = Range("T" & Rows.Count).End(xlUp).Row

Range("T3:AD" & x).Replace What:="0", Replacement:="", LookAt:=xlWhole, SearchOrder:=xlByRows ', _

    MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True

End Sub

Escenario N° 3: quitar ceros a toda la hoja activa. 

Esto se da generalmente en tablas diarias o mensuales, donde muchas celdas mantienen resultados en cero, impidiendo ver con claridad el resto de los valores, tal como se aprecia en la siguiente imagen:

La solución en este caso, es ir al menú Archivo, Opciones Excel, Avanzadas y quitar el tilde a la opción de 'Mostrar un cero......'

Escenario N° 4: como la solución anterior solo modifica el modo de tratar los ceros en la hoja activa, nos obligaría a ir hoja por hoja en caso de hojas mensuales o diarias. 

Entonces, para modificar todas o gran parte de las hojas del libro recurriremos nuevamente a una macro. En el Editor, en un módulo copiaremos el siguiente código, donde podemos agregar instrucciones que omitan los cambios en ciertas hojas:

Sub quitaCerosTotal()

'quita ceros en todo el libro

For Each sh In Sheets

    'omito alguna(s) hoja(s)

    If sh.Name <> "FILTRO" Then

        sh.Select

        ActiveWindow.DisplayZeros = False

    End If

Next sh

End Sub

Ver video N° 57 desde aquí.