How to Create Custom Excel Forms with Date Pickers and Drop-Down Lists in WPS Office
Question details
The user wants to create a custom Excel registration form in WPS Office with a birthday date picker, a church drop-down list, an automatic district lookup, and a status drop-down list.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Membership or registration tracking that requires form controls and dependent fields.
- Observed behavior
- Excel's built-in data form cannot be customized with calendars, dependent fields, or extra controls, so a custom VBA UserForm is required.
Enable the Developer tab in WPS Spreadsheets before opening the Visual Basic Editor. If you save VBA code, use a macro-enabled workbook format such as .xlsm.
Create a custom UserForm with date pickers and ComboBox controls in WPS Spreadsheets
Use this when you need form controls beyond Excel's built-in data form, including a birthday calendar, dependent church/district fields, and a status dropdown.
The built-in Excel data form cannot be customized with calendars, dependent fields, or additional controls. A custom UserForm gives you full control over the registration form layout and stores submitted values in a structured worksheet table.
In WPS Spreadsheets, click File > Options > Customize Ribbon, enable Developer, and then click Developer > Visual Basic.
In the Visual Basic Editor, click Insert > UserForm. Add Labels and TextBoxes for basic fields such as Full Name and Birthday, and resize the form for the registration layout.
For the birthday field, add a TextBox and set its format or validation code to accept a date. If your WPS VBA version includes the Date and Time Picker control or MonthView control, add that control instead.
Click View > Toolbox, add two ComboBox controls, name them cboChurch and cboStatus. Populate cboStatus with Registered, Dropped, Transferred, Deceased, and Returnee, and set the church ComboBox RowSource to the church list range.
Add a district TextBox or Label. In the cboChurch_Change event, use VLOOKUP or a worksheet lookup to pull the matching district from a church-district table into that field.
Add a CommandButton named btnSave. In its Click event, write the form values to the next empty row of a structured worksheet table using ListObject.DataBodyRange and clear the form after saving.
Use worksheet data validation for a quick drop-down-only registration sheet
Use this when you do not need a pop-up calendar and want a faster, formula-based form directly on the worksheet.
Build a Custom Excel Registration Form in WPS Office
Use WPS Spreadsheets and its Visual Basic Editor to create a structured registration form with a birthday date field, church and status drop-down lists, automatic district lookup, and a save button that writes to a worksheet table.
- 1. Enable macro tools in WPS Spreadsheets: Click File > Options > Customize Ribbon, enable Developer, and create or open a macro-enabled workbook.
- 2. Design the registration UserForm: Click Developer > Visual Basic, then Insert > UserForm, and arrange fields for name, birthday, church, district, and status.
- 3. Add dropdowns, lookup code, and save button: Add ComboBox controls for church and status, a TextBox for district, and a CommandButton that saves values into the data table.
- 4. Run and test the form: Press F5 in the Visual Basic Editor to launch the form, submit a test row, and confirm the record is written to the worksheet table.
Frequently Asked Questions
Can I customize Excel's built-in data form with a calendar and dropdowns?
No. The built-in Excel data form cannot be customized with calendars, dependent fields, or extra controls. You need to create a custom UserForm in the Visual Basic Editor.
How do I add a date picker to a WPS Excel UserForm?
Open the Visual Basic Editor from Developer > Visual Basic and insert a UserForm. Add a TextBox for the birthday and apply date validation or formatting. If your WPS VBA version includes Date and Time Picker or MonthView, you can add that control instead.
How can a church ComboBox automatically show the district?
Store church and district pairs in a lookup table. In the church ComboBox change event, use Application.WorksheetFunction.VLookup or another lookup method to populate the district field automatically.
How do I save UserForm values to a worksheet table?
Add a CommandButton to the UserForm. In its Click event, use the next empty row in the worksheet table, either through ListObject.DataBodyRange or by finding the next blank row, and write the TextBox and ComboBox values into the correct columns.
Does WPS Office support VBA UserForms and .xlsm files?
Yes. WPS Office supports VBA editing and macro-enabled workbooks in compatible versions. Enable the Developer tab and save the workbook as a macro-enabled .xlsm file to keep the code.
