sábado, 17 de marzo de 2018

Interactuando con Userform

El modelo que aquí se presenta permite crear registros en una base de datos. Además también se podrán modificar o eliminar registros de esa base.

Como adicional, utilizaremos 4 botones que nos permitirán encontrar el primer registro, el último, los anteriores y los siguientes al registro mostrado en el formulario.

Imaginemos una base con 2 campos principales: Código y Nombre. Pueden ser Proveedores, Clientes, Productos, Alumnos, Empleados, etc.

El código nos servirá para numerarlos de modo consecutivo. Pero la búsqueda se hará por Nombre (o Descripción, Apellidos, u otros textos).























¿Cuáles son los principales procesos que ejecutaremos desde el userform?

1- Al inicio del código del userform declaramos 2 variables que serán utilizas a lo largo de todos los subprocesos:
          Dim hop                  'variable que identifica a la hoja base de Proveedores
            Dim fily As Long     'variable que indica la fila del registro encontrado

2- En el evento Initilize del formulario se procede a :
  • Ordenar por la col B (texto) para mostrarlos en el desplegable y también para la búsqueda desde los botones inferiores. Esto porque al crear registros se agregan al final de la base y porque además el usuario puede cambiar el orden si lo necesita.
  •  Mostrar la numeración correlativa con la siguiente instrucción, que se repetirá luego de cada registro creado:
            Label7.Caption = Application.WorksheetFunction.Max(hop.Range("A:A")) + 1

3-  Para Buscar un registro se despliega la lista o se comienza a introducir los primeros caracteres para ubicarlo. Se muestran los campos del registro encontrado y se guarda en la variable 'fily' la fila. Se puede optar por Eliminarlo o sobreescribir en ellos para modificarlo.

Al Aceptar se ejecuta el mismo proceso que si fuera un nuevo registro. Si la variable 'fily' está vacía se busca la primer fila libre para agregarla, sino se sobreescribe en la fila del registro encontrado.

4- El primer registro es el de la fila 2 porque la tabla se encuentra ordenada por nombres. Y se mostrarán los campos de este registro:
          fily = 2
          Call paseDatos

5- El último registro es el último de la tabla y se obtiene con esta instrucción:
          fily = hop.Range("A" & Rows.Count).End(xlUp).Row
          Call paseDatos

6- El anterior es el de la fila anterior al del registro mostrado actualmente, entonces será:
dato = TextBox4.Text
Set busco = hop.Range("B:B").Find(dato, LookIn:=xlValues, lookat:=xlWhole)
If Not busco Is Nothing Then
    fily = busco.Row - 1

7- El siguiente será el de la fila siguiente al del registro mostrado actualmente:
dato = TextBox4.Text
Set busco = hop.Range("B:B").Find(dato, LookIn:=xlValues, lookat:=xlWhole)
If Not busco Is Nothing Then
    fily = busco.Row + 1

En el caso 6 (anterior) habrá que evaluar si no se está ya en el primer registro... y en el caso 7 (último) se evaluará si se llegó a una fila vacía significando que ya se está mostrando el último y por lo tanto no hay 'siguiente'.
If hop.Range("A" & fily) <> "" Then
      Call paseDatos
Else
      MsgBox "No hay más registros en la base.", , "ATENCIÓN"
      Exit Sub
End If

El resto de la programación del formulario son instrucciones de control, de pase a mayúsculas en algunos campos o de control de números en otros. 

Descargar el ejemplo completo desde aquí.

En el libro de ejemplo se deja además un userform que permite la búsqueda por ID desde los botones inferiores (Primero, Anterior, Siguiente y Último). La hoja siempre se presenta de modo ordenado por la columna B (texto).
Este ejemplo se explica en el siguiente VIDEO

lunes, 5 de marzo de 2018

Cinta o Ribbon (parte 2)

Siguiendo con el tema de 'cómo crear una cinta de opciones o Ribbon personal' vamos a tomar nota de otras instrucciones que necesitamos agregar en el Editor CustomUI y en el Editor VBA de Excel.

VIDEO 2

1- Para agregar aclaraciones a los botones, además del label podemos utilizar uno o los 2 atributos siguientes: screentip y supertip. El resultado se verá como en la imagen.

<button id="Button12" label="Listas del Sistema"  screentip="Actualiza listas del Sistema" supertip="Para modificar y actualizar listas de: unidades y otras listas." imageMso="AccessRelinkLists" size="large" onAction="Boton12"












2- Una lista desplegable sería como un subgrupo. Se pueden declarar los mismos atributos que para los botones. Lo cerramos del mismo modo que hacemos con el grupo. En ese caso el código tendrá la siguiente estructura:

<group id="Group4" label="Gestion de Usuarios">
<menu id="Menu3" label="Usuarios" size="large" image="usuarios" >
                <button id="Button9" label="Registrar Usuario"  onAction="Boton9" />
                <button id="Button10" label="Modificar Usuario"  onAction="Boton10" />
                <button id="Button11" label="Eliminar Usuario"  onAction="Boton11" />
</menu>
</group>











3- Para este tipo de modelo (donde luego haremos mención a cada elemento de la cinta para habilitarlos o no),  en el Editor de Excel escribiremos la siguiente macro donde además declaramos la variables como públicas ya que serán utilizadas en varios subrutinas:

Option Base 1
Public Cinta As IRibbonUI
Public RetVal(16) As Boolean      'cantidad de botones

Sub CargarCinta(CintaDeExcel As IRibbonUI)
    Set Cinta = CintaDeExcel
    frm_Login.Show
End Sub

 Y en el Editor CustomUI desde la primer instrucción indicaremos que al abrir el libro se ejecute esa subrutina:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="Ribbon.CargarCinta">

4- Los botones se numerarán siguiendo el orden de aparición para poder, mediante un bucle, activarlos o no según categoría de usuario. 
En el grupo Gestión de Usuarios agregamos un botón y un desplegable con 3 opciones quedando así la numeración:

<group id="Group4" label="Gestion de Usuarios">
<button id="Button8" label="Cerrar sesion" imageMso="StartTimer" size="large" onAction="Boton8" />
<menu id="Menu3" label="Usuarios" size="large" image="usuarios" >
                <button id="Button9" label="Registrar Usuario"  onAction="Boton9" />
                <button id="Button10" label="Modificar Usuario"  onAction="Boton10" />
                <button id="Button11" label="Eliminar Usuario"  onAction="Boton11" />
</menu>
</group>

NOTA: si relacionamos el número de botón con el nombre de la macro nos ahorraremos dificultades a la hora de realizar algún cambio (como agregar o quitar botones).

5- Las opciones que pueden ser habilitadas o no contarán además con el siguiente atributo: getEnabled (inhabilitar) o getVisible (ocultar)

<button id="Button9" label="Registrar Usuario"  onAction="Boton9getEnabled = "RetornoDelBoton9" />

Donde le estamos indicando que estará habilitado o no según el valor de la variable  RetornodelBoton.

6- En Excel guardaremos en 2 celdas de una hoja los valores FALSO y VERDADERO (en libro de ejemplo se utilizaron L1:L2 de hoja Login)

7- En el editor de Excel tendremos los siguientes códigos:
  • En el userform LOGIN declaramos la matriz con la cantidad de botones que tendrá nuestra cinta en total:

Option Base 1
Private Sub btn_Registrar_Click()
Dim rango As Range
Dim vBoton(16) As Boolean    'cant de botones en la cinta
Application.ScreenUpdating = False

  • En la macro del botón INGRESAR se evaluará la categoría del usuario que se está logeando. Por ejemplo, con el siguiente código le habilitamos los botones del 6 al 13 y deshabilitamos los botones del 14 al 16 para el usuario de categoría 'Admin', omitiendo el botón 8 que no sufrirá cambios.
ElseIf Status = "Admin" Then
            For x =6 To 13
                'omitimos el botón 8 que no cambia para ningún usuario
                if x <> 8 Then 
                      vBoton(x) = Sheets("Login").[L2]     ‘aquí se guarda el texto VERDADERO
                      RetVal(x) = vBoton(x)
                      Cinta.InvalidateControl ("Button" & x)
                End If
            Next x
            For x = 14 To 16
                vBoton(x) = Sheets("Login").[L1]     ‘aquí se guarda el texto FALSO
                RetVal(x) = vBoton(x)
                Cinta.InvalidateControl ("Button" & x)
            Next x
  • En el módulo donde colocamos las macros de llamada a las opciones de la cinta, también agregaremos las de cada botón de Retorno (ajustar el número)
Sub RetornoDelBoton9(control As IRibbonControl, ByRef ValorDevuelto)
    ValorDevuelto = RetVal(9)
End Sub

8- Podemos hacer una combinación de atributos o modo en que se muestren los botones, ocultando algunos e inhabilitando otros.
Por ejemplo en la segunda imagen, para un usuario ocultamos los 2 últimos controles del grupo de Guardado con getVisible (número 6 y 7 de control). El 8 queda sin cambios y el resto solo se inhabilitó (getEnabled).

Así se verá para un usuario con todos los permisos:
Y así se verá para un usuario con restricciones:

VIDEO 2

Descargar archivo Excel con el ejemplo desde aquí.



jueves, 1 de marzo de 2018

Cinta personalizada (Ribbon)

Un trabajo profesional requiere también de una imagen más profesional que una simple hoja de Excel. Y una de las opciones es crear nuestra propia cinta de opciones o Ribbon, personalizandola con grupos y botones más acorde al contenido de nuestra aplicación.

Aquí veremos varias alternativas:

Modelo 1- Agregar un menú personalizado dentro de los de Excel. De modo predeterminado se aloja al final de las fichas de Excel salvo que le indiquemos otra ubicación como en este ejemplo donde la alojé al inicio.


Modelo 2- Crear una cinta personalizada omitiendo las fichas de Excel. En este modelo ya podemos observar  una serie de fichas, algunas con botones y otras con listas desplegables.

Modelo 3- Uso de un menú personalizado donde se inhablitan opciones o grupos según el usuario que esté trabajando en la aplicación.


Modelo 4- Uso de un menú personalizado ocultando grupos completos según el usuario que esté trabajando en la aplicación.


Para crear nuestra propia cinta necesitaremos de:

a- un editor que nos permita escribir el código necesario. Utilizaremos el llamado OfficeCustomUIEditor que se podrá descargar de modo gratuito desde la Web (hay varios sitios para descargarla, sino pueden hacerlo desde aquí )

b- La galería de imágenes de Office (OfficeIconsGallery - opcional.*)

c- Una carpeta con iconos a gusto para insertar en la cinta (opcional. *)

* La galería de imágenes de Office contiene gran cantidad de iconos que los podemos utilizar sin necesidad de contar con una carpeta personal de imágenes. Se asumen ya incluídas en la aplicación Excel. Desde aquí pueden descargar un pequeño Excel que las contiene. Allí mismo dejo las instrucciones de cómo elegir las que irán en la cinta a crear.

Para otros botones algo más personales habrá que buscar iconos en formato PNG. Recomiendo ubicarlos en una misma subcarpeta para tener un fácil acceso a la hora de insertarlos. 
Los nombres de los archivos PNG deben ser cortos y sin espacios. Si fuese necesario tendrán que renombrarlos antes de utilizarlos en el editor CustomUI.

En el siguiente video se muestra el paso a paso para crear la cinta según los distintos modelos presentados al inicio de esta nota.


A continuación  un resumen de los principales detalles a tener en cuenta:

1- Una vez descargado el editor CustomUI se procederá a instalarlo. Es liviano y no ofrece ninguna dificultad. Al ejecutarlo se verá una ventana sencilla con pocas herramientas:

2- Abrimos el libro donde vayamos a agregar nuestra cinta personalizada. Una vez abierto insertaremos el Office2010 Custom para versiones 2010 y posteriores. De lo contrario insertaremos el Office 2007 Custom. 

3- Desde el mismo menú Insert, opción Sample XML, vamos a elegir Custom Tab lo que nos dará como resultado un código bien simple que será el punto de partida para crear nuestra cinta.

4- A esta altura ya debemos tener diseñada la aplicación en cuanto a tareas a realizar: Cómo se van a agrupar los controles, los textos que se van a mostrar en cada grupo, si las opciones se van a presentar de modo lineal o como lista desplegable y qué imágenes se asociarán a cada botón u opción.

RECIÉN ENTONCES SE COMENZARÁ A PROGRAMAR LA CINTA EN EL EDITOR CUSTOM UI.

Este punto es importante, porque los controles se numeran según el orden en que se presentan. Agregar a continuación alguna opción que hemos olvidado hará que tengamos que hacer un cambio en los nombres de los botones y eventualmente de las macros asociadas..

5- Si vamos a utilizar iconos de la galería de Office, abriremos el Excel con la galería y al final del menú Desarrollador (o Programador) encontraremos varias galerías. Tomaremos nota del nombre de las imagenes elegidas.

El código se puede redactar en un txt y luego copiarlo en el Custom o desarrollarlo directamente aquí en el Editor. 
Si observamos el modelo básico, prestaremos atención a las siguientes instrucciones:
  • Si la instrucción startFromScratch está en false el menú que vayamos a crear se mostrará como uno más junto con los que presenta la cinta de Excel (modelo 1). 

       





Para ubicarlo al inicio del resto de las pestañas, agregaremos la instrucción insertBeforeMso e indicando el nombre de la pestaña, en este caso antes de Inicio.
          <tab id="DbMacros" insertBeforeMso="TabHome" label="Mi Aplicación">

          Si dejamos la instrucción startFromScratch en True será el único menú presente (modelo 2)
  • A continuación agregaremos los grupos y sus botones siguiendo este esquema:    
  • Cada grupo que se abre con <group debe ser cerrado con </group> 
  • Cada botón que se abre con  <button id= debe ser cerrado con   />
  • Las imágenes de la Galería de Office se insertan como imageMso= , en cambio las que insertemos como Png serán con image=
  • Al finalizar con todos los grupos se cerrarán las instrucciones iniciales siguiendo este orden:
                     </tab >
                   </tabs >
                 </ribbon >
               </customUI >


6 - Necesitamos insertar también las imágenes personales, es decir aquellas que hemos agregado con la instrucción image=
Si las tenemos en una misma carpeta podremos seleccionarlas todas juntas para insertarlas en un solo movimiento.
Podemos verificar si están todas abriendo el desplegable. Con clic derecho sobre algún nombre nos permitirá removerlo.



7 - Al finalizar probaremos si todo se encuentra bien estructurado y redactado ejecutando el botón que se observa en imagen siguiente. 

Si recibimos esta respuesta es que nuestro programa está correctamente desarrollado, sino se nos indicará en qué línea se presentó algún error.

8 - Por otra parte, desde el Editor VBA de nuestro libro procederemos a revisar las macros de cada botón para verificar que los nombres sean los correctos en las instrucciones onAction.
Recordemos que las subrutinas deben llevar el texto: control As IRibbonControl

Por ejemplo:      Sub MacroInicio(control As IRibbonControl)
                            Sheets("Menu").Select
                            End Sub

9- Una vez todo correcto procederemos a Guardar.... o Cerrar y nos dará la opción de guardar.

Probaremos nuestro Excel . Si necesita algún cambio volveremos al Custom. Recordar que recién podremos guardar los cambios en este editor una vez que hayamos cerrado nuestro Excel. 









sábado, 17 de febrero de 2018

Atajos de teclado

En esta segunda entrada del blog Aplica_Excel hablaremos de los distintos atajos para realizar tareas desde una aplicación Excel.

Seguramente muchos acostumbrarán a manejarse dentro de la hoja Excel con los atajos propios de la aplicación. Desde las combinaciones con tecla CTRL, pasando por las teclas de función (F1 a F12) hasta combinaciones con otras teclas como ALT y MAYUSC.
Las habituales que seguramente la mayoría utiliza: CTRL C (copiar), F1 para acceder a la Ayuda, y tantas otras. Pueden descargar una lista bastante completa desde aquí.

Pero también podemos utilizar atajos de teclado para ejecutar botones dibujados en una hoja o dentro de un Userform.
  •       En una hoja:
En la siguiente imagen contamos con un menú de opciones y lo hemos presentado con botones de la barra Formularios
Para ejecutar cada botón u opción le asignamos atajos de teclado con tecla CTRL y una letra que nos relacione con la tarea.
Esto lo logramos desde la ficha Desarrollador, Macros, seleccionándola y desde el botón Opciones le asignamos la letra deseada.





   
















Por ejemplo, para la Gestión de Usuario utilicé la letra U (mayúsculas o minúscula será lo mismo). 
Y al botón Cerrar Sesión le asigné la letra S.


También una macro asociada a una imagen puede ser ejecutada haciendo clic sobre la imagen o con un atajo de teclado, como en este caso donde le asocié el atajo CTRL R.


Si tenemos botones ActiveX dibujados en una hoja también podemos asignarle atajos, pero en éstos necesitamos asociar la letra desde la propiedad Acelerator del control.
- Desde la ficha Desarrollador, pasaremos a Modo Diseño.
- Seleccionamos el control con clic derecho, Ver Propiedades.
- En la propiedad Acelerator introducimos la letra para ese atajo.
- Desactivamos el Modo Diseño.


  • Atajos dentro de un formulario: 
Al activar un formulario podremos ejecutar cada botón de comando con teclas aceleradoras, asignándole una letra que aparecerá subrayada. Nuevamente aquí tratamos de relacionar la letra con la tarea a realizar o texto del botón.


Para asignar estas teclas trabajaremos desde el Editor.
Se selecciona cada CommandButton y desde su propiedad Acelerator se establece la letra a utilizar.
Para ejecutarlo se presionarán juntas las teclas ALT y la letra elegida. Para este caso: ALT R


 Otro ejemplo con ALT N


Las teclas aceleradoras pueden ser asignadas a cualquier tipo de control. Veamos el caso de los OptionButton. Nuevamente aquí les he asignado una letra que tenga relación (en lo posible) con el texto. Al avanzar con la tecla aceleradora ( ALT T) automáticamente se marcará esa opción.


De este modo, avanzando con Enter Tab y llamando a diferentes comandos con sus teclas asociadas, la carga de un formulario será muy ágil.

  • Formulario diseñado en hoja Excel:

Este último ejemplo no es exactamente sobre atajos de teclado, sino en cómo avanzar por los distintos campos sin utilizar el ratón cuando tenemos un formulario diseñado en una hoja.

Como Excel trae de modo predeterminado, todas las celdas bloqueadas debemos seleccionar aquellas donde vayamos a ingresar o modificar datos y las dejaremos desbloqueadas desde el menú Formato, Protección
En este ejemplo seleccionamos solo las celdas de color considerando que aquí ingresaremos datos. El resto se completará con fórmulas.


Solo resta proteger la hoja (la clave es opcional) para observar su funcionamiento. De este modo el avance con Enter o Tab se realizará solo por las celdas desbloqueadas.


Descargar archivo Excel con ejemplos desde aquí





viernes, 16 de febrero de 2018

Autoajuste de filas


Una de las tareas que más dificultad presenta en Excel es la de ajustar el alto de fila según datos ingresados. En especial si se trata de celdas combinadas.

Son muchas las situaciones que pueden presentarse ….. trataré de ejemplificar la mayor cantidad de casos posibles, ya sea que se trabaje con una columna única o columnas combinadas.

En estos VIDEO 1 y VIDEO 2 se muestra la ejecución de todos los casos que a continuación resumo:

a- Tabla de datos donde una columna x debe ser autoajustada para mostrar todo el texto en una fila. La columna tendrá activada su propiedad Ajustar Texto. De ese modo al escribir en una celda de esa columna elegida automáticamente se autoajustará el alto de fila.

b- Tabla de datos con columnas combinadas. El alto de las filas se autoajusta mediante código que se coloca en el objeto HOJA donde se trabajará.

Private Sub Worksheet_Change(ByVal Target As Range)
'se controla lo ingresado en celdas D:E, a partir de fila 3
If Intersect(Target, Range("D:E")) Is Nothing Then Exit Sub
If Target.Row < 3 Then Exit Sub

'autoajuste celda en col D:E
fily = Target.Row
'ancho de columnas combinadas
    anchocol = Range("D" & fily).ColumnWidth + Range("E" & fily).ColumnWidth
'alto normal de la fila
    alto = 15
'se evalúa cuántos saltos de renglón existen en texto
Set c = Range("D" & fily)
saltos = Len(c) - Len(Application.WorksheetFunction.Substitute(c, Chr(10), vbNullString))
'si no hay saltos de renglón utiliza un método de cálculo, sino otro
If saltos = 0 Then
    anchotexto = Len(Target.Text)
    If anchotexto <= anchocol Then
        Target.RowHeight = 15
        Exit Sub
    End If
    dif = (anchotexto - anchocol) / alto
    cantfilas = alto + alto * (Int(dif) + 1)
Else
    'evalua cuantos renglones utiliza el párrafo
    Z = 1: canti = 0
    For x = 1 To saltos + 1
        salto1 = InStr(Z, c, vbLf)
        If salto1 > anchoCol Then
            canti = Int(salto1 / anchoCol) + canti
        End If
        Z = salto1
    Next x
    cantfilas = alto * (saltos + 1 + canti)
End If
'se asigna el alto obtenido
With Range("D" & fily)
    .RowHeight = cantfilas
    .WrapText = True
    .HorizontalAlignment = xlLeft
End With
End Sub


c- Datos obtenidos desde un formulario o Userform. Nuevamente aquí ya podemos dejar asignada la propiedad Ajustar Texto en la columna de Descripción.
Si no configuramos de antemano esta columna con la propiedad Ajustar Texto, lo podremos indicar para cada celda al momento de guardar datos en la columna D con esta instrucción:

Range("D" & fily).WrapText = True

d- Datos obtenidos desde un formulario o Userform, desde un control con varias líneas.

En primer lugar, se ajustará la propiedad MultiLine en True para el control que recibirá el texto.
Para pasar al renglón siguiente al momento de introducir el texto, se presionarán juntas las teclas Mayúsc + Enter.

Al realizar el guardado, la instrucción indicará que se trata de una celda con Ajuste de texto tal como hemos visto en el punto anterior, con la instrucción:

Range("D" & fily).WrapText = True


e- Celdas combinadas. 
Al igual que en los ejemplos anteriores, podemos trabajar con un campo textbox de una sola fila (sin saltos de renglón) o en modo columna.

En este ejemplo contamos con columnas combinadas (D:E) para contener el texto de la Descripción. Y el control textbox del formulario tendrá su propiedad MultiLine en True














La diferencia en el código tratándose de celdas combinadas, es que aquí se necesita calcular cuántas filas son necesarias para mostrar el texto completo considerando el total de columnas utilizadas para el texto. En el ejemplo utilizamos las columnas D:E

         'ancho de columnas combinadas 
anchoCol = Range("D" & fily).ColumnWidth + Range("E" & fily).ColumnWidth 

Las instrucciones para pasar a la hoja el campo Descripción y llamar a la macro que calcula filas necesarias, serán las siguientes:

       Range("D" & fily) = TextBox6        'descripción
       'autoajuste celda en col D:E
       Call calcula_AltoFila

Y en un módulo dentro del mismo formulario se encontrará la subrutina.

        Sub calcula_AltoFila()
       ‘instrucciones
       End Sub



NOTA: la variable fily que se utiliza en las 2 subrutinas debe estar declarada al inicio del formulario o Userform con esta instrucción:

        Dim fily As Long


Por último, solo quedaría agregar una instrucción para que los textos de toda la fila ajustada se presenten de modo centrado, con esta instrucción que se agrega al botón de guardado (ACEPTAR) al finalizar todo el pase de datos:

Range("A" & fily).EntireRow.VerticalAlignment = xlCenter

              VIDEO 1     VIDEO 2
    

              (descargar modelo completo en Excel desde aquí)