How to Transfer a Selected Record with an Excel List Box Using VBA in WPS Spreadsheets
Question details
The user needs VBA code to select a value from an Excel list box and copy the matching row from one worksheet to another.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook has source records and a destination worksheet. The user wants an Excel list box to choose a record and transfer the corresponding row automatically.
- Observed behavior
- When the list box value is selected and the transfer button is clicked, the matching source row should be copied to the destination worksheet; unmatched values are handled with an error check.
Open the workbook in WPS Spreadsheets and ensure the Developer tab is visible so you can open the Visual Basic editor and insert controls. Save the workbook as a macro-enabled file such as .xlsm before adding the list box and VBA code.
Transfer a Selected List Box Record Using Find and ListObjects
Use this method when the source data is stored in a formatted Excel table and you want the selected list box value to locate the exact row.
This method populates the list box from the source table and uses the selected value as a lookup key. It is reliable when rows may be filtered, sorted, or contain non-contiguous data.
In WPS Spreadsheets, click the Developer tab, choose Visual Basic or press Alt+F11, then insert a UserForm. Name the form and controls so the code can reference them.
On the UserForm, insert a ListBox control named ListBox1 and a CommandButton named TransferData. Set the CommandButton Caption to Transfer.
In the UserForm Initialize event, set the ListBox RowSource to the source table column range, for example: ListBox1.RowSource = "Sheet2!A2:A100". Adjust the range to match the first column of your source table.
Double-click TransferData and paste the following code: Dim wsSource As Worksheet Dim wsDest As Worksheet Dim foundCell As Range Set wsSource = ThisWorkbook.Sheets("Sheet2") Set wsDest = ThisWorkbook.Sheets("Sheet1") Set foundCell = wsSource.ListObjects("DataTable").DataBodyRange.Columns(1).Find(What:=Me.ListBox1.Value, LookIn:=xlValues, LookAt:=xlWhole) If Not foundCell Is Nothing Then wsDest.Cells(1, 1).Resize(1, 2).Value = foundCell.Resize(1, 2).Value End If
Run the UserForm, select a record in the list box, click Transfer, and check the destination worksheet. Adjust sheet names, table name, and destination range if the row does not appear.
Transfer a Record by List Index When Source Data Is Not a Table
Use this alternative when the source data is a normal range and you load the list box with AddItem instead of a table column.
Automate List Box Record Transfers with WPS Spreadsheets VBA
Use the built-in VBA editor in WPS Spreadsheets to copy a selected list box record from one worksheet to another. Keep the familiar Excel VBA syntax while WPS Office handles the workbook, table, and control automation.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the .xlsx or .xlsm workbook, and display the Developer tab from File > Options > Customize Ribbon if it is hidden.
- 2. Create the UserForm and controls: Click Visual Basic or press Alt+F11, insert a UserForm, and add a ListBox and CommandButton to the form.
- 3. Add the transfer macro: In the button click event, use ListObjects("DataTable") and Find to locate the selected list box value in the source table.
- 4. Run and verify the result: Run the UserForm, choose a record, click the transfer button, and confirm that the matching row is written to the destination worksheet.
Frequently Asked Questions
Why does my VBA code return "Object required" when transferring a list box record?
This usually means an object name is wrong or missing. In the VBA editor, check that the UserForm, ListBox, worksheet, and table names exactly match the code, and use Set when assigning worksheet and range objects.
Can I copy the entire row instead of only two columns?
Yes. Replace Resize(1, 2) with a larger column count or use foundCell.EntireRow.Copy to copy the whole row to the destination sheet.
How do I handle a selected value that is not found in the source table?
Keep the If Not foundCell Is Nothing check. Add an Else block such as MsgBox "Record not found" to inform the user instead of leaving the destination unchanged.
Does WPS Spreadsheets support ListBox controls and VBA macros?
WPS Office for Windows supports VBA macros and UserForm controls such as ListBox in macro-enabled workbooks. Macro support can vary by platform, so test the workbook in your WPS Office version.
Which file format should I save the workbook in after adding the list box and VBA code?
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) or the WPS macro-enabled format so the VBA project and list box controls are preserved.
