Botón Guardar para Formularios VBA en Excel (Generar Código Cli_001)

Introduction

In this tutorial, you will learn how to create a "Save" button for a VBA form in Excel. This guide will help you save data from your form into an Excel table and generate unique client codes automatically. By the end of this tutorial, you will be able to implement a functional and efficient data entry system using VBA Macros.

Step 1: Generate a New Client Code

• Open your VBA editor in Excel.
• Create a function to generate a unique client code that increments automatically.
• Use the following code snippet:

Function GenerateClientCode() As String
    Dim lastCode As Integer
    lastCode = Application.WorksheetFunction.Max(Sheets("YourSheetName").Range("A:A"))
    GenerateClientCode = "CLI_" & Format(lastCode + 1, "0000")
End Function

• Replace "YourSheetName" with the actual name of your sheet.

Step 2: Copy Macro from the Page

• Copy your existing macro code from the provided resources or from the video.
• Paste it into your VBA editor and make necessary adjustments to suit your form's needs.

Step 3: Declare Public Variables

• At the top of your module, declare any public variables that will be used throughout your macros.
• Example:

Public clientData As Range
Public clientCode As String

Step 4: Declare an Array for Data Storage

• Create an array to temporarily hold data from the form.
• Example:

Dim formData(1 To 5) As String

• Adjust the size of the array based on how many fields you have in your form.

Step 5: Loop to Extract Numbers

• Implement a loop to extract numbers from your form inputs.
• Use a loop to iterate over the array and assign values.

For i = 1 To UBound(formData)
    formData(i) = Me.Controls("TextBox" & i).Value
Next i

Step 6: Find the Highest Number in the Column

• Use Excel functions to find the maximum number in your designated column.
• Ensure this is done before generating the new client code.

Step 7: Program the Save Button

• Add an event handler for your "Save" button.
• Inside the button click event, execute the following actions:

Private Sub btnSave_Click()
    clientCode = GenerateClientCode()
    ' Insert data into table
    Sheets("YourSheetName").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = clientCode
    For i = 1 To UBound(formData)
        Sheets("YourSheetName").Cells(Rows.Count, i + 1).End(xlUp).Offset(1, 0).Value = formData(i)
    Next i
End Sub

• This will save the client code and the form data into the next available row.

Step 8: Insert Data into Excel

• Ensure that every time you save the form, the data is appended to the next empty row in your table.
• Use the Offset method to target the correct cells.

Step 9: Create Rows for Each Data Entry

• If your form includes multiple entries, you may need to create a new row for each entry.
• Adjust your code to accommodate the number of entries as needed.

Step 10: Program Additional Features

• If your form includes additional features such as addresses or options, make sure to program those buttons similarly.
• Utilize similar methods to capture their data and insert it into the respective columns.

Conclusion

You have now created a fully functional "Save" button for your VBA form in Excel. This system allows you to save data efficiently while generating unique client codes automatically. As a next step, consider exploring additional VBA features to enhance your forms further, such as data validation and error handling.