69- Control WebBrowser para mostrar documentos o páginas Web

 El control WebBrowser nos permite mostrar en una hoja o en un formulario Userform, el contenido de documentos, páginas Web, como así también gifs o imágenes animadas.

En video N° 20 ya vimos cómo trabajar con Userforms que presentaban una tarjeta animada, con referencia a las fiestas de fin de año.

Y en video N° 40, vimos otros ejemplos donde colocamos tarjetas animadas ya sea en la apertura del libro o al activar una hoja de inicio.

En esta entrada veremos 2 usos más.

1 - Mostrar un documento (Pdf, Doc u otro tipo de archivo)

Este ejercicio continúa al tema anterior donde mostrábamos una lista de documentos según la carpeta y subcarpeta que elegimos desde un par de desplegables. 

Al seleccionar un elemento de la lista, se nos mostrará el contenido de ese PDF. Con todas las herramientas propias de Adobe que nos permite guardar, imprimir o cambiar de tamaño al PDF.

El libro, que se puede descargar desde el enlace al pie, presenta toda la programación ya vista en tema anterior. Aquí solamente agregaré las instrucciones correspondientes al evento Click del control ListBox.

Private Sub ListBox1_Click()

If ListBox1.ListIndex < 0 Then Exit Sub

Dim rutaPDF As String, archivo As String, ext As String

'la ruta del pdf es la del libro activo + carpeta+subcarpeta = Dire

    rutaPDF = Dire & "\"

'nombre del Pdf

    archivo = ListBox1.List(ListBox1.ListIndex, 0)

'extensión

    'ext = ".pdf"

WebBrowser1.Navigate rutaPDF & archivo    'mis archivos incluyen la extensión

End Sub

Previamente, habrá que agregar una instrucción al inicio del módulo Userform1, para declarar la variable Dire que es una variable compartida con varios de los procesos.

Dim Dire As String

2 - Mostrar una página Web dentro de un control WebBrowser.

En este ejercicio, al activar el Userform ya se mostrará el contenido de alguna página Web. De allí podremos tomar información, ya sea números, textos, completar tablas, etc.

Private Sub UserForm_Activate()    'ruta a la pág de Wikipedia, Población mundial.

Dim rutaWeb As String

rutaWeb = "https://es.wikipedia.org/wiki/Poblaci%C3%B3n_mundial"

WebBrowser1.Navigate rutaWeb

End Sub

Importante: si la página elegida presenta mucha publicidad nos aparecerán mensajes de aceptar o cancelar los Scrip, lo que puede hacer muy poco práctico el uso de este método para capturar información de allí

Descargar libro de ejemplo desde aquí.

Ver video N° 69 desde aquí.

68 - Listar Carpetas, Subcarpetas y Archivos.

 Cuando necesitamos trabajar con directorios que pueden ser ubicados (en paquete) en otra ubicación, nos será útil contar con un programita que utilice el objeto FileSystemObject. 

Veremos el uso del método GetFolder para obtener el contenido de una carpeta, las propiedades SubFolder y Files para hacer referencia a la colección de subcarpetas y archivos respectivamente.

En el ejemplo, contaré con un control desplegable (ComboBox1) para presentar las primeras carpetas a recorrer. Asumo que estarán en la misma ubicación del libro activo.

Luego tendremos un segundo control desplegable (ComboBox2)  que nos mostrará las subcarpetas de la carpeta seleccionada en el primer control.

Y así podemos seguir con otras ubicaciones hasta encontrar la carpeta que contenga los archivos que buscamos. En este caso, la selección del segundo control ya nos devolverá en una lista (ListBox1) el total de archivos allí encontrados.

A continuación solo resta desarrollar la acción que realizaremos sobre esa lista. Imprimir todos, seleccionar 2 o más archivos para eliminarlos, moverlos a otra ubicación, etc.

Los códigos desarrollados en el libro que se adjunta en esta entrada (ver descarga al pie) son los siguientes.

Dim ruta As String     'ruta de las carpetas Entradas y Salidas

 

Private Sub UserForm_Initialize()

ComboBox1.AddItem "ENTRADAS"

ComboBox1.AddItem "SALIDAS"

End Sub

 

Private Sub ComboBox1_Change()      'listar subcarpetas en el 2do combobox

Dim fs As Object, carpeta As Object, subcarpeta As Object

 

'la ruta de la carpeta principal es la del libro activo

    ruta = ThisWorkbook.Path & "\" & ComboBox1.Text

'contempla posible error de ruta no hallada

    On Error GoTo sinRuta

'se crea la referencia al objeto Filesystem

    Set fs = CreateObject("Scripting.FileSystemObject")

    Set carpeta = fs.GetFolder(ruta)

'se agregan las subcarpetas al 2do combobox

ComboBox2.Clear

    For Each subcarpeta In carpeta.SubFolders

        ComboBox2.AddItem subcarpeta.Name

    Next

Exit Sub

 

sinRuta:

    MsgBox "No se encontraron carpetas en la ruta indicada."

End Sub

 

Private Sub ComboBox2_Change()     'listar los archivos de la subcarpeta seleccionada

Dim Archi                   'guarda el nombre de cada archivo encontrado

Dim Dire  As String         'guarda el directorio a revisar

'la ruta de la subcarpeta

    Dire = ruta & "\" & ComboBox2.Text

 

'con el objeto Filesystem y con los objetos encontrados en la subcarpeta

On Error GoTo sinRuta

With CreateObject("Scripting.FileSystemObject")

    With .GetFolder(Dire)

    'se recorre el conjunto de archivos encontrados

    ListBox1.Clear

    For Each Archi In .Files

        ListBox1.AddItem Archi.Name

    Next

    End With

End With

Exit Sub

 

sinRuta:

    MsgBox "No se encontró la ruta de la subcarpeta."

End Sub

 

Private Sub ListBox1_Click()

If ListBox1.ListIndex < 0 Then Exit Sub

MsgBox "Has seleccionado el archivo " & ListBox1.List(ListBox1.ListIndex)

End Sub

 

Private Sub CommandButton1_Click()   'botón para limpiar controles y volver a empezar

ComboBox1.ListIndex = -1: ComboBox1.SetFocus

ListBox1.Clear: ComboBox2.Clear

End Sub

Descargar libro de ejemplo desde aquí.

Ver video N° 68 desde aquí.

67 - Uso de INPUTBOX para seleccionar un rango

 En la mayoría de las macros, trabajamos con un rango ya previamente seleccionado o  armamos la referencia al rango como una combinación de fila y columna. 

Por ejemplo: dire = Range(Cells(1,1), Cells(20,colx))

A continuación veremos el uso de la función INPUTBOX que nos permitirá seleccionar un rango para continuar con un proceso ya iniciado.

Para este ejemplo tendremos:

1 hoja principal y 1 hoja que se agrega para recibir la copia del rango seleccionado:

Se filtra la columna 2 (Fecha) de la tabla principal, por el año que se obtiene de la celda E9.

En la macro del ejemplo, que colocamos en el Editor de Macros, insertando un módulo, tenemos:

Sub procesoContinuo()

'hoja activa.

    Set hojaTabla = ActiveSheet

'crear una hoja nueva

    Set nvaHoja = Sheets.Add

'volver a la hoja principal

    hojaTabla.Activate

'en este ejemplo se filtra la tabla x algún criterio

    crit = "12/27/" & Year(Range("E9"))

    ActiveSheet.ListObjects("PaymentSchedule3").Range.AutoFilter Field:=2, _

        Operator:=xlFilterValues, Criteria2:=Array(0, crit)

'se selecciona un rango de la tabla filtrada

On Error Resume Next

Set rgox = Application.InputBox("Seleccione una celda o rango", Type:=8)

'si el rango no está vacío lo copiamos en la nueva hoja

If Not IsEmpty(rgox) Then

    rgox.Copy

    nvaHoja.Activate

'se pegan solo valores y formatos de número

    Range("B3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _

        xlNone, SkipBlanks:=False, Transpose:=False

    Application.CutCopyMode = False

    MsgBox "El rango ya fue copiado. El proceso continúa....", , "Información"

End If

'otras instrucciones. Por ej:

'1- volver a hoja principal y quitar los filtros,

    hojaTabla.Activate

    ActiveSheet.ListObjects("PaymentSchedule3").Range.AutoFilter Field:=2

'2- renombrar la hoja creada,

 '----------

'3- dar formato al rango copiado, etc

 '--------

End Sub

 

NOTA: observar que se pueden seleccionar tanto rangos continuos como discontinuos, desde la ventana del InputBox. 

Descargar libro de ejemplo desde aquí.

Ver Video N° 67 con el desarrollo de 2 ejemplos: macro con selección de rango previo y macro con selección de rango durante un proceso.

66 - Nombrando hojas Excel desde VBA.

 Todos los usuarios de Excel seguramente renombran las pestañas que vienen de modo predeterminado al abrir un libro (Hoja1, Hoja2, etc) a su gusto y necesidad.  Así, encontramos nombres como 'Inicio', "Enero', 'Clientes', etc. Esos son los nombres de las 'pestañas'.

¿Pero cómo identificarlas correctamente desde algún código, macro o formulario?

Veremos a continuación las diferentes modos de llamarlas según la tarea que vayamos a realizar.

Modo N° 1:  Por su nombre de pestaña, entre comillas. Algunos ejemplos:

Sheets("PROVEEDORES").Select

Sheets("PROVEEDORES").Unprotect

Sheets("PROVEEDORES").Range(“A:B”).Copy

Selection.Copy Destination:= Sheets("PROVEEDORES").[C5]

Modo N° 2:  Por su código de nombre. Es el texto que antecede al nombre en la lista de hojas del libro. Así por ejemplo Hoja3 será la hoja Presupuesto.

Hoja3.Select

Hoja3.Unprotect

Hoja3.Range("A:B").Copy

Selection.Copy Destination:=Hoja3.[C5]

NOTA: Este método requiere que recordemos a qué hoja corresponde el código Hoja3. Lo que es una dificultad al copiar códigos con esta expresión, ya que podemos no tener los mismos textos.

Modo N° 3: Al inicio de un módulo, se declara una variable del tipo 'hoja' para guardar el nombre. En el resto del módulo se hará mención a la variable en lugar del nombre o código de nombre.

Dim hop As Worksheet      'tipo de variable hoja

Set hop = Sheets("PROVEEDORES")

    hop.Select

    hop.Unprotect

    hop.Range("A:B").Copy

    Selection.Copy Destination:=hop.[C5]

NOTA: Este método es muy práctico a la hora de trabajar con Userforms, donde asignamos la hoja a la variable desde el evento Initialize del Userform. Y en todos los procesos se hará mención a esa variable. Si por alguna razón, más adelante cambiamos de nombre a nuestra hoja, solo habrá que modificar esa línea inicial.

Modo N°4: Llamar a la hoja cuyo nombre se guardó en una variable del tipo String o texto.

Dim hojax As String      'tipo de variable texto

'nombre contenido de una celda de hoja LISTAS:

hojax = Sheets("LISTAS").Range("B1")

'o también obtenido con un InputBox

hojax = InputBox("Ingrese el nombre de la hoja a utilizar.")

    Sheets(hojax).Select

    Sheets(hojax).Unprotect

    Sheets(hojax).Range("A:B").Copy

    Selection.Copy Destination:=Sheets(hojax).[C5]

NOTA: Observar que en este caso, se llama a la hoja con Sheets(variable), sin uso de comillas.

Modo N° 5: Llamar a la hoja haciendo referencia a su índice o ubicación entre las pestañas.

Sheets(1).Select        ‘primera pestaña

Sheets(Sheets.Count).Select    ‘última pestaña. Sheets.Count nos devolverá el total de hojas.

A continuación, algunas instrucciones para obtener información de la hoja activa:

nombre = ActiveSheet.Name

codi = ActiveSheet.CodeName

indi = ActiveSheet.Index

Ver video N° 66 desde aquí.

65 - Protección de Hojas

 Los libros Excel cuentan con varios métodos de protección: 

- Con contraseña de apertura 

- Con contraseña de escritura

Estas dos opciones se pueden establecer al momento del Guardado, botón Herramientas, Opciones Generales. Se pueden establecer las dos contraseñas, una o ninguna.

Una vez abierto el libro podemos encontrar en el menú Revisar, grupo Proteger, las siguientes opciones:

- Protección de Hojas: Con contraseña o no y con diferentes permisos. La protección es por cada hoja, de modo individual.

- Protección de Libro: esta opción permite impedir el cambio en la estructura del libro: agregar o quitar hojas, ocultarlas y mostrarlas, cambiar de nombre o de ubicación. La contraseña es opcional.

Al presionar el botón Protección de libro, nos encontraremos con una ventana conteniendo varias opciones que podemos tildar o no. El tildado significa que será posible realizar esa tarea en una hoja protegida. Según la imagen solo se podrán seleccionar las celdas desbloqueadas.

Para trabajar de modo correcto con una hoja alimentada desde un Userform, debemos establecer cuáles serán las celdas bloqueadas y cuáles no. Y recién entonces proceder a la protección de la hoja.

Como la hoja Excel presenta de modo predeterminado todas las celdas bloqueadas, podemos cambiar este estado desde menú Inicio, Formato, Formato de celdas, Proteger y quitar el tilde a la opción de bloqueo.

Por ejemplo, encabezados, títulos y fórmulas debieran estar bloqueadas (menú Inicio, Formato, Formato de Celdas, Proteger y tildar la opción de Bloqueo. En el caso de fórmulas, también podemos tildar la opción 'Ocultar' para que no se muestren en la barra de fórmulas.)

El resto de la hoja, donde irán los datos debemos dejarlas desbloqueadas. Salvo que necesitemos impedir que se hagan cambios una vez completado el registro.

Si la hoja presenta Botones u objetos como imágenes, la opción 'Modificar Objetos' debiera quedar sin tilde para evitar el cambio accidental de estos objetos.

Si vamos a permitir (o se tildar) la opción de Autofiltro, debemos ya tenerlo aplicado en el rango de títulos antes de la protección.

Para desproteger/proteger una hoja mediante VBA, utilizaremos los métodos Unprotect y Protect.

Ejemplo 1:

Sub quitaProteccion()    

 'en este ejemplo se desprotege la hoja activa (no tiene clave) y se la protege con clave.

ActiveSheet.Unprotect

ActiveSheet.Protect "12345"

End Sub

Ejemplo 2:  Solo se seleccionarán las celdas desbloqueadas.

 Sub SeleccionaDesbloqueadas() 

ActiveSheet.Protect "12345", DrawingObjects:=True, Contents:=True, Scenarios:=True

'impide la selección de celdas bloqueadas

ActiveSheet.EnableSelection = xlUnlockedCells 

End Sub

Ejemplo 3:  Con permisos para formato de columnas y filas, Orden y Autofiltro.

Sub Permisos()

ActiveSheet.Protect "12345", DrawingObjects:=True, Contents:=True, Scenarios:=False _

AllowFormattingColumns:=True, AllowFormattingRows:=True, _

        AllowSorting:=True, AllowFiltering:=True

End Sub

Ejemplo 4:  En una hoja totalmente bloqueada, la macro desbloquea aquellas celdas sin color o con un color determinado.

Sub desbloqueaBlancas()

'La hoja se encuentra bloqueada en su totalidad.

'la macro permite desbloquear aquellas celdas sin color para el ingreso de datos.

'el rango a recorrer es de fila 1 a 36 y columna 2 hasta 30 (B1:AD36)

Dim i As Integer, j As Integer

On Error Resume Next

For i = 1 To 36

    For j = 2 To 30

        If Cells(i, j).Interior.Color = 16777215 Then

            Cells(i, j).Locked = False

        Else

            Cells(i, j).Locked = True

        End If

    Next j

Next i

MsgBox "Fin del proceso."

End Sub

Ver video N° 65 - Protección de hojas desde aquí.

Ver video N° 37 - Protección de libro y hojas Excel. 

Ver lista completa de videos desde aquí.

63 - MSGBOX limitado?

Los MSGBOX son cuadros de mensaje que nos permiten mostrar un mensaje y un par de botones para tomar alguna decisión al respecto.

Por ejemplo:

Pero ante la imposibilidad de mostrar más botones, o de personalizar los textos de los mismos, utilizaremos un pequeño objeto del tipo Userform para simular justamente el uso de un MsgBox.

En este modelo de ejemplo, colocamos un control Label donde mostramos el mensaje. Y cuatro controles del tipo CommandButton para ejecutar las distintas opciones. Cada botón dentro del formulario tendrá una instrucción para llamar a su proceso.

Private Sub CommandButton1_Click()

'MsgBox "A continuación se procede a GUARDAR la factura.", , "Información"

Call GuardaFact

End Sub

 

Private Sub CommandButton2_Click()

Call ImprimeFact

End Sub

Private Sub CommandButton3_Click()

Call CopiaFact

End Sub

 

Private Sub CommandButton4_Click()

Unload Me

End Sub 

Y los diferentes procesos los dejaremos en un módulo, junto con las instrucciones para llamar al Userform:

Sub llamaUF()

UserForm1.Show

End Sub

 

Sub GuardaFact()

'.... código que guarda la factura

End Sub

 

Sub ImprimeFact()

'.... código que imprime la factura

End Sub

 

Sub CopiaFact()

'.... código que guarda una copia de la factura 

End Sub

Ver video N° 63 desde aquí.

61 - ComboBox en una Ribbon o Cinta de Excel

 En los videos N° 4 y 5 hablamos del uso de botones y menú desplegable dentro de una cinta o Ribbon.

Y en el video N° 52 le incorporamos un control del tipo TextBox.

En estos videos anteriores, hablamos sobre los diferentes argumentos: Label, Image, Screentip y otros como 'OnAction' para llamar a las macros de los botones y 'OnChange' para llamar a la macro en caso de un control TextBox.

Hoy vamos a agregar otro control a la cinta. Se trata de un ComboBox. Nos servirá para mostrar una cantidad considerable de elementos, en lugar de llenar la cinta de botones. 

Por ej, lista de Proveedores, Clientes, Usuarios, hojas de un libro para llegar hasta ellas sin necesidad de tener un botón de avance y otro para el regreso al menú principal.

Aquí, además de los argumentos propios a todos los botones de la cinta, veremos otros 3 argumentos que se necesitan programar:

- getItemCount:  para obtener el número total de elementos a mostrar en el control desplegable.

- getItemID:  el número del elemento seleccionado.

- getItemText:  el texto del elemento seleccionado.

En el libro desarrollado según la siguiente imagen, donde necesitamos ir a la hoja seleccionada,  no nos hace falta el 2do argumento: getItemID. Queda explicado para otros casos, como por ejemplo, evaluar qué ubicación ocupa el elemento seleccionado y según eso, seguir un procedimiento.

El código completo que colocamos en el editor OfficeRibbonx para un control del tipo ComboBox, se encuentra en el libro a descargar (ver enlace al pie). Allí también se encuentran las siguientes macros:

- Las macros al inicio del módulo donde guardaremos las subrutinas para cada botón de la cinta:

Option Explicit

Public Cinta As IRibbonUI

Sub CargarCinta(CintaDeExcel As IRibbonUI)

    Set Cinta = CintaDeExcel

End Sub

- Las macros del control ComboBox, según sus 3 argumentos declarados en el Editor de la Ribbon:

Sub irOtrosMeses(control As IRibbonControl, TextoSeleccionado As String)

    On Error GoTo sinHoja

    Sheets(TextoSeleccionado).Select

    [B7].Select

    Exit Sub

sinHoja:

    MsgBox "No se encuentra la hoja seleccionada.", , "Atención"

End Sub

'cuenta la cantidad de elementos que tendrá el control.

Sub ObtenerNumerodeMeses(control As IRibbonControl, ByRef NumerodeOpciones)

Dim x As Integer

x = Sheets("Listas").Range("C" & Rows.Count).End(xlUp).Row

    NumerodeOpciones = Application.WorksheetFunction.CountA(Sheets("LISTAS").Range("C1:C" & x))

End Sub

'guarda el nro de elemento que se ha seleccionado

Sub ObtenerIdMes(control As IRibbonControl, NumeroOpcion As Integer, ByRef nroElemento)

    nroElemento = Sheets("LISTAS").Cells(NumeroOpcion + 1, 3)

End Sub

'guarda el texto del elemento seleccionado

Sub ObtenerTextoMes(control As IRibbonControl, NumeroOpcion As Integer, ByRef TextoMes)

    TextoMes = Sheets("LISTAS").Cells(NumeroOpcion + 1, 3)

End Sub

Descargar libro de ejemplo desde aquí.

Ver video N° 61 desde aquí.

60 - Combinar filas en un rango. Descombinarlas.

Combinar celdas es una de las tareas frecuentes en Excel. Ya sea que realicemos algún formulario o formato donde se deben ubicar diferentes campos en diferentes posiciones dentro del formato.

En el ejemplo, se trata de combinar solo las celdas que abarcan las col D:F de cierto formulario para el registro de Nombres y Apellidos.

La herramienta que utilizamos manualmente sería la de Combinar Celdas del menú Inicio, Alineación.

Con VBA, el método Merge es el que nos combina un rango establecido y Unmerge para descombinarlo.

A continuación diferentes macros para realizar esta tarea, según condiciones:

Ejemplo 1:  combinar celdas de una sola fila.

Sub combina()

[D6:F6].Merge

End Sub

Ejemplo 2: combinar todas las celdas de un rango en una sola. Se conservará solo el texto de la primera celda del rango seleccionado.

Sub combina_1()

Range("D6:F14").Merge

End Sub

Ejemplo 3: dentro de un rango, combinar las celdas de cada fila en un rango independiente. Colocando la expresión Merge en True.

Sub combina_2()

[D6:F14].Merge (True) 

End Sub     

Nota: Podemos mejorar el código centrando cada fila combinada, de este modo:

Sub combina_Filas()

With [D6:F14]

    .Merge (True)                  'combina cada fila del rango en una sola celda.

    .HorizontalAlignment = xlCenter

End With

End Sub

Para descombinarlas, utilizaremos el método UnMerge. La instrucción será la misma para todas las situaciones, indicando el rango a descombinar.

Ejemplo 4:

                        Sub descombina()

                        [D6:F14].UnMerge

                        End Sub

Descargar ejemplo desde aquí.

Ver video N° 60 desde aquí.

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í.