VBA & Macro Problems

How to Search for UserForm TextBox Values Across Worksheets in Excel VBA

Kushani Nimanthika Oct 12, 2026 870 views

Question details

The user needs an Excel VBA macro that searches multiple worksheets for a part number entered into a UserForm TextBox and warns if duplicates are found.

Search for UserForm TextBox Values Across Worksheets in Excel VBA

Product: Excel
Device & OS: not provided
Scenario: Data entry and validation using VBA UserForms across a multi-sheet workbook.
Observed behavior: The macro should fetch the value from TextBox1, check that it is not blank, search all designated sheets, and notify the user with the exact cell address to inspect if a duplicate entry exists.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that your VBA project includes a UserForm containing a TextBox (named TextBox1) and a CommandButton.

Solution 1Recommended

Implement a VBA Search and Duplicate Warning Script

Use a CommandButton click event to read the TextBox value, iterate through all worksheets using the Find method, and prompt the user upon detecting duplicate part numbers.

To accomplish this, you must loop through your target worksheets and apply the Range.Find method on each sheet. By capturing the click event of a button on the UserForm, you can halt the duplicate entry and let the user visually inspect the existing record.

Access the UserForm Code

Press Alt + F11 to open the VBA Editor, locate your UserForm in the Project Explorer, right-click it, and select 'View Code'.

Create the Button Click Event

Select your CommandButton from the top-left dropdown in the code window to generate a 'Private Sub CommandButton1_Click()' event.

Read and Validate the TextBox

Inside the click event, declare a variable and assign it using 'Trim(Me.TextBox1.Value)'. Add an If statement to check if the value is blank, and exit the sub if it is.

Loop Through Worksheets to Find Duplicates

Use a 'For Each ws In ThisWorkbook.Worksheets' loop. Inside the loop, use 'ws.Cells.Find(What:=yourValue)' to search for the TextBox value.

Prompt the User on Match

If a match is found (Not matchRange Is Nothing), display a MsgBox with the worksheet name and cell address. Ask if they want to inspect it. If yes, use 'ws.Activate', 'matchRange.Select', and 'Me.Hide' to let the user review the duplicate.

Implement a VBA Search and Duplicate Warning Script

Handling the User Choice: By utilizing 'Me.Hide', the UserForm temporarily disappears so the user can interact with the selected duplicate row. You can add logic to show the form again once the inspection is complete.

Efficiently Handle Macros with WPS Office

Create and Manage VBA UserForms with WPS Spreadsheets

WPS Spreadsheets provides robust support for VBA macros, enabling you to build complex UserForms, automate multi-sheet searches, and validate your data directly within a lightweight workspace.

1. Open Your Macro Workbook: Launch WPS Spreadsheets and open your .xlsm file containing the UserForm.
2. Access the VBA Editor: Navigate to the 'Developer' tab on the ribbon and click on 'Visual Basic' or 'WPS Macro' to access the built-in VBA editor.
3. Edit Your UserForm Logic: Locate your UserForm code and paste or edit your multi-sheet search and duplicate-warning script.
4. Run and Validate: Click 'Run' or trigger the UserForm from your worksheet to ensure the duplicate part number warnings function perfectly.

Full support for standard Excel VBA macros and UserFormsSeamlessly compatible with Microsoft Excel .xlsm filesLightweight application with high processing speed for complex scriptsFree to download with an intuitive, familiar interface

microsoft office alternative - wps office

Frequently Asked Questions

Why should I use Trim() when reading TextBox values in VBA?

Using the Trim() function removes any accidental leading or trailing spaces entered by the user. This ensures that the Find method searches for the exact text characters and doesn't fail due to invisible spaces.

How do I hide the UserForm to inspect the worksheet?

You can use the command 'Me.Hide' (or 'UserFormName.Hide') in your VBA code. This temporarily hides the dialog box, granting you full access to navigate and inspect the selected worksheet cells behind it.

Can the VBA Find method search across all worksheets automatically?

No, the Find method only operates on a specific range or a single worksheet at a time. To search an entire workbook, you must construct a loop (such as 'For Each ws In Worksheets') and run the Find command on each sheet sequentially.

Excel & Data Problems