Barcode scanning with Excel

[Chief1904]

My office is trying to go paperless with our hunting program and issue ID cards with bar codes. I'm trying to have a barcode scanner be able to scan the ID card and pull up their information in excel or access. How can I do this? My boss doesn't want to spend $12,000 on a full setup of software and hardware. Any help would be great!

 

[L. Howard]

Hi again Alex,

Maybe you have all the info and ideas you need to do your project. If so, then please let this thread know it is closed.

However, I have an even further version of your workbook, in progress, which does the following, in general terms.

All scan-in entries are made on the Dashboard sheet into a single cell very much like the individual sheets (where the input cell is surrounded by yellow).

The destination sheet is selected from a drop down just above the scan-in cell. The b-code is scanned in and a button clicked and that b-code goes to the proper sheet in the column for S, M, L etc. If it is a duplicate then it is listed in a separate column along with a date on that sheet. Ditto for all the other sheets.

For the FBAout sheet, you scan into the same single cell on Dashboard sheet and click a different button and the FBAout sheet is populated the same as if you were doing it right on the FBAout sheet, b-code to the proper column with a date, except it is done from the Dashboard.

Once FBAout is fully populated, you can go to it and parse the sales info as you normally would. I would point out that this would be a opportunity to do the color groups to their own separate sheet, following the Dashboard theme.

So, let me know if you are interested OR not.

Howard

 

[centoro]

anyone can help me out.

i need make a database in excel and using barcode scanner for checking the stock.
how i can input the barcode to excel automatic when i use the scan?
i only need the 3 coloumn. there are barcode,name,quantity.

anyone can help me out? how i should do this since im not good in excel..

thanks

 

[L. Howard]

Hi centoro,

Post your request as a new post instead of on the tail of an old post like this one. A new post will get many more views and have a better chance for resolution.

Be more specific about what you want to happen and where you want it to happen, citing specific rows, columns and cells and what should occur and when. So when you scan something into cell A2, then this or that should do this or that.

How is the scan-in supposed to check the stock? Where is the stock and is it stock numbers, stock names, stock quantities. What sheet is the stock on, what sheet is the scan on. Is the stock referring to the stock market or inventory of items?

With all the above info, do a step by step description of what should happen once the scan-in occurs to the completion of the results you want.

You can copy and paste a small example of you worksheet in your post for clarification along with you explanation.

Howard

 

[Mikeali786]

[FONT=&quot]I have an attendance workbook where students come in and scan their barcodes. So I was wondering if there is a way to add an input box so when a student scans their barcode it shows the barcode number as well as their name until the next student comes and scans their code. Also the number from the barcode goes to a certain cell then goes down one cell for the next student.[/FONT]

 

[L. Howard]

Is there a specific reason to have in input box to display the just-scanned barcode and name? That can be done in two specified cells on the worksheet.

For instance, if the scan-in cell is A2, and once a scan is made the scanned barcode is posted in column F, in the first empty cell. The student name appears in the same row in column G. (A date/time stamp could also be posted in column H)

It would be normal to delete the barcode number in A2 once the barcode and name are posted to F & G. columns, to await the next student scan-in.

To do the above scenario would require that the scan-in to A2 reacts the same as you typing the number into A2 and hitting ENTER. A Change_Event macro would then process the barcode to post the numbers and names in columns F and G, (H).

Also requires a list of student barcode numbers with the names in two columns somewhere off screen or on a separate "student roster" sheet.
Do you have a current workbook with a specific data layout or are you in the building process?

Howard

 

[Mikeali786]

https://drive.google.com/file/d/0B1_5qbMaS9CLd3NXWkxFZEVZOG8/view?usp=sharing

That is what I have setup so far. I have a "Students List" where all the students information goes this includes where each student gets their own barcode to match their own student id. Next I have a tab for each grade and here it shows if the student is Tardy, Excused, Unexcused, Present, or there's No School. Student Report is just a quick way to pull up an individual students information. Then the last tab is "w1a" there's suppose to be 30 of them but I haven't made all of them yet. Each tab is for the about of weeks class in held which connect directly to all the grade tabs. Once I student comes they scan their barcode then the number is put into A3 then their name gets removed from the "Absent" list and they get a "P" next to their name in the grade they belong in tab.

I want to make it where when they scan their barcode they can confirm that it went through just by looking at it that it has been recorded. I kinda have that now but I would like to make it just that so they don't see anything else on the workbook.

 

[L. Howard]

Okay, you'll have a pretty full workbook when completed.

The best I can figure is that you want a "graphic" notification upon a student scanning in his/her ID number.

Give this a try in the sheet module of Sheet "w1a". When an ID is entered in column A, the code checks the Student List sheets column A, from row 4 and on down, for a valid ID. A message box is the visual response for either a good ID number (along with the students name) or bad ID number.

If there are going to be several sheets like "w1a" that will require this kind of macro, then it should be rewritten for the ThisWorkbook module. Then one macro will work all the "wXx" sheets.

Howard

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
  
If Intersect(Target, Range("A:A")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
 
Dim LRow As Long
Dim aRng As Range, rngFnd As Range
Dim myFnd As String

myFnd = Target

If myFnd = "" Then
    Exit Sub
  ElseIf IsNumeric(myFnd) Then
    myFnd = Val(myFnd) '/ converts a "text" number to a value
  Else
    '/ is text and that is okay
End If

With Sheets("Student List")

    LRow = Sheets("Student List").Cells(Rows.Count, "A").End(xlUp).Row
    
    Set rngFnd = Sheets("Student List").Range("A4:A" & LRow).Find(What:=myFnd, _
                     LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                     SearchDirection:=xlNext, MatchCase:=False)

    If Not rngFnd Is Nothing Then
         MsgBox rngFnd & " is good ID for " & rngFnd.Offset(, 2)
        
      Else
        MsgBox "No match found."
        Target.ClearContents
        Target.Activate
        Exit Sub

    End If

End With

End Sub

 

[mjeakman]

Howard. I am looking for the same thing Chief was. Your file is no longer availabe on Dropbox. Can it be updated? I wouold really appricated it. MJEakman/Houston

Hi Chief,

Have a look-see at this workbook I wrote for a school book distribution, as a possible solution for your scanning need. Of course it's the concept you will be looking at, not the school book distribution to students.

https://www.dropbox.com/s/blu89n0i6yfs4be/Scan Method Demo.xlsm

What you will see:

Sheet 1 is the scanned data read out. Note the Student ID no.s in column L to be used for the demo. The button clears the data.
Type a student ID no. in column A, hit enter, see the data that is returned. (this would probably be your hunter barcode scan)
Enter student no. 789 and see that no student has been assigned that number yet.
Don't confuse column D Barcode with anything to do with your barcode scanning, you will probably be scanning the hunter barcodes into column A, as mentioned, and returning pertinent data from sheet 2.

Sheet 2:

Instead of student information, a compiled data base pertinent to your hunters.

Regards,
Howard

 

[L. Howard]

Hi MJEakman,

This may be what you are looking for.

Using two sheets, on sheet 1, A2 is the scan-in cell and the returned data for that ID scan-in is displayed in the row, columns C thru J.

Sheet 2 has the data from which the scan-in retrieves the info.


Howard

Excel 2012
	A	B	C	D	E	F	G	H	I	J
1	ID Scan		Name	Address	City	State	Rifle	Pistol	Bow/Arrow	Member since
2	10004		Hunter-5	5555	Houston	TX	0	0	X	2014
Sheet1

Cell Formulas
Range	Formula
C2:J2	{=VLOOKUP(A2,Sheet2!$A$2:$I$6,{2,3,4,5,6,7,8,9},0)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Excel 2012
	B
14
Sheet2

 

[L. Howard]

Sheet 2 like this, did not post correctly in last post.

Howard

Excel 2012
	A	B	C	D	E	F	G	H	I
1	ID Number	Name	Address	City	State	Rifle	Pistol	Bow/Arrow	Member since
2	10000	Hunter-1	1111	Denver	CO		X	X	1966
3	10001	Hunter-2	2222	Ft. Worth	TX	X	X	X	1999
4	10002	Hunter-3	3333	NY City	NY	X			2011
5	10003	Hunter-4	4444	San Diego	CA		X		2003
6	10004	Hunter-5	5555	Houston	TX			X	2014
Sheet2

Cell Formulas
Range	Formula
A1	ID Number
A2	10000
A3	10001
A4	10002
A5	10003
A6	10004
B1	Name
B2	Hunter-1
B3	Hunter-2
B4	Hunter-3
B5	Hunter-4
B6	Hunter-5
C1	Address
C2	1111
C3	2222
C4	3333
C5	4444
C6	5555
D1	City
D2	Denver
D3	Ft. Worth
D4	NY City
D5	San Diego
D6	Houston
E1	State
E2	CO
E3	TX
E4	NY
E5	CA
E6	TX
F1	Rifle
F3	X
F4	X
G1	Pistol
G2	X
G3	X
G5	X
H1	Bow/Arrow
H2	X
H3	X
H6	X
I1	Member since
I2	1966
I3	1999
I4	2011
I5	2003
I6	2014