Scan Barcode, but dont enter data if the barcode has already been entered

[Aerotech]

Hi, I have been working on a spreadsheet for sometime now, and many of the answers I have found have been on this forum, but now I can't seem to find a solution for exactly what I want to do. The first data to be entered into the sheet will be scanned eg, Unique Barcode, Drawing Number, Part Description and serial number. as each part goes through its process it has to be baked from anything between 2 - 16 Hours. what I am looking for is something that will recognise the unique ID if it has already been entered and instead of entering it in the new cell, jump back to the first entry and Highlight it so the user wont need to re-enter all of the data again, and also save on multiple entries of the same part, as this can be up to 10 - 15 bake cycles. Thanks in advance Sorry for the single paragraph, but either my enter key has stopped working or I need to do something in settings ???

 

[Aerotech]

Hi Joe, Still have a similar problem.

If I scan the barcodes XXXX7001XXXX, XXXX7002XXXX through to 7010 and then rescan say 7002 from a blank cell it will find the ID with 7002 and highlight it, which is exactly what I wanted, the problem comes if you were to accidently scan another code while still on 7002, 7002 is replaced by the code you accidently scanned, Ideally if the code that was accidently scanned was already in the list it would jump to that cell and highlight it, or if the code had not yet been entered it would move down to the next empty cell and enter it there.

Many Thanks,
Gary.

 

[JoeMo]

Hi Joe, Still have a similar problem.

If I scan the barcodes XXXX7001XXXX, XXXX7002XXXX through to 7010 and then rescan say 7002 from a blank cell it will find the ID with 7002 and highlight it, which is exactly what I wanted, the problem comes if you were to accidently scan another code while still on 7002, 7002 is replaced by the code you accidently scanned, Ideally if the code that was accidently scanned was already in the list it would jump to that cell and highlight it, or if the code had not yet been entered it would move down to the next empty cell and enter it there.

Many Thanks,
Gary.

See if I understand what you are saying. If you try to enter a code that's already present, that code is removed and the earlier cell with that code already entered is selected - and that's what you want. Now, with that earlier cell selected, if you accidentally enter another code it overwrites the one that was there - you don't want that.

If that's correct, then I would suggest that immediately after selecting the earlier cell I add a message box informing the user that the code already exists and when the user clicks OK the next available empty cell is selected (that would be the same cell the user initially entered the duplicate ID in) for entry of the next bar code. Is that acceptable?

 

[Aerotech]

Hi Joe
If I scan a barcode from a blank cell and that code already exists it will find and go to the cell with that code which is great and just what I needed, However, If while still on that that cell a different barcode is scanned or accidently scanned then the original data is replaced with the new scanned data, and that is not what I want.

I think the problem maybe down to the fact that the scanner issues a Carriage Return after scanning, which is great if entering a batch of 20 - 30 new ID's, but not so good on cell's with data already present.

Ideally it would be good if a cell with data already present rejected any new scanned data unless it matches with the data that is already there, If not it would go off and look for matching data in the rest of the cells, If no matches are found it would enter the new data in the first empty cell.

It would be useful to be able to scan and find duplicate data from anywhere in the Column, including already populated cells as at the moment the user may have to scroll down through hundreds of entries to a blank cell and scan from there each time.

Does that make sense, or even possible?

Thanks once again for you help,

Regard's,
Gary.

 

[JoeMo]

Hi Joe
If I scan a barcode from a blank cell and that code already exists it will find and go to the cell with that code which is great and just what I needed, However, If while still on that that cell a different barcode is scanned or accidently scanned then the original data is replaced with the new scanned data, and that is not what I want.

I think the problem maybe down to the fact that the scanner issues a Carriage Return after scanning, which is great if entering a batch of 20 - 30 new ID's, but not so good on cell's with data already present.

Ideally it would be good if a cell with data already present rejected any new scanned data unless it matches with the data that is already there, If not it would go off and look for matching data in the rest of the cells, If no matches are found it would enter the new data in the first empty cell.

It would be useful to be able to scan and find duplicate data from anywhere in the Column, including already populated cells as at the moment the user may have to scroll down through hundreds of entries to a blank cell and scan from there each time.

Does that make sense, or even possible?

Thanks once again for you help,

Regard's,
Gary.

You didn't indicate if you find the solution I proposed in post #12 acceptable.

 

[Aerotech]

Hi Joe, Sorry about that, Yes what you proposed would work just fine, it would stop the overwrite, and from the description will be a "Search" and "Data Entry" system rolled into one.

Regard's,
Gary.

 

[Aerotech]

Hi Joe
Will be away for a week from late Thursday Night until Friday 12th, so if you don't hear from me please don't think I am ignoring you, look forward to trying out the code you proposed.

Regard's,
Gary.

 

[JoeMo]

Hi Joe, Sorry about that, Yes what you proposed would work just fine, it would stop the overwrite, and from the description will be a "Search" and "Data Entry" system rolled into one.

Regard's,
Gary.

This will prevent the overwrite and direct the user to the next empty cell for new bar code entry. I added a selection_change procedure that will require the user who wants to edit an existing entry in column A to say 'Yes' to edit it which you may want to modify after you try it.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Variant, lR As Long
If Not Intersect(Target, Range("A:A")) Is Nothing And Target.Row >= 3 Then
    lR = Range("A" & Rows.Count).End(xlUp).Row
    If Target.Row = lR Then
        On Error Resume Next
        n = Application.Match(Target.Value, Range(Cells(3, 1), Cells(Target.Row - 1, 1)), 0)
        On Error GoTo 0
        If Not IsError(n) Then
            Application.EnableEvents = False
            Cells(n + 2, 1).Select
            MsgBox "That code has already been entered in cell " & ActiveCell.Address(1, 1) & vbCrLf & vbCrLf & "Click OK and enter a new code."
            Target.Select
            Target.ClearContents
            Application.EnableEvents = True
        End If
    Else
        If Application.CountIf(Range("A3:A" & lR), Target.Value) > 1 Then
            MsgBox "That code has already been entered." & vbCrLf & vbCrLf & "Click OK and enter a new code."
            Application.EnableEvents = False
            Target.Select
            Target.ClearContents
            Application.EnableEvents = True
            Application.MoveAfterReturn = True
        Else
            Application.EnableEvents = False
            Cells(lR + 1, 1).Select
            Application.EnableEvents = True
        End If
    End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim nR As Long, Ansr As Integer
If Not Intersect(Target, Range("A:A")) Is Nothing And Target.Row >= 3 Then
    If ActiveCell.Row <> Range("A" & Rows.Count).End(xlUp).Row + 1 Then
        If Not IsEmpty(Target) Then
            Ansr = MsgBox("Click 'Yes' to edit this cell, 'No' to go to the next empty cell for code entry.", vbYesNo, "Edit Cell?")
            If Ansr = vbNo Then
                Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
            Else
                Application.MoveAfterReturn = False
            End If
        End If
    End If
End If
End Sub

 

[Aerotech]

Hi Joe
That's Great, I haven't had chance to try it yet as we are busy packing, but I will try to get back to you before we go.

Regard's,
Gary.

 

[Aerotech]

Hi Joe
Managed to try the code and it looks to be just the job, will have more time to intergrate it into the rest of the worksheet when I get back from holiday.

Many thanks for your help, will get back to you and let you know how things are going.

Regard's,
Gary.