28 - 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")        

'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: sobrescribe 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        'opcional

End Sub

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

Acceso al  VIDEO 28.