miércoles, 22 de enero de 2020

Modificar-Eliminar registros de hoja BASE con Userform.

En entrada anterior, con fecha 14/01/2020, vimos un formulario para el registro de datos a una hoja.

Pero todo lo que se ingresa debe permitir ser modificado.... y eventualmente también eliminado.
Por eso, en esta entrada vamos a diseñar y programar un Userform para estas 2 tareas.

Vamos a trabajar con la hoja Base de Proveedores que utilizamos para el Registro de datos anterior a la que le agregamos una columna más para registrar la fecha de la modificación.
En caso de eliminar un registro podemos optar por 2 acciones: eliminarlo de la hoja base .... o quitarlo de la base y copiarlo en otra hoja que será como un historial de elementos eliminados.

El formulario tendrá un campo de búsqueda y mostrará los campos del registro encontrado. A continuación se solicitará confirmación, tanto para Modificar como para Eliminar.

Los códigos que requiere este formulario son los siguientes:

1- En el evento Initialize: 
    a- se guarda el nombre de la hoja base en una variable que se declara al inicio del módulo para ser utilizada en todas las subrutinas.
   b- se ordena la lista de nombres (en un rango auxiliar) para poder mostrarlos de modo ordenado. Esta macro la dejé en el mismo formulario. Por eso se la llama con la variable que indica cuál es la útima fila ocupada.
   c- se llena el control Combobox1 con la lista ordenada.
   d- el Combobox2 ya tiene su lista asignada en su propiedad RowSource (Hoja Listas)

Dim hop
Private Sub UserForm_Initialize()
Application.ScreenUpdating = False
Set hop = Sheets("PROVEEDORES")         'variable declarada al inicio de este módulo
'se quitan posibles filtros en la hoja de Proveedores
With hop
    '.Unprotect
    If .FilterMode = True Then .ShowAllData
    .Select
End With
'se busca última fila del rango para llenar el combo
filp = hop.Range("B" & Rows.Count).End(xlUp).Row
If filp > 2 Then
    'si hay más de un registro se ordena la lista de nombres antes de mostrarla en el desplegable
    If filp > 3 Then Call ordenaLista(filp)
    For Each cd In hop.Range("K3:K" & filp)
        ComboBox1.AddItem cd.Value
    Next cd
End If
End Sub

Sub ordenaLista(fini)
'se limpia la col auxiliar K y se copia el rango actual de nombres
With hop
.Range("K:K").Clear
.Range("B3:B" & fini).Copy Destination:=[K3]
End With
ActiveWorkbook.Worksheets("PROVEEDORES").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("PROVEEDORES").Sort.SortFields.Add Key:=Range( _
"K3:K" & fini), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("PROVEEDORES").Sort
.SetRange Range("K3:K" & fini)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

2- En el evento Change del Combobox: se busca el registro y de encontrarlo mostrará en cada control el contenido de las columnas de esa fila. Se guarda la fila del elemento encontrado para sobreescribirlo o eliminarlo. Esta variable se declara el inicio del módulo con esta instrucción:
            Dim fily As Long
Private Sub ComboBox1_Change()
'busca la ubicación del registro
If ComboBox1 = "" Then Exit Sub
dato = ComboBox1.Value
Set busco = hop.Range("B:B").Find(dato, LookIn:=xlValues, lookat:=xlWhole)
'si el dato se encuentra en la base se muestran todos sus campos
If Not busco Is Nothing Then
    fily = busco.Row
    Label7.Caption = hop.Range("A" & fily)
    TextBox4.Text = hop.Range("B" & fily)
    TextBox6.Text = hop.Range("C" & fily)
    ComboBox2.Text = hop.Range("D" & fily)
    TextBox7.Text = hop.Range("E" & fily)
    TextBox11.Text = hop.Range("F" & fily)
    TextBox9.Text = hop.Range("G" & fily)
    TextBox10.Text = hop.Range("H" & fily)
Else
    MsgBox "No se encuentra este Proveedor en la base.", , "ATENCIÓN"
    Exit Sub
End If
TextBox4.SetFocus
End Sub

3- El botón Modificar: sobreescribe en cada celda del registro el contenido de los controles. La fila está indicada en la variable guardada al momento de la búsqueda y declarada al inicio del módulo.
Se controla que haya un nombre (significando registro encontrado). 
Los campos Nombre, Teléfono, Saldo y Productos mantienen el formato del formulario de Registro, o sea mayúsculas, valores enteros, decimales y mayúsculas respectivamente.

Private Sub Aceptar_Click()
If TextBox4 = "" Then
    MsgBox "Falta el nombre del Proveedor.", , "Faltan Datos"
    TextBox4.SetFocus
    Exit Sub
End If
sino = MsgBox("¿Deseas guardar estos datos?", vbQuestion + vbYesNo, "Confirmar")
If sino <> vbYes Then Exit Sub
hop.Range("B" & fily) = TextBox4
hop.Range("C" & fily) = TextBox6
hop.Range("D" & fily) = ComboBox2
hop.Range("E" & fily) = Val(TextBox7)
If TextBox11 <> "" Then hop.Range("F" & fily) = CDbl(TextBox11)
hop.Range("G" & fily) = TextBox9
hop.Range("H" & fily) = TextBox10
hop.Range("I" & fily) = Date
'formato a las col
hop.Columns("G:G").AutoFit
If hop.Range("G1").ColumnWidth > 36 Then hop.Columns("G:G").ColumnWidth = 36
Application.ScreenUpdating = True
Call Cancelar_Click
'refrescar la pantalla
Application.ScreenUpdating = True
End Sub

4- El botón Cancelar: además de limpiar los controles vuelve a actualizar la lista de nombres para mostrarla de modo ordenada en el ComboBox.

Private Sub Cancelar_Click()
Label7.Caption = ""
'limpia todos los controles Textbox
For Each ct In Me.Controls
    If TypeName(ct) = "TextBox" Then ct.Value = ""
Next ct
fily = 0
'se limpia el combobox y se lo llena nuevamente con datos actualizados
ComboBox1.Clear
'se busca última fila del rango para llenar el combo
filp = hop.Range("B" & Rows.Count).End(xlUp).Row
If filp > 2 Then
    'si hay más de un registro se ordena la lista de nombres antes de mostrarla en el desplegable
    If filp > 3 Then Call ordenaLista(filp)
    For Each cd In hop.Range("K3:K" & filp)
        ComboBox1.AddItem cd.Value
    Next cd
End If
ComboBox2.ListIndex = -1
ComboBox1.ListIndex = -1
TextBox4.SetFocus
End Sub

5- El botón Eliminar: previamente a eliminar el registro de la hoja Base se lo copiará en otra hoja (Eliminados).

Private Sub Eliminar_Click()
If TextBox4 = "" Then Exit Sub
If ComboBox1 = "" Then
    MsgBox "Selecciona desde el desplegable el nombre del Proveedor a eliminar.", , "ATENCIÓN"
    ComboBox1.SetFocus
End If
sino = MsgBox("¿Desea eliminar este Proveedor?", vbQuestion + vbYesNo, "CONFIRMAR")
If sino = vbYes And fily > 0 Then
    'busca 1er fila destino y pega el rango. Luego coloca fecha de eliminación en col I
    With Sheets("ELIMINADOS")
        ini = .Range("A" & Rows.Count).End(xlUp).Row + 1
        hop.Range("A" & fily & ":I" & fily).Copy Destination:=.Range("A" & ini)
        .Range("J" & ini) = Date
    End With
    hop.Range("A" & fily).EntireRow.Delete xlUp     'se elimina de la base
End If
'se limpia el UF para iniciar una nueva búsqueda
Application.ScreenUpdating = True
Call Cancelar_Click
End Sub

6- El botón Salir: cierra el formulario. Al llamar al evento de cierre (QueryClose) recordar de volver a proteger la hoja si estuviese protegida. La clave es opcional. Además en este evento se limpia la columna auxiliar utilizada para ordenar la lista.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'se limpia la col auxiliar K
hop.Range("K:K").Clear
'hop.Protect
End Sub



Descargar el ejemplo con su código desde aquí.

Ver VIDEO 28.


martes, 14 de enero de 2020

Crear registros en hoja BASE con Userform

Son muchas las ocasiones en que necesitamos crear registros e ir acumulándolos en una hoja tipo BASE, de modo lineal. Es decir 1 registro por fila con cantidad de columnas según los campos utilizados.

En esta entrada vamos a ver cómo se realiza un formulario de REGISTRO. En entradas posteriores hablaremos de las opciones de Modificar y Eliminar registros de esa base.

El siguiente modelo, que servirá para cualquier tipo de actividad (Clientes, Proveedores, Alumnos, Pacientes, etc) tendrá campos del tipo:
- Label: que permiten mostrar información que luego será guardada en una columna. En ese caso se trata de un código o número de Identificación del registro.
- Textbox: son los campos para introducir valores o textos.
- ComboBox: un desplegable para seleccionar algún elemento de una lista.
- CommandButton: los botones de acción: Aceptar, Cancelar y Salir.


A partir de este modelo podremos agregar más controles y más columnas en la hoja que actualmente presenta este orden:

Por ejemplo, podríamos agregar un control Label para la fecha del día y más controles TextBox y ComboBox para ingreso o selección de datos.

Al pie encontrarán el enlace para descargar el formulario con su programación completa.

¿Qué vamos a tener en cuenta al momento de programar un formulario de REGISTRO? 

1- Los LABEL los utilizamos para mostrar información que luego puede ser guardada en el registro o no. Será el campo de la numeración automática de registro. Es decir un control que no será modificado por el usuario.
          Label7.Caption = Application.WorksheetFunction.Max(hop.Range("A:A")) + 1

2- Los TEXTBOX pueden ser controlados al momento del ingreso. Por ejemplo un control de valores numéricos:
         Private Sub TextBox7_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
          If KeyAscii < 48 Or KeyAscii > 57 Then
             KeyAscii = 0
             MsgBox "Error en el dato. Solo se aceptan valores numéricos."
          End If
         End Sub

También pueden ser convertidos como el campo nombre pasándolo a mayúsculas a medida que se registra en él:
         Private Sub TextBox4_Change()
         TextBox4 = UCase(TextBox4)
         End Sub

3- Al inicio del módulo Userform se declara una variable para guardar el nombre de la hoja BASE.
         Dim hob

Y desde el evento INITIALIZE del formulario se guarda en esa variable el nombre de la hoja de base. Esto servirá para mover la información a la hoja sin necesidad de activarla o seleccionarla previamente.
También en este evento de inicio se guarda en el Label el número del registro a crear.
         Private Sub UserForm_Initialize()
         Set hop = Sheets("PROVEEDORES")
         'otras instrucciones
         Label7.Caption = Application.WorksheetFunction.Max(hop.Range("A:A")) + 1
         End Sub

4- Al ACEPTAR se debe verificar que haya un dato clave, que en este caso es el nombre. Incluir en este control previo todos los campos que sean de caracter obligatorio. Y siempre es recomendable enviar un mensaje de confirmación.
         Private Sub Aceptar_Click()
         If TextBox4 = "" Then
              MsgBox "Falta el nombre del Proveedor.", , "Faltan Datos"
              TextBox4.SetFocus
              Exit Sub
         End If
         sino = MsgBox("¿Deseas guardar estos datos?", vbQuestion + vbYesNo, "Confirmar")
         If sino <> vbYes Then Exit Sub

5- Al realizar el pase a la hoja se tendrá en cuenta el tipo de dato. Los controles utilizados son del tipo 'texto' por lo tanto tendremos que convertirlos con las siguientes funciones:
   - VAL para guardar valores numéricos enteros.
   - CDBL  para guardar valores numéricos con decimales. Evaluar si el control se encuentra vacío.
'se pasa el registro. se busca la fila libre
fily = hop.Range("A" & Rows.Count).End(xlUp).Row + 1
hop.Range("A" & fily) = Label7.Caption
hop.Range("B" & fily) = TextBox4
hop.Range("C" & fily) = TextBox6
hop.Range("D" & fily) = ComboBox1.Text
hop.Range("E" & fily) = Val(TextBox7)
If TextBox11 <> "" Then hop.Range("F" & fily) = CDbl(TextBox11)
hop.Range("G" & fily) = TextBox9
hop.Range("H" & fily) = TextBox10

Como la columna de SALDO ya presenta un formato 'Moneda' con decimales y de color rojo para los negativos, que fue asignado desde la misma hoja para la columna completa no será necesario asignarle formato a la hora de guardar los campos.

6- Al finalizar el pase se limpiará el formulario para permitir un nuevo registro. Para esto se llamará a la misma macro que la del botón CANCELAR:
Call Cancelar_Click
End Sub

7- La macro para CANCELAR limpia los controles y actualiza nuevamente el número para el ID correlativo.
         Private Sub Cancelar_Click()
         'se limpian los textbox
         For Each ct In Me.Controls
             If TypeName(ct) = "TextBox" Then ct.Value = ""
         Next ct
         'se limpia el combobox
         ComboBox1.ListIndex = -1
         'se incrementa el ID del prov.
         Label7.Caption = Application.WorksheetFunction.Max(hop.Range("A:A")) + 1
         TextBox4.SetFocus
         End Sub

8- El botón SALIR cierra el Userform. Recordar que si se desprotegió (y quizás se mostró hoja oculta) habrá que volverla a proteger y ocultar. Esto se instruye en el evento de cierre:
         Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
         hop.Protect
         End Sub

Descargar el ejemplo con su código desde aquí

Ver VIDEO N° 27.