?
Solved

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

Posted on 2016-07-18
4
Medium Priority
?
22 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 54

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 54

Accepted Solution

by:
Ryan Chong earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

839 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