Solved

Excel Workbook - Prevent Value Duplicates in one column all worksheets of a workbook

Posted on 2016-07-18
4
13 Views
Last Modified: 2016-07-18
I have a excel workbook with many rows of data.  There are also many worksheets - each worksheet relates to a week of data.  In column E of all worksheets combined there cannot be duplicates.  So I want the workbook to prevent me from entering a duplicate value.  A prompt saying "Duplicate Found" or the cell going red would be good.

I'd imagine this would be done on a change event with VB of a Macro Enabled Workbook
0
Comment
Question by:iits
  • 2
  • 2
4 Comments
 
LVL 50

Expert Comment

by:Ryan Chong
ID: 41718062
do you have a sample can upload here so we can try to diagnose the issue?
0
 

Author Comment

by:iits
ID: 41718066
0
 
LVL 50

Accepted Solution

by:
Ryan Chong earned 500 total points
ID: 41718097
just a quick test...

in each of the worksheet, try add:
Private Sub Worksheet_Change(ByVal Target As Range)
    Call checkDuplicate(Target)
End Sub

Open in new window

then create a public module and add:
Public Sub checkDuplicate(v As Range)
    isDup = False
    
    If v.Cells.Count > 1 Then Exit Sub
    
    If v.Value = "" Then
        isDup = False
        Exit Sub
    End If
    
    For Each w In Worksheets
        Set Rng = w.Range("E:E").Find(What:=v.Value, _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng Is Nothing Then
            If (w.Name = v.Parent.Name And v.Row = Rng.Row And v.Column = Rng.Column) Then
                Set Rng = w.Range("E:E").Find(What:=v.Value, _
                        After:=v, _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
            End If
            isDup = Not (w.Name = v.Parent.Name And v.Row = Rng.Row And v.Column = Rng.Column)
            If isDup Then
                MsgBox "Duplicate value found at worksheet: [" & w.Name & "], cell (" & Rng.Row & ", " & Rng.Column & ")", vbCritical, "Error"
                Exit Sub
            End If
        Else
            isDup = False
        End If
    Next
End Sub

Open in new window

UniqueExample_b.xlsm
0
 

Author Closing Comment

by:iits
ID: 41718102
Billiant! Thank You
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question