52 - Uso de cuadros o controles dentro de una RIBBON

En las entradas de Marzo 2018 (VIDEOS 4 y 5) del canal, vimos cómo diseñar y programar una cinta o Ribbon personalizada. 

Ya sea con iconos de Office o propios, con menú de Excel o solo la barra, con botones 'privados' o no ..... y así diferentes modelos de cinta.

Ahora veremos que también podemos agregar controles del tipo TextBox o Combobox en alguna opción de la cinta.

Para los que requieran de una versión actualizada del complemento, según las últimas versiones de Office, deberán descargar el OfficeRibbonXEditor.

Tomando el libro de ejemplo (ya con los botones diseñados en videos anteriores) vamos a colocar a continuación del último grupo (N° 6) otro nuevo para incluir un TextBox que nos permita ingresar el nombre con el que se deberá guardar el PDF de la hoja activa. 

NOTA: En el grupo 'Gestión de guardado' ya teníamos un botón que realizaba esa tarea, donde se solicitaba mediante un InputBox el nombre con el que debiera ser guardado el PDF. Ahora vemos que podemos omitir ese botón y solicitar el dato desde el nuevo control.

El código para el nuevo grupo quedaría entonces así. 

<group id="Group7" label="Guarda Pdf"  >

   <editBox id= "miTxt" label="Ingresar texto" screentip= "Corresponde al nombre con el que se guardará la hoja activa en PDF." onChange ="Boton17"/>

</group>

El control utilizado para el texto se llama 'editBox' y el argumento que llama a la macro es onChange a diferencia de los otros botones en los que se utiliza onAction.

En el libro, entre las macros de llamada a los controles de la Ribbon, escribiremos el código. 

Recordar de llamar a la subrutina con el mismo nombre que colocamos en el argumento onChange. Y agregar el argumento que será el texto que ingresamos en el control. El nombre no necesita ser el mismo que el utilizado en el código de la Ribbon.

Sub Boton17(control As IRibbonControl, nbreArchi)

        If nbreArchi <> "" Then Call conPdf(nbreArchi)

End Sub

A continuación la subrutina que puede estar ubicada en otro módulo:

        ^{Sub conPDF(archi)}

                 ^{ruta = ThisWorkbook.Path}

                ^{ActiveSheet.Copy}

                ^{With ActiveWorkbook}

                    ^{.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ruta & "\" & archi & ".pdf"   ', _}

                ^{End With}

^{End Sub}

Si en lugar de un PDF, se quisiera guardar la hoja activa como hoja de Excel, en lugar de 'exportar' haríamos un 'guardar como' con la siguiente instrucción (gracias al aporte del usuario Francisto Torres ;)

Sub conXlsx(nbre)      'ajustar el nombre de la macro en el botón 7.

    ruta = ThisWorkbook.Path

    ActiveSheet.Copy

    With ActiveWorkbook

        .SaveAs Filename:=ruta & "\" & nbre & ".xlsx", FileFormat:=xlOpenXMLWorkbook

        .Close savechanges:=False

    End With

End Sub

Acceso al VIDEO N° 52 desde aquí.

51 - Tu segunda aplicación: Votación y/o Encuestas con imágenes en un Userform

Continuando con el tema de las Imágenes, en esta oportunidad se realizó una pequeña aplicación apta para uso de Encuestas como así también de Votación.

En el Userform haremos uso de controles IMAGE asignándoles la propiedad Picture tanto desde la lista de propiedades como desde programación, tomandolas desde el contenido de celdas.

El desarrollo completo se puede descargar desde la sección DESCARGAS de mi sitio web.

Acceso al VIDEO Nº 51 desde aquí. 

50 - Documento Excel con imágenes incorporadas en el libro.

En la entrada anterior insertábamos imágenes directamente a las celdas desde una carpeta auxiliar. Esta opción es muy útil si vamos a incorporar imágenes de alta calidad y así no incrementamos el peso del libro. 

Pero el inconveniente que presenta este modelo es que si deseamos enviar el libro a terceros, también tenemos que adjuntar la carpeta con las imágenes. 

En cambio, en esta entrada veremos otro modelo que nos permite insertar las imágenes en el mismo libro, asignándoles un nombre apropiado para luego relacionarlas con los registros a utilizar.

Para esto, utilicé como ejemplo un modelo de factura, una tabla de productos y una hoja con las imágenes de los productos.

Nuestra base de productos tendrá una columna de 'Códigos' que son los que se presentan en lista desplegable en col B. Y al seleccionar un código, se completará el resto de las columnas con información (detalle e importe en este ejemplo) obtenida mediante fórmulas. Para el detalle y considerando que la hoja Base tiene una tabla sería:

SI.ERROR(BUSCARV(B11;Tabla1[[#Todo];[CODIGO]:[PRECIO]];2;FALSO);"")

Además se ejecuta una macro en el evento Change de esta hoja, que será la que ubique la imagen relacionada con ese código.

Entonces, primeramente en una hoja (a la que llamé imgProd), desde menú Insertar, Ilustraciones vamos a seleccionar cada una de nuestras imágenes de productos. Y estando seleccionada, desde la barra de nombres escribiremos el código que le corresponde.

No nos vamos a preocupar ni del tamaño ni de su propiedad de NO mantener proporción porque esto lo haremos desde la misma macro.

Una vez lista esta tarea, entraremos al Editor de macros y seleccionaremos la hoja, en este caso 'Factura'. Y allí pegaremos el siguiente código:

Private Sub Worksheet_Change(ByVal Target As Range)

'----- IMPORTANTE: ESTE CÓDIGO se utilizará con imágenes previamente insertadas en la hoja imgProd

'y se le asigna el nombre correspondiente al producto.

'Si la factura solo se utilizará para impresión (Pdf o impresora) no es necesario subirlas al

'libro y en ese caso utilizar el código colocado en hoja Img9 (2). Recordar que al enviar

'copia de la factura por mail o whatsapp las imágenes no se verán salvo que también se adjunte

'la carpeta que las contenga.

'-------------------------------------------------

'por cambio en celdas B11:B14 se busca la imagen que coincida con el valor seleccionado

If Intersect(Range("B11:B14"), Target) Is Nothing Then Exit Sub

If Target.Count > 1 Then Exit Sub

'se quita la imagen anterior

For Each sh In ActiveSheet.Shapes

    If sh.Type = 11 Or sh.Type = 13 Then

        If sh.Top = Target.Top Then sh.Delete: Exit For

    End If

Next sh

If Target.Value = "" Then Exit Sub

foto = Target.Value

Application.ScreenUpdating = False

'se controla posible error por no encontrar la imagen

On Error Resume Next

Sheets("imgProd").Select

ActiveSheet.Shapes.Range(Array(foto)).Select

    Selection.Copy

    Sheets("Factura").Select

    Range("F" & Target.Row).Select

    ActiveSheet.Paste

'estando la imagen seleccionada se ajusta su dimensión a la celda de la misma fila

Selection.ShapeRange.LockAspectRatio = msoFalse     'NO mantiene proporción

With Selection

    .Top = Target.Top

    .Left = Range("F" & Target.Row).Left + 1

    .Height = Range("F" & Target.Row).Height

    .Width = Range("F" & Target.Row).Width

End With

'pasa a fila siguiente

Target.Offset(1, 0).Select

End Sub

Acceso al VIDEO Nº 50 con el paso a paso desde aquí.

Descargar libro de ejemplo desde aquí.

49 - Catálogo en Excel, con imágenes externas.

 En esta entrada veremos una manera de crear un CATÁLOGO con imágenes en Excel.

Vamos a crear en primer lugar, una tabla de productos donde guardaremos algunos campos como Código, Descripción, Precio, y otros.

Si el código del producto coincide con el nombre de la imagen, no hará falta agregar nada más. Y las instrucciones para insertar la imagen serán las siguientes:

'el nombre de la imagen se toma de la col D (código)

 archi = ruta & Cells(mifila, 4) & ".jpg"

ActiveSheet.Pictures.Insert(archi).Select 

En cambio si los nombres de los archivos de imagen no coinciden con el código, tendremos que agregar otra columna para guardar ese nombre, con su extensión o no.

    'o el nbre se lo toma de la col H que ya tiene la extensión

    archi = ruta & Cells(mifila, 8)

   ActiveSheet.Pictures.Insert(archi).Select

NOTA: si en la tabla de Productos, los nombres de archivo no contienen la extensión, debe ser considerada en el código. Por ejemplo:

                    archi = ruta & Cells(mifila, 4) & ".jpg"

La ruta de la carpeta que contiene los archivos de imagen puede quedar establecida en el código. 

Por ejemplo:

ruta = ThisWorkbook.Path & "/imagenes/"

o ser tomada desde alguna celda, por ejemplo M2 de la hoja activa:

                    ruta = Range("M2")

El proceso puede ejecutarse desde un botón de formulario o con un atajo de teclado. En ambos casos desde el Editor se insertará un módulo y allí se copiará la siguiente macro, haciendo los ajustes mencionados anteriormente:

 

Sub buscaimagen()    

    'esta macro se ejecuta teniendo de antemano la ruta de la carpeta 

    'que contiene las imágenes

'se borran imágenes anteriores

On Error Resume Next

For Each sh In ActiveSheet.Shapes

    If Left(sh.Name, 7) = "Picture" Then sh.Delete

Next sh

ruta = ThisWorkbook.Path & "/imagenes/"       'ruta preestablecida

'ruta = Range("M2")                                 ' o contenido de una celda

'fila de inicio

mifila = 7

'recorre la col que tiene las direcciones de los archivos y los ubica

While Cells(mifila, 4) <> ""

    'el nombre de la imagen se toma de la col D (código)

    'archi = ruta & Cells(mifila, 4) & ".jpg"

    'o el nbre se lo toma de la col H que ya tiene la extensión

    archi = ruta & Cells(mifila, 8)

    'evaluar si existe el archivo en carpeta, sino continuar con el resto

    If Dir(archi) = "" Then GoTo sigo

    ActiveSheet.Pictures.Insert(archi).Select

    'las dimensiones son las de la celda en fila activa y col F

    'se incrementa el margen en 1 y se disminuye la dimensión en 1 para mostrar el borde.

    With Selection.ShapeRange

        .Left = Cells(mifila, 6).Left + 1

        .Top = Cells(mifila, 6).Top + 1

        .LockAspectRatio = msoFalse             'NO mantiene proporción

        .Width = Cells(mifila, 6).Width - 1

        .Height = Cells(mifila, 6).Height - 1

    End With

sigo:

    'pasa a fila siguiente

    mifila = mifila + 1

Wend

'fin del proceso. se posiciona en alguna celda de encabezado.

[E6].Select

Exit Sub

 

sinImagen:

MsgBox "Se presentó un problema con la ruta o nombre de archivo", , "ERROR"

End Sub

ATENCIÓN: a la macro Sub buscaImagen( ) que se encuentra en el libro, le faltan las instrucciones marcadas de color.

 NOTA: Con este modo de insertar imágenes, se puede imprimir (Pdf o impresora) pero si se envía el li bro o se lo coloca en alguna ruta de descarga (como en este caso) deberá adjuntarse también la carpeta con los archivos que deberán ser colocados en la misma ruta que se estableció en la macro. En este caso lo recomendable es dejarla indicada en alguna celda.

Ya veremos en otros ejemplos cómo insertar y guardar en el mismo libro los archivos. Esto solo es recomendable cuando el peso de las imágenes no impidan el buen manejo del libro.

Descargar archivo de ejemplo desde aquí.

Acceso al video N° 49

Gestor de Documentos

 Aplicación desarrollada en Excel para gestión de documentos SIN detalle de productos: cuotas de alumnos, socios, presupuestos y facturas por trabajos, notas de débito y crédito, etc. 

Con seguimiento de Clientes.

Configurable a tu empresa. 

Instructivo en la misma aplicación. 

Ver demostración en el siguiente video.

u$s 39,-

Gestor de Ventas

Aplicación desarrollada en Excel para gestionar facturas, notas de venta, presupuestos, remitos o albaranes.

Con seguimiento de Clientes y Productos.

Permite configurar datos de la empresa (tipo de moneda, numeración y otros campos), como así también las claves de hojas.

Con Instrucciones de uso en la misma aplicación.

Ver Demostración en el siguiente video:

u$s 49,-

48 - Eventos del Libro Excel

 Si bien en entradas anteriores (Eventos de Hoja Excel) ya vimos algunos ejemplos de macros colocadas a nivel de Libro, a continuación veremos los principales eventos para el objeto Libro, es decir aquellas macros que colocamos en el objeto ThisWorkBook.

OPEN: se ejecuta al abrir un libro. Aquí colocaremos las instrucciones que nos mostrará el libro con el formato deseado: qué barras quitamos, cómo ver la pantalla, qué hoja mostramos primero, qué hojas ocultamos, y así con el resto.

Ejemplo: 

Private Sub Workbook_Open()

Sheets("MENU").Select                                     'hoja de inicio

[B3].Select

ActiveWindow.DisplayWorkbookTabs = False   'sin pestañas

Application.DisplayFormulaBar = False           'sin barra fórmula

Userform1.Show                                                  'mostrar un Userform

End Sub

ACTIVATE: se ejecuta al activar un libro. Al abrir el libro ya se lo está activando. Pero aquí colocaremos las instrucciones que necesitamos que se ejecuten al pasar de otro libro a éste que ya se encuentra abierto. Por ejemplo, mostrar una hoja que siempre está oculta y al entrar al libro la necesitamos hacer visible para ejecutar algún proceso.

Ejemplo: 

Private Sub Workbook_Activate()

Sheets("Resultados").Visible = True

End Sub

DEACTIVATE: se ejecuta cuando desactivamos el libro, ya sea para pasar a otro o para cerrarlo. Siguiendo el ejemplo anterior, aquí podríamos volver a ocultar la hoja que mostramos al activarlo.

NOTA: las instrucciones se ejecutan estando ya en el nuevo libro activado. Por lo tanto hay que indicarle el nombre del que se acaba de desactivar para realizar alguna acción allí.

Ejemplo: 

Private Sub Workbook_Deactivate()

Workbooks("RESUMEN ANUAL.xlsm").Sheets("Resultados").Visible = xlVeryHidden

MsgBox "Acabas de salir del libro RESUMEN"

End Sub

BEFORE PRINT: se ejecuta al llamar a la opción de Impresión. En este evento se puede indicar cuál será la impresora a utilizar, la cantidad de copias. También se puede evitar la impresión de alguna hoja como en la siguiente macro:

Ejemplo: 

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'seleccionar la impresora

'impedir que cierta hoja se imprima

If ActiveSheet.Name = "Resultados" Then Cancel = True

End Sub

BEFORE SAVE: se ejecuta al guardar el libro. Aquí podríamos indicar el guardado de alguna copia en otra subcarpeta. También es conveniente dejar el libro con las mismas medidas de seguridad que veremos en el evento siguiente.

Ejemplo: 

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'guardar además una copia

ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "/COPIAS/" & Format(Now, "hh-mm-ss") & "_" & ActiveWorkbook.Name

End Sub

BEFORE CLOSE: se ejecuta al cerrar el libro. Aquí podríamos dejar las mismas instrucciones que en el guardado si no desarrollamos el evento BeforeSave (hacer una copia en otra subcarpeta). 

NOTA: Es conveniente dejar el libro con las mismas medidas de seguridad que establecemos en el evento Open. Es decir que si al abrir ocultamos ciertas hojas, debiéramos dejarlas también ocultas aquí en este evento como también en el de guardado (Save). Considerar que al abrir un libro bien podría abrirse sin habilitar las macros y por lo tanto no se ejecutarán las instrucciones de Open.... lo que dejaría visibles aquellas hojas a proteger.

Ejemplo: 

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'ocultar algunas hojas

Sheets("Resultados").Visible = xlVeryHidden

End Sub

NEWSHEET: se ejecuta al insertar una nueva hoja. Ya sea que presionemos el botón + desde las pestañas como si ejecutemos una instrucción agregando una nueva hoja. Podemos indicarle por ejemplo la ubicación.

Ejemplo: 

Private Sub Workbook_NewSheet(ByVal Sh As Object)

ActiveSheet.Move Before:=Sheets("Enero")

End Sub

Los eventos iniciados con SHEET:  son las mismas macros que colocaríamos en los objetos HOJA. Pero que tienen un alcance a todas las hojas del libro, exceptuando las que no requieran de estos procesos. 

Ejemplo:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If ActiveSheet.Name <> "PORTADA" And ActiveSheet.Name <> "MENU" Then

    Call procesoHoja

End If

End Sub

IMPORTANTE: si se programa un evento iniciado con Sheet en el objeto ThisWorkBook no debe ser colocado en el objeto HOJA si se trata del mismo proceso. 

Acceso al VIDEO N° 48.