miércoles, 11 de julio de 2018

Bloquear, ocultar, agrupar esquemas e inmovilizar paneles.

Son muchas las situaciones donde necesitamos ocultar o bloquear rangos de celdas.

Y para ello tenemos varias opciones:

1- Bloquear acceso a rangos más allá de los datos. Generalmente son rangos donde realizamos cálculos o guardamos las listas que utilizaremos en la aplicación.
En una tabla como en la de la imagen solo se accede al rango A:G con el siguiente código:

   Sub ocultando()
   'atajo de teclado CTRL O
   Sheets("Ventas").ScrollArea = "A:G"
   End Sub



Para desbloquear los rangos tendremos otra macro dejando el ScrollArea vacío.

   Sub mostrando()
   'atajo de teclado CTRL M
   Sheets("Ventas").ScrollArea = ""
   End Sub

Del mismo modo que procedemos con las columnas podemos hacerlo con filas. Por ejemplo, permitiendo solo el acceso hasta la fila 20.

   Sheets("Ventas").ScrollArea = "A1:G20"

2- Ocultar rangos más allá de los datos: este método también nos permite tener un área para cálculos auxiliares y no visibles para terceros.
Con la misma macro se ocultan las columnas desde la H hasta el final y se vuelven a mostrar (en caso de estar ocultas). Lo mismo se puede aplicar a filas.

Sub ocultaDesdeH()
'atajo de teclado CTRL H
If Columns("H:XFD").Hidden = True Then
    Columns("H:XFD").Hidden = False
Else
    Columns("H:XFD").Hidden = True
End If
End Sub



3- Utilizar esquemas: si contamos con una tabla donde algunas columnas contienen datos necesarios pero no relevantes, podemos agrupar esas columnas en un esquema:


Para este ejemplo hemos seleccionado los títulos de las columnas C:G y desde menú Datos, Agrupar optamos por Columnas.
De este modo fácilmente mostramos y ocultamos aquellas columnas cuya información no es necesaria tenerla siempre a la vista.
Para quitar el esquema iremos nuevamente al menú Datos, Desagrupar, Borrar Esquema.

NOTA: a la hora de programar el uso de una tabla con esquemas, debemos tener presente estos detalles:
a - Si por ejemplo estando en la celda B3 utilizamos la instrucción ActiveCell.Offset(0,1) nos mostrará la Dirección del cliente. 
Pero si tenemos las columnas ocultas, con la misma instrucción seguiremos obteniendo información de la columna C y no las Ventas que se encuentran en col H.


En estos casos, en lugar de ir contando cuántas columnas colocar en el argumento del Offset, para asegurarnos de tomar la columna correcta será mejor utilizar la expresión:

    Range("H" & ActiveCell.Row)

b - Si protegemos la hoja del modo habitual desde menú Revisar, Proteger hoja e intentamos utilizar el esquema para ocultar/mostrar columnas, un mensaje nos indicará que no es posible utilizar esta herramienta en una hoja protegida.
Para resolver esta situación tendremos que proteger la hoja mediante código, agregando otros argumentos a los habituales.

    Sub protegiendo()
    'atajo: CTRL p
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
      Scenarios:=True, AllowFormattingColumns:=True, AllowFiltering:=True, _
      UserInterfaceOnly:=True
    ActiveSheet.EnableOutlining = True
    End Sub


4- Inmovilizar Paneles: este es otro método que nos permite ocultar columnas o filas a medida que avanzamos por la hoja, dejando siempre visibles aquellas filas superiores o columnas a izquierda como títulos o encabezados.

Si por ejemplo tenemos una hoja con información diaria, en algún momento tendremos que ocultar los primeros días para observar los últimos días del mes.


Podemos inmovilizar solamente la columna de conceptos (seleccionando la col D) o inmovilizar solamente los títulos superiores (seleccionando la fila 10).
Pero si deseamos mantener inmovilizados tanto los encabezados superiores como las primeras columnas de conceptos, tendremos que seleccionar una celda (D10),  es decir la intersección entre filas superiores y columna de conceptos a la izquierda. Lo que hará que al movernos por la hoja podremos ver los encabezados de este modo. (observar los rangos de letras de columnas y números de fila que van quedando ocultos a medida que nos movemos por la hoja).



Ver VIDEO


lunes, 25 de junio de 2018

Compartir macros y Userforms

El tema de hoy tiene que ver con la posibilidad de llamar a diferentes Userforms o subrutinas desde distintos procesos, según algún criterio común a todos.

Primer Caso: Imaginemos la siguiente situación: tenemos  varios userforms y todos utilizan una misma lista de clientes. Para no repetirla podremos mostrarla en otro formulario al que tengan acceso los anteriores.

Entonces vamos a crear un Userform con una lista (ListBox) y en evento doble clic de la misma volcaremos el registro seleccionado al formulario que lo llamó.

Veamos cómo programarlo:
Aquí tenemos 3 formularios (Clientes, Ventas y Cobros) que utilizarán la misma lista que se encuentra en el formulario UF_Lista.
En un módulo tendremos las subrutinas que llaman a cada formulario y al inicio una declaración de variable pública (llamaUf)

Entonces cada formulario tendrá un botón de llamada con las siguientes instrucciones, donde se guardará un número para indicar de qué formulario se trata (1 para Clientes, 2 para Ventas y 3 para Cobros)
     Private Sub CommandButton2_Click()
     llamaUf = 3
     UF_Lista.Show
     End Sub


Solo falta ver cómo se devuelve la información al control Cliente de cada formulario luego de la selección de la lista:

El evento que utilizamos en el ListBox es el DobleClick y el bucle es del tipo Select Case... End Select.
   Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
   If ListBox1.ListIndex = -1 Then
       MsgBox "Debes seleccionar un elemento con doble clic"
       Exit Sub
   End If
   Select Case llamaUf
   Case Is = 1
       UF_Clie.TextBox1 = ListBox1.List(ListBox1.ListIndex)
   Case Is = 2
       UF_Ventas.TextBox3 = ListBox1.List(ListBox1.ListIndex)
   Case Is = 3
       UF_Cobros.TextBox4 = ListBox1.List(ListBox1.ListIndex)
   End Select
   'se cierra el uf
   Unload Me
   End Sub

NOTA: si son pocas las opciones también se puede utilizar un bucle del tipo If...Elseif....End If
   If llamaUf = 1 Then
       UF_Clie.TextBox1 = ListBox1.List(ListBox1.ListIndex)
   Elseif llamaUf = 2 Then
      UF_Ventas.TextBox3 = ListBox1.List(ListBox1.ListIndex)
   End If




Segundo Caso: una misma macro es llamada desde varios procesos diferentes.
Aquí tendremos algo similar a lo anterior pero los procesos no llamarán a un formulario sino a una macro común a todos, que se encontrará ubicada en un módulo.

Imaginemos esta situación: tenemos 3 hojas con información y en algún proceso una hoja debe ser ordenada por una columna en especial. En otro proceso otra hoja también debe ser ordenada pero no necesariamente por la misma columna sino por otra y así con todas las hojas.

Entonces, primero vamos a declarar las variables públicas al inicio de un módulo (*) para guardar la siguiente información:
- nombre de la hoja que debe ser ordenada.
- rango ocupado por la tabla de datos.
- columna por la que hay que ordenar esa tabla.

(*) Se declaran como Public si van a ser utilizadas fuera del módulo donde están declaradas y con Dim si van a ser utilizadas en subrutinas dentro del mismo módulo.

     Public hojax As String   'para indicar quién llamó a la macro de orden
     Dim colOrden As String, rgoOrd As String

Ya con eso, cada botón o proceso que llame al ordenamiento, tendrá estas instrucciones donde irán cambiando el nombre de las hojas:

     Sub ordenaClie()
     hojax = "Clientes"      'podría ser: hojax = ActiveSheet.Name si es un proceso que se ejecutará desde la hoja activa.
     Call macroOrdena
     End Sub

La macroOrdena primero selecciona la hoja, guarda las referencias de rango y columna a ordenar y luego llama a la subrutina de orden propiamente dicha.

Sub macroOrdena()
'se selecciona la hoja solicitada
Sheets(hojax).Select
'se establece la última fila con datos de la hoja activa
filx = Range("A" & Rows.Count).End(xlUp).Row

'según la hoja será el rango y criterio de orden
If ActiveSheet.Name = "Clientes" Then
    colOrden = "D2:D" & filx
    rgoOrd = "A1:F" & filx
ElseIf ActiveSheet.Name = "Ventas" Then
    colOrden = "E3:E" & filx
    rgoOrd = "A2:E" & filx
ElseIf ActiveSheet.Name = "Cobros" Then
    colOrden = "D2:D" & filx
    rgoOrd = "A1:D" & filx
Else
    'en otras hojas no se ejecuta
    Exit Sub
End If

'se ejecuta el ordenamiento
Call ordenando
Range("A1").Select
End Sub

Sub ordenando()
Application.ScreenUpdating = False
    Range(rgoOrd).Select
    ActiveWorkbook.Worksheets(hojax).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(hojax).Sort.SortFields.Add Key:=Range(colOrden) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets(hojax).Sort
        .SetRange Range(rgoOrd)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Como se puede observar, la última subrutina es la que nos genera la grabadora de macros cuando necesitamos ordenar un rango. Y en lugar de tenerla repetida por cada hoja aquí la tenemos 1 sola vez.

En el libro de ejemplo, se agregó otra que vuelve todas las hojas a su orden normal, en este caso por col A. Y esa macro también,  luego de asignar a cada hoja su rango,  utiliza la subrutina anterior llamada 'ordenando'. 

Ver VIDEO

Descargar el ejemplo desde aquí.





domingo, 10 de junio de 2018

Listas (con rangos en otras hojas)

Hasta ahora hemos visto el tema Listas con los rangos siempre en la misma hoja.
Excel no permite asignar a una celda con Validación de datos un origen marcando un rango de otra hoja.
Pero para esto también hay una solución.

En la entrada anterior (y en libro de descarga) observamos que nuestra hoja de trabajo, con las celdas validadas, la lista de meses y la tabla de datos, es la llamada 'ListaDinamica'.

Ahora, si quitamos esa lista de meses de allí y la colocamos en otra hoja, por ejemplo 'Validación', lo que haremos es asignarle, a esta nueva lista, un nombre de rango. 
Desde menú Fórmulas, Administrador de nombres, Nuevo y la llamaré LISTA_MES.


El ámbito ahora debe quedar marcado como 'Libro'.

El paso siguiente es asignar a la celda C4 como origen este nuevo nombre de rango:


De este modo nuestra hoja de trabajo no necesita la lista de meses.


Ver VIDEO
Descargar libro con ejemplo de las 2 últimas entradas desde aquí.

Listas dependientes (sin macros)

Siguiendo con el tema desarrollado en entrada anterior, aquí veremos cómo utilizar en una hoja Excel listas dependientes pero sin macros sino con fórmulas.

Vimos que los distintos tipos de listas que podemos utilizar en Excel son:
- Celdas con validación de datos (menú Datos)
- Controles dibujados (menú Desarrollador o Programador)
- Controles en un Userforms.

Entonces, si no queremos utilizar macros en nuestro libro optaremos por el modelo de celdas con Validación de datos.

NOTA: desde el VIDEO pueden observar el paso a paso.

1- Primero vamos a colocar una lista de meses para asignarla a la primer celda, que en el ejemplo es C4. Desde menú Datos, Validación de datos optar por Lista y en Origen seleccionar o ingresar el que corresponda (en el ejemplo es M1:M12)

2- Luego vamos a copiar esa lista de meses y con Pegado especial, Transponer, la ubicaremos a partir de fila 1 para contar con un rango de meses y sus días en forma vertical.

3- Seleccionaremos el rango de títulos (en el ejemplo O1:Z1) y desde el menú Fórmulas, Administrador de Nombres, Nuevo le asignaremos el nombre MESES.



4- Completamos las columnas con días (vencimientos, feriados, cumpleaños, etc)

5- Ahora seleccionaremos cada rango para asignarles Nombres de rango. Utilizaremos los mismos títulos para que el sistema los reconozca al seleccionar un mes en el desplegable de C4.
Por ejemplo, seleccionamos el mes de Abril con sus días y desde menú Fórmulas, Administrador de nombres dejaremos el nombre seleccionado y ajustaremos el rango para que inicie en fila 2. Esto se hará con cada mes de la tabla.


6- Solo nos resta validar la segunda celda, la que será dependiente de la primera. Es decir, que al seleccionar algún mes desde C4, en otra celda nos muestre los días que le corresponden a ese mes.
Aquí necesitaremos de la función INDIRECTO, es decir que busque el contenido de C4 y según eso despliegue el rango de igual nombre.


Observemos entonces el resultado obtenido. Al seleccionar Abril nos mostrará en el siguiente desplegable el rango con ese mismo nombre.


Lo visto hasta aquí es perfecto para listas de rangos fijos, por ejemplo País-Ciudades.
Pero en otros casos quizás necesitemos ir agregando datos a los rangos, en nuestro ejemplo más días en algunos meses.

Por lo tanto necesitaremos rangos dinámicos. Y las modificaciones que realizaremos entonces son, a partir del punto 5:

5b - Dejaremos los rangos asociados con cantidad suficiente de celdas, podría ser hasta 31, 100 o directamente toda la columna:


6b- Utilizaremos para el Origen en la Validación de datos de la celda dependiente, la función DESREF que devuelve la referencia de un rango, que es un número de filas y columnas de una referencia dada.
La función tiene los siguientes argumentos:

=DESREF(ref, filas, col, [alto], [ancho])


filas: es el número de filas, hacia arriba o hacia abajo de la ref. 
columnas: es el número de columnas hacia derecha o izquierda de la ref. 
[alto]: es un argumento opcional que nos permite indicar la cantidad de filas con datos que tiene la columna.
[ancho]: indica la cantidad de columnas que mostraremos, que como en este caso es 1 sola columna la omitimos.

Nuestra fórmula para la validación de la celda dependiente quedará entonces así, según el modelo de la imagen anterior:

=DESREF(O1;1;COINCIDIR(C4;MESES;0)-1;CONTARA(INDIRECTO($C$4))-1)

ref: O1 que es la 1er celda de la tabla de MESES.

filas: 1 fila hacia abajo porque los datos empiezan en fila 2.

columnas: la buscaremos en el rango MESES según el valor de C4 con la función COINCIDIR
.
                 COINCIDIR(C4;MESES;0) -1

Como la función COINCIDIR nos devolverá el número de columna dentro del rango MESES, para ENERO nos devolverá 1. 
Pero el argumento 'columnas' indica el número de columnas que nos desplazamos a derecha, siendo para ENERO 0 columnas, por eso en la fórmula DESREF restamos 1.

[alto]: buscaremos dentro de la columna la cantidad de celdas ocupadas, utilizando la función CONTARA, donde el rango será el que tenga el nombre del contenido de C4. Entonces si en C4 seleccionamos ABRIL buscará la cantidad de celdas ocupadas en rango de nombre ABRIL y le restamos 1 para omitir el título.

                  CONTARA(INDIRECTO($C$4))-1


Ver VIDEO

Descargar libro con ejemplo desde aquí.












domingo, 3 de junio de 2018

Listas dependientes

Seguramente todos en algún momento han utilizado alguna lista desplegable en Excel.

El objetivo de esta entrada será entonces 'relacionar' 2 o más listas de modo de hacerlas 'dependientes'. Es decir que según el elemento que se seleccione en una primera lista será la información que se presente en la segunda.

Pueden ser 2 listas validadas, 2 controles ComboBox o 1 control Combobx + 1 control ListBox.

NOTA: del mismo modo que relacionamos 2 listas podemos hacerlo con 3 o más.

Primero veamos qué tipo de herramientas podemos utilizar para el uso de listas. Muchas veces nos comentan en los foros que tienen un desplegable dibujado en la hoja.... ahora veremos que hay más de un tipo de desplegable y por lo tanto tienen distinto tratamiento.

- En hojas Excel:
  • Validación de datos (desde el menú Datos).
  • Controles Cuadro combinado (desplegables) y Cuadro de Lista (en modo lista) de la barra Formularios.
  • Controles ComboBox y ListBox de la barra ActiveX.
Estos controles se encuentran en menú Desarrollador (o Programador según la versión Excel), ficha Controles.

- En Userforms:
  • Controles ComboBox y ListBox (los mismos de la barra ActiveX).

La información que se presentará en cada lista la tendremos en un rango de la misma hoja, por ejemplo M1:M2 conteniendo los meses del año.

NOTA 1: Este paso será el mismo para la primer lista en caso de Listas Dependientes.

NOTA 2: Es posible trabajar con datos ubicados en otras hojas..... pero eso será tema para otra entrada. Aquí nos ocuparemos de relacionar 2 o más listas y los datos los tendremos en la misma hoja.


1-  Validación: Desde menú Datos, Validación, las opciones son Lista y en el campo Origen ingresaremos el rango donde se encuentren, en este caso, los meses del año.

2-  Controles Barra Formulario: Dibujamos un control con esta herramienta (Cuadro combinado o Cuadro de lista). 
Al seleccionarlo con clic derecho tendremos la opción de asignarle Formato (rango, vincularlo con una celda, cantidad de elementos a mostrar al desplegarlo)  y Asignar macro si hiciera falta.

3-  Controles ActiveX: hay que pasar desde la misma ficha Controles a Modo Diseño para poder insertarlos en la hoja.
Luego de dibujarlos y haciendo clic derecho sobre el control se accede a una lista de opciones, entre las que encontramos Propiedades y Ver Código si necesitamos asociarlo a una macro.

Este tipo de controles (ActiveX) son los mismos que utilizamos en un Userform. Las propiedades que utilizaremos en este ejemplo son:
     LinkedCell : celda donde guardaremos el valor elegido.
     ListFillRange: rango de donde se toman los valores
     ListRows: cantidad de elementos que se mostrará al desplegar la lista.

Hecha esta introducción, veremos ahora cómo las hacemos dependientes unas de otras.

En todos los casos trabajaré con un rango de meses (M1:M2)  y una lista de días, que pueden ser vencimientos, cumpleaños, días de reunión, etc.


Modelo 1: con 2 listas validadas.
Como he mencionado anteriormente, la primer lista se confecciona según lo ya explicado por lo que la celda C4 tendrá asignado el rango M1:M12.


Para que se modifique el rango de la celda D4 necesitamos una macro que controle la selección en la celda C4. Esta macro se colocará en la hoja correspondiente, en mi ejemplo se llama Modelo1.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$4" Then Exit Sub
'si la celda C4 queda vacía se quita la lista en D4
If Target.Value = "" Then
    [D4].Validation.Delete
    Exit Sub
End If
'se arma el rango para mostrar en D4
Set busco = Range("M:M").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not busco Is Nothing Then
    'fila y colmnas del mes buscado
    filx = busco.Row
    colx = Range("AZ" & filx).End(xlToLeft).Column
    'si no hay datos más allá de la col M el rango quedará vacío
    If colx > 13 Then
        rgo = Range(Cells(filx, 14), Cells(filx, colx)).Address
    Else
        rgo = Cells(filx, 14).Address   'celda vacía
    End If
    With [D4].Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & rgo
    End With
End If
End Sub

NOTA: En próxima entrada publicaré otro ejemplo donde las listas se relacionan mediante fórmulas. Pero esto requiere conocer el uso de las funciones INDIRECTO y DESREF además de conocer la herramienta 'Nombres de rango', por lo que dejaré como tema aparte.

Modelo 2: Cuadro combinado y Cuadro de lista de la herramienta Formulario.

Este modelo es válido tanto para mostrar la lista dependiente en otro cuadro combinado o en un cuadro lista. En mi ejemplo utilicé la segunda opción.

Como ya hemos visto al definir los controles de la barra Formulario, el primer control tendrá asignado el rango M1:M12 y la celda A5 para guardar el índice del elemento seleccionado. Este índice nos indicará la fila del mes elegido ya que al mes enero le corresponde la celda M1 y así con cada mes.

Lo que se intenta es mostrar en la lista los días que corresponden al mes elegido. 
Como el rango para asociarlo a una lista debe mostrarse de modo vertical, lo que haremos una vez identificado el rango de días, es transponerlo en una columna auxiliar y de allí asociarlo al cuadro de lista.
NOTA: la columna auxiliar L puede mantenerse oculta.


Para este tipo de controles las macros se ubican en un módulo. Luego se la asignamos con clic derecho sobre el primer control, opción Asignar Macro.

Sub CuadroCombinado1()
'fila del mes y últ col con días
filx = [A5]
colx = Range("AZ" & filx).End(xlToLeft).Column
'se limpia la col auxiliary se pasa el rango de días allí de modo vertical
Range("L:L").Clear
Range(Cells(filx, 14), Cells(filx, colx)).Copy
Range("L1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
Application.CutCopyMode = False
'se ubica la última celda ocupada. También puede ser utilizada la últ col -14
fini = Range("L" & Rows.Count).End(xlUp).Row
'se asigna el rango obtenido al control ListBox
ActiveSheet.Shapes("List Box 2").Select
Selection.ListFillRange = "Modelo2!L1:L" & fini
'opcional: seleccionar alguna celda
Range("F5").Select
End Sub

Para limpiar el Cuadro combinado alcanza con borrar el contenido de la celda A5. Entonces necesitaremos controlar esta acción para limpiar también el Cuadro de lista.

Para ello colocaremos un código en la misma hoja Modelo2:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$5" Then
    If Target = "" Then
        ActiveSheet.Shapes("List Box 2").Select
        Selection.ListFillRange = ""
        'opcional: seleccionar alguna celda
        Range("F5").Select
    End If
End If
End Sub

NOTA: observar el nombre que se incluye en las macros para identificar el Control de Lista. Esto puede variar según las distintas versiones de Excel.

Para obtener el nombre, dibujar el control en la hoja. A continuación encender la grabadora y ubicar el control en la posición adecuada o ajustarlo en tamaño. Detener la grabadora y en un módulo se encontrará un código de donde se puede obtener el nombre correcto para las macros.
Por ejemplo:
                          ActiveSheet.Shapes.Range(Array("List Box 3")).Select


Modelo 2: Combobox y ListBox de la barra ActiveX

Este modelo es válido tanto para mostrar la lista dependiente en otro ComboBox o en un ListBox. En mi ejemplo utilicé la segunda opción.

Se dibuja el primer control y se asignan las Propiedades de rango y celda asociada tal como hemos visto anteriormente en el punto 3.

Dibujamos el ListBox y dejamos vacía la propiedad ListFillRange.

NOTA: Recordar que para acceder a las distintas opciones haciendo clic derecho sobre estos controles hay que pasar previamente a Modo Diseño desde el menú Desarrollador (o Programador).

Las macros para este tipo de controles se ubican en la hoja donde se encuentran los controles.
Clic derecho sobre el ComboBox, opción Ver Código nos introduce en el Editor, en la hoja correspondiente. Y allí colocaremos una macro como la de este ejemplo:

Private Sub ComboBox1_Click()
'se limpia la lista de datos anteriores
ListBox1.Clear
If ComboBox1 = "" Then Exit Sub
'fila del mes elegido ...depende de la ubicación del elemento seleccionado
filx = ComboBox1.ListIndex + 1
'ultima col con nros de días
colx = Range("AZ" & filx).End(xlToLeft).Column
'si colx es 13 es la col M (del mes) y no tiene días
If colx > 13 Then
    'se recorre el rango desde la col 14 (N) agregando al Listbox
    For x = 14 To colx
        ListBox1.AddItem Cells(filx, x)
    Next x
End If
End Sub

Aquí necesitaremos también la macro que nos limpie los controles y eso lo haremos desde el evento Change de la hoja:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$6" Then
    If Target = "" Then ListBox1.Clear
End If
End Sub

NOTA: observar que aquí utilicé otro modo de llenar el ListBox. En lugar de asignarle un rango se recorre la fila de días agregándolos al listado. Pero bien podría haber utilizado el modelo 2.


Modelo 4: Controles en Userforms.

Para trabajar con este tipo de controles dentro de un Userform se siguen las mismas instrucciones dejadas en el Modelo 3.
Los controles son los mismos. Lo que puede cambiar es el evento desde donde los limpiaremos, que puede ser desde un botón Cancelar o al finalizar algún otro proceso. 
La instrucción, ajustando el nombre del control, siempre será:

                    ListBox1.Clear 

Ver VIDEO

Descargar el ejemplo completo desde aquí.











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