VBA & Macro Problems

Fix VBA Code That Adds UserForm Data to an Excel Table in WPS Spreadsheets

Steven Parker Nov 13, 2026 869 views

Question details

Correct VBA code that writes UserForm entries to an Excel table and automatically generates the next patient ID.

Fix VBA Code That Adds UserForm Data to an Excel Table in WPS Spreadsheets

Product: WPS Spreadsheets
Device & OS: not provided
Scenario: The user is building a data entry UserForm in WPS Spreadsheets and wants submitted patient information to be appended to a Contacts Excel table with an auto-generated Patient ID.
Observed behavior: The current VBA code uses the wrong objects, so attempts to find the last row and write data to the Excel table fail or produce incorrect behavior.

Before you start

Open the VBA editor with Alt+F11 and verify that the worksheet contains an Excel table named Contacts with a PatientID column before changing any code.

Solution 1Recommended

Rewrite the submit code to use a ListObject and ListRows.Add

Use this solution when your UserForm code fails because it treats the Excel table like a normal worksheet range or cannot append a new table row.

Excel tables are ListObject objects in VBA. Instead of finding the last worksheet row, add a ListRow directly to the table and write values through the new ListRow range.

The Patient ID should be generated from the maximum existing PatientID value in the table, not from a text box or a last-row calculation.

Open the UserForm submit code

In WPS Spreadsheets, press Alt+F11 to open the VBA editor. In the Project Explorer, double-click the UserForm and then double-click the Submit or Save button to open its Click event.

Declare worksheet and ListObject variables

At the top of the Click event, declare a Worksheet variable and a ListObject variable: Dim ws As Worksheet, tbl As ListObject. Then set ws = ThisWorkbook.Worksheets("Patients") and set tbl = ws.ListObjects("Contacts").

Add a new row to the Excel table

Replace last-row code with Dim newRow As ListRow: Set newRow = tbl.ListRows.Add. This inserts a new row at the end of the table and returns a ListRow object you can write to.

Write UserForm values into the new row

Use the newRow.Range cells to assign values, such as newRow.Range(1, 2).Value = Me.txtFirstName.Value. Use the VBA Date function for the current date instead of asking the user to enter it.

Generate the next Patient ID automatically

Add the following: Dim nextID As Long: nextID = Application.WorksheetFunction.Max(tbl.ListColumns("PatientID").DataBodyRange) + 1. Then write nextID to the PatientID column in the new row.

Validate required fields before saving

Reject empty names or names with only periods or invalid special characters, store trimmed values back into the text boxes, and call the add-row code only after validation passes.

Rewrite the submit code to use a ListObject and ListRows.Add

Patient records save correctly: The UserForm now appends records directly to the Excel table and creates the next patient ID automatically.

Solution 2

Separate validation and ID generation into reusable procedures

Use this approach to keep the submit button readable and reuse patient validation or ID calculation elsewhere in the VBA project.

WPS Spreadsheets VBA fix

Fix Excel VBA UserForm Table Code in WPS Spreadsheets

Use WPS Spreadsheets to edit and run the corrected VBA UserForm code. It supports Excel table objects, ListRows.Add, and the familiar VBA editor, so the patient data entry form can append rows and generate IDs without switching tools.

1. Open the macro-enabled workbook in WPS Spreadsheets: Launch WPS Office, open the .xlsm workbook that contains the UserForm, and enable macros if prompted.
2. Open the VBA editor: Press Alt+F11 to open the VBA editor and select the UserForm module from the Project Explorer.
3. Replace the table write logic: Locate the Submit button code and replace the last-row worksheet logic with a ListObject, ListRows.Add, and newRow.Range assignments.
4. Add the automatic Patient ID calculation: Use Application.WorksheetFunction.Max on the PatientID DataBodyRange plus 1 and assign it to the new row.
5. Run and test the form: Run the UserForm, enter a patient name, and confirm the record is appended to the Contacts table with the next ID and today's date.

Supports Excel table objects and ListRows.Add in VBA macrosOpens and saves .xlsm and .xlsx files with Microsoft Excel compatibilityFree, lightweight Office suite with a familiar ribbon and VBA editorRuns patient data entry macros without requiring Microsoft Office

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA code fail when I try to find the last row of an Excel table?

Excel tables are ListObject objects, not plain worksheet ranges. Declare a ListObject variable and add a row with tbl.ListRows.Add instead of using ws.Cells(ws.Rows.Count, ...).End(xlUp).

How can I automatically create the next Patient ID in a UserForm?

Use Application.WorksheetFunction.Max(tbl.ListColumns("PatientID").DataBodyRange) + 1 and write that value to the new table row.

Should I ask the user to enter the current date in the form?

No. Use the VBA Date function in the submit code to populate the date column automatically and avoid input errors.

Does WPS Spreadsheets support the same VBA UserForm code as Excel?

Yes. WPS Spreadsheets supports VBA UserForms, ListObject tables, and the same ListRows.Add method, so you can open and edit the macro-enabled workbook in WPS Office.

What should I validate before saving a patient record?

Require first and last names, reject entries made only of periods or invalid special characters, and store trimmed values back into the text boxes before appending the table row.

Excel & Data Problems