No Duplications

[srizki]

I have to type a an ID in column D of a worksheet called “restricted funds”, I have 13 sheets in that workbook and each sheet has in column D some IDs, I can not type a duplicate ID, in the that sheet. What is the best way to avoid no duplications. IDs are simply numbers.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Thanks

 

[erik.van.geit]

Hi,

This can be done using a worksheet_change event
You might want to take a look at "FIND" in the VBA helpfiles.

But why in the first place would you let the user enter a wrong number?

Is there any order in the numbers? Then you might consider to double click to add the next one.

Is this a good example?

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=192 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; WIDTH: 48pt; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>sheet 1</TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; WIDTH: 48pt; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" width=64>sheet 2</TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; WIDTH: 48pt; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" width=64>sheet 3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" x:num>3</TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" x:num>7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>2</TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" x:num>6</TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" x:num>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>4</TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" x:num>14</TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" x:num>9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>5</TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent"></TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" x:num>11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>10</TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent"></TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" x:num>12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent"></TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" x:num>13</TD></TR></TBODY></TABLE>

kind regards,
Erik

 

[srizki]

Thanks Eric,
Not every sheet has numbers in some order, they are somewhat in order. So for example on one sheet 5 digit numbers starting 88, on another sheet starting first two digits could be 87 or 82 etc. on this particular sheet I had to highlight all the sheets and find a number, if there was not found I used that number. As in your example for sheet two I have to see if the number has been used?.

How can I double click to generate the next number?

thanks Erik
"I love Jesus pbuh, and all prophets, before and after him".

 

[erik.van.geit]

Hi,

It's easy to loop through sheets and see if there is a certain number. I already suggested to use FIND.

But

if there was not found I used that number

what if the number IS found? Then you would try again, not? And if the next try is found again? This is not working in a logic way, but wasting time. For me it makes no sense to fill in an ID number at random

That's why I tried to ask for a "pattern": more specifically
"how do you define a good number?"

best regards,
Erik

PS: and what if you fill in a number on the other sheets, which is already on your “restricted funds” sheet?

 

[erik.van.geit]

forgot this

How can I double click to generate the next number?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Const col As Long = 4
 
If Target.Column <> col Then Exit Sub
 
Dim sht As Worksheet
Dim mmm As Long
 
Cancel = True
 
    For Each sht In ThisWorkbook.Worksheets
    mmm = Application.Max(mmm, sht.Columns(col))
    Next sht
 
Target = mmm + 1
 
End Sub

see
http://www.cpearson.com/excel/events.htm
and
http://www.tushar-mehta.com/excel/vba/vba-XL events.htm
http://www.mcgimpsey.com/excel/modules.html
TO INSTALL IN SHEET CODE WINDOW:
1. right click the "Name Tab" of sheet you want code to work in
2. Select "View Code" in drop down menu
3. VBE window will open ... paste code in and exit VBE

 

[srizki]

Hi Erik, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Sorry for the late reply.<o></o>
Someone has created this workbook to input some ID’s.<o></o>
ID’s are first put on the sheets and then assigned to new projects. I do not see any pattern of those ID’s, therefore I can not define a GOOD number, what I did notice is that numbers are somewhat in order. 88114, 88117, 88118, 88119, 88121 etc. why the numbers are missing I have no idea. I am trying to help this person to do the job correctly. <o></o>
<o> </o>
I have not tried the macro yet that you provided, but that is interesting to know to generate next number by clicking.<o></o>
<o> </o>
Thanks<o></o>
<o> </o>
Sohail<o></o>

 

[erik.van.geit]

I have to type a an ID in column D of a worksheet called “restricted funds”, I have 13 sheets in that workbook and each sheet has in column D some IDs, I can not type a duplicate ID, in the that sheet. What is the best way to avoid no duplications. IDs are simply numbers.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thanks

So we have tried to avoid typing in existing numbers, but your responses were: "not possible to know in advance". This would be code to disable duplicates in a certain range on all sheets.

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
'Erik Van Geit
'080624 2313
'no duplicates allowed on this sheet or other sheets
'within the range "RngAddress"
'multiple changes within range not allowed, unless clearing data
 
Dim sht As Worksheet
Dim dup As Range
Dim RngAddress As String
Dim msg As String
 
RngAddress = "D1:D" & Rows.Count     'range to check
 
    If Intersect(Target, Range(RngAddress)) Is Nothing Then Exit Sub
 
    If Target.Count > 1 Then
        If Application.CountA(Target) = 0 Then
        Exit Sub
        End If
    msg = "Please change only one item at a time in the range " & RngAddress
 
    Else
 
        For Each sht In ThisWorkbook.Worksheets
            If sht.Name <> ActiveSheet.Name Then
            Set dup = sht.Range(RngAddress).Find(What:=Target, LookIn:=xlFormulas, LookAt:=xlWhole)
                If Not dup Is Nothing Then
                msg = "The item """ & Target & """ can be found on sheet """ & sht.Name & """ in cell " & dup.Address(0, 0)
                Exit For
                End If
            Else
                If Application.CountIf(sht.Range(RngAddress), Target) > 1 Then
                msg = "The item """ & Target & """ is already in this list."
                Exit For
                End If
            End If
        Next sht
 
    End If
 
    If Len(msg) > 0 Then
        With Application
        .EnableEvents = False
        .Undo
        .EnableEvents = True
        End With
    MsgBox msg, vbCritical, "ERROR"
    End If
 
End Sub

 

[srizki]

Erik,

Thank you very much, this code is excellent, one more question, when I put this code in ThisWorkBook, it did not work, I copied and paste it on each sheet, is there a faster way to put the change-event code.

Thanks
Sohail

"No Jesus, No paradise."

 

[erik.van.geit]

when I read your initial question, you would only need to put the code in the sheetmodule "restricted funds"

should it work for all sheets?

 

[srizki]

You are right, I wanted it only for "restrictrd fund", then later I decided to use it on each sheet, so I copied and pasted on each sheet, it is excellent, it does not only say it is duplicate, it even says what sheet and in what cell it was used.

I can use this code on other work books too.

Thanks Erik.