Page 1 of 2 12 LastLast
Results 1 to 10 of 18

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

This is a discussion on Scan Barcode, but dont enter data if the barcode has already been entered within the Excel Questions forums, part of the Question Forums category; Hi, I have been working on a spreadsheet for sometime now, and many of the answers I have found have ...

  1. #1
    New Member
    Join Date
    Jun 2013
    Posts
    19

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

    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 ???

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    8,346

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

    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?
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    New Member
    Join Date
    Jun 2013
    Posts
    19

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

    Quote Originally Posted by JoeMo View Post
    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.

  4. #4
    MrExcel MVP
    Join Date
    May 2009
    Posts
    8,346

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

    Quote Originally Posted by Aerotech View Post
    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.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  5. #5
    New Member
    Join Date
    Jun 2013
    Posts
    19

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

    Thank you.

  6. #6
    MrExcel MVP
    Join Date
    May 2009
    Posts
    8,346

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

    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.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  7. #7
    New Member
    Join Date
    Jun 2013
    Posts
    19

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

    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.

  8. #8
    New Member
    Join Date
    Jun 2013
    Posts
    19

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

    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.

  9. #9
    New Member
    Join Date
    Jun 2013
    Posts
    19

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

    Quote Originally Posted by JoeMo View Post
    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.

  10. #10
    MrExcel MVP
    Join Date
    May 2009
    Posts
    8,346

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

    Quote Originally Posted by Aerotech View Post
    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
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com