Create an Excel VBA Search Form with Save and Next Controls in WPS Office
Question details
The user needs to build a VBA UserForm that searches records by Department and Kaizen Status on the General Consultation Environment worksheet, populates fields such as Idea Headline and Analysis Department Deadline, saves edits back to the current record, and moves to the next matching record.
- Product
- WPS Spreadsheets (Excel VBA)
- Device & OS
- not provided
- Scenario
- The user manages consultation records and wants a form-based search, edit, save, and record navigation workflow instead of scrolling the worksheet.
- Observed behavior
- The user needs a sample workbook or exact layout guidance to create reliable search, save, and next-record VBA event code.
Before you start, open the target workbook in WPS Spreadsheets and confirm the General Consultation Environment sheet name and column headers, especially Department, Kaizen Status, Idea Headline, and Analysis Department Deadline, match the names used in the VBA code. Also enable the Developer tab so you can open the VBA editor and run the UserForm.
Build and Code the VBA Search Form in WPS Spreadsheets
Use this when you want to create the complete UserForm search tool from the WPS Office VBA editor with Search, Save, and Next controls.
The form relies on a consistent table layout: a Department filter, Kaizen Status filter, editable fields such as Idea Headline and Analysis Department Deadline, and a clear current-row pointer. Give each control a logical name so the event code can reliably read and write values.
In WPS Spreadsheets, open the workbook that contains the General Consultation Environment worksheet. Press Alt+F11 to open the VBA editor, then click Insert > UserForm.
Add two ComboBoxes for Department and Kaizen Status, TextBoxes for the editable data fields such as Idea Headline and Analysis Department Deadline, and three CommandButtons named Search, Save, and Next. Use the Properties window to set clear names like cmbDepartment, cmbStatus, btnSearch, btnSave, and btnNext.
In the Search button click event, read cmbDepartment.Value and cmbStatus.Value, loop through the data range on Sheets("General Consultation Environment"), compare the Department and Kaizen Status columns, and populate the text boxes with the first matching row.
In the Save button click event, write the edited text box values back to the current row using the same column indexes. In the Next button click event, continue the search from the row after the current match and populate the form with the next matching record.
Prepare an Anonymized Sample Workbook to Validate Column Mapping
Use this when the search or save code returns the wrong cells or you need to share the workbook for exact VBA debugging.
Create Your Excel-Compatible VBA Search Form with WPS Office
WPS Spreadsheets includes a built-in VBA editor and supports Excel VBA UserForms, so you can build a Department and Kaizen Status search form with Save and Next controls and keep working in the familiar .xlsm/.xlsx environment.
- 1. Enable the Developer tab: In WPS Spreadsheets, click File > Options > Customize Ribbon, enable Developer, and click OK to show the Developer tab.
- 2. Create your UserForm: Click Developer > Visual Basic or press Alt+F11, then insert a UserForm and add ComboBox, TextBox, and CommandButton controls.
- 3. Add the search, save, and next event code: Use the worksheet name General Consultation Environment and control names from your form to write or paste the event procedures for filtering, saving edits, and moving to the next record.
- 4. Run and assign the form: Press F5 to run the UserForm from the VBA editor, or insert a button on the worksheet and assign the form-opening macro so users can open it from the sheet.
- 5. Save as a macro-enabled workbook: Use File > Save As and choose the .xlsm format so the VBA UserForm and macros are retained.
Frequently Asked Questions
Does WPS Spreadsheets support Excel VBA UserForms?
Yes. WPS Spreadsheets includes a VBA editor at Developer > Visual Basic or Alt+F11, and it supports UserForms with standard controls, but you should test the workbook in both WPS Office and Excel if you plan to share it.
How do I make the Search button find records by Department and Kaizen Status?
Use the Search button click event to read the Department and Kaizen Status combo box values, then loop through the data rows on the General Consultation Environment sheet and compare the corresponding columns until a match is found.
How does the Save button write changes back to the worksheet?
The Save button click event writes the current TextBox values to the active record's row by using the same column indexes or named range cells that were used to populate the form.
Why does my form not show the next record when I click Next?
Store or find the current row number, then start the next search from the next row. Also make sure the Department and Kaizen Status values are updated and that the worksheet data is sorted or filtered only if your code expects that.
What should I include in an anonymized sample workbook for VBA help?
Keep the worksheet name, header row, column order, named ranges or table names, the UserForm controls, and a few non-confidential rows that show the expected Search, Save, and Next workflow.
