VBA & Macro Problems

How to Prevent Duplicate Serial Numbers in an Excel Dropdown List

Aamir Naveed Akram Sep 28, 2026 869 views

Question details

The user needs a method to prevent the same serial number from being selected more than once from a dependent dropdown list in a workbook.

How to Prevent Duplicate Serial Numbers in an Excel Dropdown List

Product: Excel
Device & OS: not provided
Scenario: Users are selecting serial numbers from a dependent dropdown list in a specific column, and there is a risk of duplicate data entry.
Observed behavior: The current setup allows users to select the same serial number multiple times from the dropdown, leading to inaccurate and duplicated data records.

Before you start

Before applying VBA macros to your workbook, ensure you save your file as an Excel Macro-Enabled Workbook (.xlsm) so your automated scripts are not lost upon closing.

Solution 1Recommended

Use a VBA Macro to Prevent and Clear Duplicates

Implement a Worksheet_Change event macro to actively monitor the dropdown column, warn the user, and instantly clear the cell if a duplicate serial number is selected.

This is the most robust method for strictly preventing duplicates. While Data Validation is great for manual typing, dropdown lists can sometimes bypass validation rules, making VBA the ideal solution for absolute data integrity.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Microsoft Visual Basic for Applications (VBA) editor.

Access the Worksheet Code

In the Project Explorer pane on the left, double-click the specific worksheet where your dependent dropdown list is located (e.g., Sheet1).

Insert the Worksheet_Change Code

Change the top-left dropdown in the code window to 'Worksheet' and the right dropdown to 'Change'. Add VBA logic to count occurrences of the Target value in your specific column (like Column R). If the count exceeds 1, trigger a MsgBox to warn the user and use Application.Undo or Target.ClearContents to remove the duplicate.

Save as a Macro-Enabled Workbook

Go to File > Save As, and change the file format to Excel Macro-Enabled Workbook (*.xlsm) to ensure your VBA code runs the next time you open the file.

Use a VBA Macro to Prevent and Clear Duplicates

Enable Macros: Make sure that macro security settings in your Trust Center allow macros to run, otherwise the duplicate prevention script will not execute.

Solution 2

Highlight Duplicates Using Conditional Formatting

If you prefer a solution without VBA coding, use Conditional Formatting to visually flag duplicate serial numbers in red as soon as they are selected.

Manage Data Effectively with WPS Spreadsheet

Prevent Duplicates Easily in WPS Office

WPS Office Spreadsheet fully supports Conditional Formatting, Data Validation, and VBA macros, allowing you to seamlessly manage dependent dropdown lists and prevent duplicate entries exactly as you would in Microsoft Excel.

1. Open Your Workbook in WPS Office: Launch WPS Spreadsheet and open your existing Excel workbook containing the dropdown lists.
2. Apply Conditional Formatting: Highlight your target column, navigate to the Home tab, and select 'Conditional Formatting' > 'Highlight Cells Rules' > 'Duplicate Values' to easily flag repeated selections.
3. Add Automation via VBA: Go to the Developer tab, click on 'VBA Editor', and paste your Worksheet_Change event code to strictly block duplicates.
4. Save Your Work: Click 'Save As' and select the '.xlsm' format to preserve your duplicate-prevention macros seamlessly.

Fully compatible with Microsoft Excel (.xlsx and .xlsm) files.Advanced Data Validation and Conditional Formatting to restrict incorrect data entry.Built-in VBA and Macro support for automating complex spreadsheet tasks.Lightweight, fast, and features a familiar, easy-to-use interface.

microsoft office alternative - wps office

Frequently Asked Questions

Can I prevent duplicates using Data Validation instead of VBA?

Yes, you can select your column, go to Data > Data Validation, choose 'Custom', and enter a formula like =COUNTIF($R$2:$R$100, R2)<=1. However, be aware that users can sometimes bypass this by copying and pasting data, which is why VBA is considered more secure.

Why isn't my duplicate-prevention VBA macro working when I reopen the file?

This usually happens if the file was saved as a standard .xlsx workbook, which strips away macros. You must 'Save As' an Excel Macro-Enabled Workbook (.xlsm). Also, check your Trust Center settings to ensure macros are enabled upon opening the file.

Does this duplicate check work for dependent dropdown lists?

Yes. Both Conditional Formatting and VBA macros monitor the final value that populates the cell. It does not matter whether the value was typed manually, selected from a standard dropdown, or chosen from a dependent dropdown list.

Excel & Data Problems