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

Aerotech

New Member
Joined
Jun 29, 2013
Messages
19
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 ???
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
What column is the ID in and starting in what row? Will new ID entries always be made in the cell immediately below the last entry? Is the ID the first piece of info entered for that ID?
 
Upvote 0
What column is the ID in and starting in what row? Will new ID entries always be made in the cell immediately below the last entry? Is the ID the first piece of info entered for that ID?

Hi, Thank you for the quick reply, The Starting cell will be A3, and all subsequent entries will be made from there ie A4,A5,etc, and yes it is also the first piece of information entered, B3 downwards has the drawing number, and from that using VLOOKUP C3 downwards has the part description.
 
Upvote 0
Hi, Thank you for the quick reply, The Starting cell will be A3, and all subsequent entries will be made from there ie A4,A5,etc, and yes it is also the first piece of information entered, B3 downwards has the drawing number, and from that using VLOOKUP C3 downwards has the part description.
Thanks for the clarification. Need to go offline now, but I will try to give you something in a few hours if there are no responses in the interim.
 
Upvote 0
Assuming the barcode entry in column A triggers a change event, the code below should remove a duplicate code entry and take the user to the cell that already contains that ID. This is a worksheet module not a standard module.
To install the code:
1. Right-click the worksheet you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Variant
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    On Error Resume Next
    n = Application.Match(Target.Value, Range(Cells(4, 1), Cells(Target.Row - 1, 1)), 0)
    On Error GoTo 0
    If Not IsError(n) Then
        Application.EnableEvents = False
        Cells(n + 3, 1).Select
        Target.ClearContents
        Application.EnableEvents = True
    End If
End If
End Sub
This code will run automatically whenever a new ID is entered in a column A cell below A3.
 
Upvote 0
Hi Joe, Thank you for taking the time to write the code, I do have a slight problem as I already have one code running, maybe I should have gone into greater detail when I first posted.

The code I already have running automatically puts the date in Column "F3" when selected and the Time in "G3", these are the "Start date and time", the code also puts the Date in column "K3" and Time in "L3" which is the "End Time", This shows when the part went into the oven and the date and time it came out.

The Code I have running I probably got from here, and is pasted below, Would it be very difficult to integrate the codes together?, Sorry to be a pain.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Cell As Range
Dim sDateCol As String
Dim sTimeCol As String

'if the cell(s) changed was not in col A or Col F then nothing to do
If Intersect(Target, Union(Range("F2:F" & Rows.Count), Range("K2:K" & Rows.Count))) Is Nothing Then GoTo End_Sub

' disable event
Application.EnableEvents = False

On Error GoTo Error_Handler

' for every modified cell
For Each Cell In Target

' if the modified cell is not in col 1 (A) or at col 6 (F) then go to next changed cell
If ((Cell.Column <> 6) And (Cell.Column <> 11)) Then GoTo Next_Cell

' if changed cell is at col 1 (col A)
If (Cell.Column = 6) Then
sDateCol = "F"
sTimeCol = "G"

Else ' default position that changed cell is at col 6 (col F)
sDateCol = "K"
sTimeCol = "L"

End If

' same row but date col
With Cells(Cell.Row, sDateCol)

.Value = Date


End With

' same row but time col
With Cells(Cell.Row, sTimeCol)

.Value = Time


End With

Next_Cell:
Next Cell

End_Sub:
Application.EnableEvents = True
Exit Sub

Error_Handler:
MsgBox Err.Description
GoTo End_Sub
End Sub

Kind Regard's,
Gary.
 
Upvote 0
I think I need to explain the full function of this spread sheet, I didn’t do so with the first post as I thought it might be a bit cheeky to ask so many questions at the same time, but I now realise that in order for anyone to help they need to know how I would like the whole thing to work, So.., Here Goes.

As I explained we have to bake items for production from anywhere between 2 – 16 Hours, in order to do this effectively and not over bake items I would like to create a spread sheet that will make the process easy and almost fool proof for the users, and also add a visual indication of when a part is due to be taken out of the oven by means of a three colour indication in the “Elapsed Time” cell, Ranging from “Green” at the start, “Amber” at Midpoint and “Red” when time is up, It would also be great if the cell could flash “Red” if the time goes over say 15 mins.

During the process of manufacture a part can be baked up to 15 times depending on it routing, so the first question I asked was a way to look for duplicate entries of the unique ID which will be scanned from a barcode, this way if the ID has been already scanned and baked it will jump to the first entry, rather than creating multiple entries and using more space on the sheet, It will also serve as a point to tally the number of bakes the item has had and show how many Hours the item has actually spent in an oven, we have occasions where an Item requires just 2 Hours, but gets left in the oven over the weekend period, Not at all good for the product, and hundreds of items are baked every week.

I will try to explain the layout of the worksheet and what I would like to try and achieve.

Cell A3 onwards = Unique ID – Scanned from Barcode
Cell B3 onwards= Drawing Number – Scanned from Barcode
Cell C3 onwards = Part Description – Filled in by VLOOKUP
Cell D3 onwards = Serial Number – Scanned from Barcode
Cell E3 onwards = Oven Number – Manual Entry
Cell F3 onwards = Date item was put into oven – Automatically entered when clicked
Cell G3 onwards = Time item was put into oven – Automatically entered when F3 is clicked
Cell H3 onwards = Will be a manual entry of how many Hours the part requires (2 – 16 Hours)
Cell I3 onwards = Will indicate what time the part is due out derived from G3 + H3
Cell J3 onwards = the elapsed time, Which I would like to show Green, Amber, Red
Cell K3 onwards = Date Removed from oven – Automatically entered when clicked
Cell L3 onwards = Time Removed from oven - Automatically entered when K3 is clicked
Cell M3 onwards = Show the number of hours for that bake period – Clear with next Bake
Cell N3 onwards = Add and store the total Hours for all bake operations of that part
Cell O3 onwards = show the total amount of times that part has been put in the oven
As interim bakes are sometimes required.

Another feature which has sprung to mind, is that when Cell “K3” is clicked indicating that the part has been removed from the oven it stops and clears the elapsed time counter and enters the value into Cell “N3”.

See what I mean about feeling a bit cheeky, but I guess the more information the better.

Regard’s,
Gary.
 
Upvote 0
Assuming the barcode entry in column A triggers a change event, the code below should remove a duplicate code entry and take the user to the cell that already contains that ID. This is a worksheet module not a standard module.
To install the code:
1. Right-click the worksheet you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Variant
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    On Error Resume Next
    n = Application.Match(Target.Value, Range(Cells(4, 1), Cells(Target.Row - 1, 1)), 0)
    On Error GoTo 0
    If Not IsError(n) Then
        Application.EnableEvents = False
        Cells(n + 3, 1).Select
        Target.ClearContents
        Application.EnableEvents = True
    End If
End If
End Sub
This code will run automatically whenever a new ID is entered in a column A cell below A3.

Hi Joe
Have tried running the code on a blank worksheet, with 10 UID's, it works great but I did notice that if I select Cell A3, and scan a code it wont enter that code in A3, instead it jumps to Cell "A5", from there on I can scan the 10 UID's I have created, and then rescan any one of them and it will jump back to the cell with that code in and highlight it which is great, but I found that if while on that cell I scan the barcode again it will clear the original entry and enter any code I scan, Is there any way of stopping this from happening?

Many Thanks,
Gary.
 
Upvote 0
Hi Joe
Have tried running the code on a blank worksheet, with 10 UID's, it works great but I did notice that if I select Cell A3, and scan a code it wont enter that code in A3, instead it jumps to Cell "A5", from there on I can scan the 10 UID's I have created, and then rescan any one of them and it will jump back to the cell with that code in and highlight it which is great, but I found that if while on that cell I scan the barcode again it will clear the original entry and enter any code I scan, Is there any way of stopping this from happening?

Many Thanks,
Gary.
See if this fixes things.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Variant
If Not Intersect(Target, Range("A:A")) Is Nothing And Target.Row > 3 Then
    On Error Resume Next
    n = Application.Match(Target.Value, Range(Cells(4, 1), Cells(Target.Row - 1, 1)), 0)
    On Error GoTo 0
    If Not IsError(n) Then
        Application.EnableEvents = False
        Cells(n + 3, 1).Select
        Target.ClearContents
        Application.EnableEvents = True
    End If
End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top