VBA & Macro Problems

Write Excel UserForm Checkbox Values to the Next Available Table Column

Emma BrownEmma Brown Nov 12, 2026 868 views

Question details

Write the values for selected UserForm checkboxes into the next available maintenance columns of a new row in an Excel table.

Write Excel UserForm Checkbox Values to the Next Available Table Column
Product
Microsoft Excel VBA UserForm and ListObject table
Device & OS
not provided
Scenario
A FleetMaintTable table records fleet maintenance procedures, and multiple selected procedures must be saved as values on one new table record.
Observed behavior
The macro must add one table row and locate the first empty maintenance cell for each selected checkbox value without creating a separate row per checkbox.
Before you start

Confirm that the worksheet is named "Fleet Maint Records" and the Excel table is named "FleetMaintTable" in Table Design > Table Name. Identify the first maintenance column in the table and update the column name in the code if your layout differs.

Solution 1Recommended

Add one table row and write selected checkboxes to empty maintenance columns

Use this approach when several selected UserForm procedures belong to one fleet maintenance record and must be stored across the same new table row.

Place this code in a Submit or Save button event, not in each checkbox Click event. A button event ensures Excel adds only one ListObject row, then writes every selected procedure into the next available maintenance column on that row.

1
Create a Submit button on the UserForm

In the VBA editor, open the UserForm, add a CommandButton from the Toolbox, and set its Name property to "cmdSaveMaintenance". Double-click the button to open its Click event procedure.

2
Add the table-based VBA procedure

In the cmdSaveMaintenance_Click procedure, paste this code and replace "Maintenance 1" with the actual header of your first maintenance column: Private Sub cmdSaveMaintenance_Click() Dim ws As Worksheet Dim tbl As ListObject Dim newRow As ListRow Dim firstMaintCol As Long Dim colIndex As Long Set ws = ThisWorkbook.Worksheets("Fleet Maint Records") Set tbl = ws.ListObjects("FleetMaintTable") 'Add one new record for all selected procedures. Set newRow = tbl.ListRows.Add 'Find the first maintenance column by its table header. firstMaintCol = tbl.ListColumns("Maintenance 1").Index colIndex = firstMaintCol If Me.PMCheckBox1.Value Then Do While colIndex <= tbl.ListColumns.Count And _ Len(newRow.Range.Cells(1, colIndex).Value) > 0 colIndex = colIndex + 1 Loop If colIndex > tbl.ListColumns.Count Then MsgBox "No empty maintenance columns are available in this table row.", vbExclamation Exit Sub End If newRow.Range.Cells(1, colIndex).Value = "ENGINE OIL CHANGED" colIndex = colIndex + 1 End If If Me.PMCheckBox2.Value Then Do While colIndex <= tbl.ListColumns.Count And _ Len(newRow.Range.Cells(1, colIndex).Value) > 0 colIndex = colIndex + 1 Loop If colIndex <= tbl.ListColumns.Count Then newRow.Range.Cells(1, colIndex).Value = "FILTER REPLACED" Else MsgBox "No empty maintenance columns are available in this table row.", vbExclamation End If End If End Sub

3
Add each remaining checkbox condition

Below the PMCheckBox2 block, add a matching If block for every maintenance checkbox. Use the checkbox control name after "Me." and write its required text, such as "TIRE ROTATION" or "BRAKE INSPECTION", into newRow.Range.Cells(1, colIndex).

4
Run the UserForm and verify the new record

In Excel, open the UserForm, select two or more procedure checkboxes, and click Save. Open the "Fleet Maint Records" worksheet and verify that FleetMaintTable contains one new row with the selected values in consecutive empty maintenance columns.

Add one table row and write selected checkboxes to empty maintenance columns
One row is created per save action: Using tbl.ListRows.Add adds a row inside the Excel table, so table formatting, formulas, and structured references can expand with the new record.
Work with spreadsheet tables and macros

Manage Fleet Maintenance Tables in WPS Spreadsheet

WPS Spreadsheet provides a familiar spreadsheet workspace for maintaining fleet records, organizing maintenance columns, and working with compatible Excel table files. You can open the workbook, review its table layout, and use supported macro workflows where available in your WPS Office edition.

  1. 1. Open the fleet workbook: Launch WPS Spreadsheet, select Open, and choose the Excel workbook that contains the Fleet Maint Records worksheet.
  2. 2. Review the maintenance table layout: Select the FleetMaintTable range and confirm that the maintenance columns have clear header names, such as Maintenance 1, Maintenance 2, and Maintenance 3.
  3. 3. Edit or add a maintenance record: Click the next record row in the table and enter selected maintenance procedures in the available maintenance cells.
  4. 4. Save in Excel-compatible format: Choose Menu > Save As and select Excel Workbook (*.xlsx) to keep the file compatible with Microsoft Excel.
Organize fleet procedures across dedicated maintenance columns in a spreadsheet table.Open and edit Microsoft Excel .xlsx and .xls fleet maintenance workbooks.Use a familiar ribbon-based interface for table and worksheet management.Lightweight Office suite for reviewing and updating maintenance records.
microsoft office alternative - wps office

Frequently Asked Questions

Why should the code run from a Save button instead of a checkbox Click event?

Each checkbox Click event runs separately. If each event calls ListRows.Add, Excel creates multiple table rows. Put the code in one CommandButton Click event so all checked values are written to the same new ListObject row.

How do I find the first maintenance column without using a fixed worksheet column number?

Use the table header with tbl.ListColumns("Maintenance 1").Index. This returns the column position inside FleetMaintTable, so the code still works if the worksheet columns move.

Can the checkbox caption be written instead of hard-coded maintenance text?

Yes. Replace a value such as "ENGINE OIL CHANGED" with Me.PMCheckBox1.Caption. This is useful when the checkbox Caption property already contains the exact procedure name to save.

What happens when every maintenance column is already occupied?

The code checks whether colIndex exceeds tbl.ListColumns.Count. If no column is available, it displays a message instead of writing outside the table range.

How can I save vehicle details in the same new table row?

After Set newRow = tbl.ListRows.Add, assign values using table column names, for example: newRow.Range.Cells(1, tbl.ListColumns("Vehicle ID").Index).Value = Me.txtVehicleID.Value.