VBA & Macro Problems

How to Use Excel Worksheet Names in a VBA UserForm Drop-Down

Olivia Miller Oct 15, 2026 869 views

Question details

Populate a VBA UserForm drop-down menu with specific Excel worksheet names and submit entered form data to the selected worksheet.

How to Use Excel Worksheet Names in a VBA UserForm Drop-Down

Product: Excel
Device & OS: not provided
Scenario: Creating a data entry VBA UserForm where users select a target worksheet from a drop-down list to record their data.
Observed behavior: The form needs to list all worksheet names except a few excluded ones, write input data to the next empty row in the chosen sheet, and clear the input fields automatically.

Before you start

Ensure that your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) before testing the UserForm, otherwise your VBA code will be lost upon closing.

Solution 1Recommended

Populate UserForm Drop-Down and Append Data via VBA

Use the UserForm_Initialize event to load sheet names into a ComboBox, and a CommandButton click event to transfer the data.

To achieve this, you need to iterate through the workbook's sheets when the form opens, populate the dropdown list, and exclude any sheets where data entry is not permitted.

When the user clicks the save button, the script will identify the selected sheet, find the next available blank row, map the text fields to individual columns, and clear the form for the next submission.

Set up the UserForm controls

Open the VBA Editor (Alt + F11) and insert a UserForm. Add a ComboBox (name it 'ddlSheets') for the sheet selection. Add TextBoxes for data entry (e.g., 'txtName', 'txtAge', 'txtAddress') and a CommandButton for the save action.

Initialize the drop-down list

Double-click the UserForm background to open its code module. Use the 'Private Sub UserForm_Initialize()' event to loop through 'ThisWorkbook.Worksheets'. Add an IF statement to check the sheet name: if it is not 'Master', 'Results', or 'Help', add it to the dropdown using 'ddlSheets.AddItem'.

Find the selected sheet and empty row

Double-click your CommandButton to open its Click event. Use 'ddlSheets.Value' to identify the target worksheet. Find the first empty row on that sheet by using 'Cells(Rows.Count, 1).End(xlUp).Row + 1'.

Write input values to columns

Assign the values from your UserForm text boxes to the corresponding cells in the empty row. For example, 'TargetSheet.Cells(emptyRow, 1).Value = txtName.Value', and continue this pattern for the remaining text boxes across different columns.

Clear the form controls

After the data transfer code, clear each input field by setting its value to an empty string (e.g., 'txtName.Value = ""'). This resets the form so the user can immediately enter the next batch of data.

Populate UserForm Drop-Down and Append Data via VBA

Prevent Custom Typing: To force users to only select from the provided sheet names, change the 'Style' property of your ComboBox (ddlSheets) in the Properties window to '2 - fmStyleDropDownList'.

Run VBA Macros with WPS Office

Easily Manage VBA UserForms with WPS Spreadsheet

WPS Office offers comprehensive support for Excel macros and VBA code. You can create, edit, and run UserForms seamlessly within WPS Spreadsheet without needing to modify your existing VBA scripts.

1. Open your macro workbook: Launch WPS Spreadsheet and open your existing .xlsm file containing the UserForm.
2. Access the VBA Editor: Navigate to the Developer tab on the ribbon and click on 'Visual Basic' to open the built-in VBA Editor.
3. Run your UserForm: Press F5 or click the Run button within the editor to launch the UserForm and begin submitting data to your worksheets.

Fully compatible with Microsoft Excel .xlsm and .xlsb formatsBuilt-in VBA editor for creating and modifying UserFormsLightweight, fast execution of complex macro scriptsFree and intuitive interface for seamless data entry

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA drop-down list completely empty when the form opens?

This usually happens if the initialization code is placed in a standard module rather than the UserForm's code module. Ensure your loop code is written inside the 'Private Sub UserForm_Initialize()' event of the UserForm itself.

How can I exclude more worksheets from the UserForm drop-down?

You can expand the IF condition in your loop using the 'And' operator. For example: 'If ws.Name <> "Master" And ws.Name <> "Results" And ws.Name <> "Help" And ws.Name <> "NewSheet" Then'.

Can I automatically close the UserForm after submitting data?

Yes. If you want the form to close completely rather than clearing the text boxes for a new entry, simply add the command 'Unload Me' at the very end of your Save button's Click event code.

Excel & Data Problems