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!

 

[Sidjer]

Hey, Howard!

I use the code to "paste" in sheet2 in a list (column B)... and it works, for a while (in a 2 sheet workbook).
I've inserted some values in Sheet1 (A:A) and in Sheet2 (C1) I type one of the values from Sheet1(A:A) and it "cut and paste" to Sheet2(B:B).. the problem is after I saved and closed the workbook and reopen to continue won't work. Just to test it I created a new 2 sheet workbook did the same thing and it works in the beginning.. after that will not (and I didn't even close the workbook, I just minimized it and worked on something else and than I returned to workbook)
Adjusted the code for my "Project" Workbook won't work at all.
I can't see the mistake I;m making! Please help!

Alex.

 

[L. Howard]

Hi Alex,

Did the code error out in any operation? Did you got an pop up error alert, and you clicked Debug or Quit.

If so, then you need to Enable Events on the sheet. From the sheet module run this snippet.

Sub XXX()
'Reset Events on worksheet if error occurs
Application.EnableEvents = True
End Sub

If there was an error, what code line was highlighted when you clicked Debug?

Perhaps you need to save as Macro Enabled workbook.xlsm.

Post the adjusted code.

Howard

 

[Sidjer]

https://www.dropbox.com/s/r4oenuitf8t3wt6/FBA Inventory_COPY.xlsm?dl=0

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Count > 1 Then Exit Sub
If Target <> Range("$C$5") Then Exit Sub


Dim LRow As Long
Dim aScan As Range 'sheet1 A:A list
Dim cScan As String 'sheet2 C1 scan-In


Application.EnableEvents = False


cScan = Sheets("FBAout").Range("C5")


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


With Sheets("Brazilian TC")


    LRow = Sheets("Brazilian TC").Cells(Rows.Count, "A").End(xlUp).Row
    
    Set aScan = Sheets("Brazilian TC").Range("A3:A" & LRow).Find(What:=cScan, _
                     LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                     SearchDirection:=xlNext, MatchCase:=False)


    If Not aScan Is Nothing Then
        aScan.Cut Sheets(FBAout).Range("F" & Rows.Count).End(xlUp)(2)
        '/B1/  aScan.Cut Sheets("Sheet2").Range("B1")
       
      ElseIf aScan Is Nothing Then
              MsgBox " No match found."
        '/B1/ Sheets("Sheet2").Range("B1").ClearContents
    End If
[C5].Select
End With


Application.EnableEvents = True


End Sub

 

[Sidjer]

mislabeled the sheets, still doesn't work!

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Count > 1 Then Exit Sub
If Target <> Range("$C$5") Then Exit Sub


Dim LRow As Long
Dim aScan As Range 'sheet1 A:A list
Dim cScan As String 'sheet2 C1 scan-In


Application.EnableEvents = False


cScan = Sheets("Sheet3").Range("C5")
    
If cScan = "" Then
    Exit Sub
  ElseIf IsNumeric(cScan) Then
    cScan = Val(cScan) '/ converts a "text" number to a value
  Else
    '/ is text and that is okay
End If


With Sheets(15)


    LRow = Sheets("Sheet15").Cells(Rows.Count, "A").End(xlUp).Row
    
    Set aScan = Sheets("Sheet15").Range("A3:A" & LRow).Find(What:=cScan, _
                     LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                     SearchDirection:=xlNext, MatchCase:=False)


    If Not aScan Is Nothing Then
        aScan.Cut Sheets("Sheet15").Range("F" & Rows.Count).End(xlUp)(2)
        '/B1/  aScan.Cut Sheets("Sheet2").Range("B1")
       
      ElseIf aScan Is Nothing Then
              MsgBox " No match found."
        '/B1/ Sheets("Sheet2").Range("B1").ClearContents
    End If
[C5].Select
End With


Application.EnableEvents = True


End Sub

 

[L. Howard]

Hi Alex,

Try this in the sheet module of sheet "FBAout"

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Count > 1 Then Exit Sub
If Target <> Range("$C$5") Then Exit Sub


Dim LRow As Long
Dim aScan As Range
Dim cScan As String

Application.EnableEvents = False

cScan = Sheets("FBAout").Range("C5")
    
If cScan = "" Then
    Exit Sub
  ElseIf IsNumeric(cScan) Then
    cScan = Val(cScan) '/ converts a "text" number to a value
  Else
    '/ is text and that is okay
End If

   LRow = Sheets("Brazilian TC").Cells(Rows.Count, "A").End(xlUp).Row
    
   Set aScan = Sheets("Brazilian TC").Range("A3:A" & LRow).Find(What:=cScan, _
                 LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                 SearchDirection:=xlNext, MatchCase:=False)

    If Not aScan Is Nothing Then
        aScan.Cut Sheets("FBAout").Range("F" & Rows.Count).End(xlUp)(2)
         
      ElseIf aScan Is Nothing Then
        MsgBox " No match found."
        
    End If
    
[C5].Select
Application.EnableEvents = True

End Sub



Sub xxx()
'run to reset Events if error
Application.EnableEvents = True
End Sub

 

[Sidjer]

thank you it works!

I may sound crazy but, I'll give it a try....
In matter to add more "Strings" in the same Sheet("FBAout") from "Ranges" in different Sheets, I have to create a "Sub" for each set of "Strings" and "Ranges" or all can be defined in 1 "Sub"?

If my VBA language is as good as my English, then I'll try a different explanation:
How to add 10-20 more Cells like "C5" and Columns like "F" in Sheet("FBAout") that will cut data from all others sheets?!
Amin.

Sorry Howard, but I,m trying to get it done and this is the one of the "LAST STEPS"!

 

[L. Howard]

How to add 10-20 more Cells like "C5" and Columns like "F" in Sheet("FBAout") that will cut data from all others sheets?

I will need to give that some thought.

Questions?

When completed you will want to be able to cut data from column A AND/OR column B on the sheets like "Brazilian TC", "CN1", "CN2", "Cn3", "Brindle" and all the others?

And you will be cutting actual scan values like A FEB2416 S005, A FEB2417 M006, A FEB2418 L007, etc. not just 1, 2, 3, etc.?

So you may want cells C2 to C22 to be scan-in cells and maybe column E to X to be the cut data to columns? Would that work for you?

With a scan-in C cells and column cut to for each sheet?

Howard

 

[Sidjer]

You got it right.

The whole picture about my project: as you figured out already, this will be a "Inventory Manager" where daily will be Scanned In and sorted by category and sizes products with barcodes I generate... and Scanned Out the product when is sold(with date stamp to indicate the sale date) and daily/weekly sales statistics.

In the All the Sheets that you helped to create will be stored products that are currently in stock and in Sheet(FBAout) will be Tables with all the products(by category) that was sold with a offset stampdate column so will be able to sort it later by sales date.

If you think all these can be done differently in a more logically way... let me know

Thank You!

 

[L. Howard]

Then there will be 54 columns for sheet names, plus a column for each name for a date.

That is 104 columns B to DD for names and dates. And a scan-in column will be in A. Will that work to start with?

Howard

 

[L. Howard]

You got it right.

If you think all these can be done differently in a more logically way... let me know

Thank You!

Hi Alex,

There are a few thing with your workbook that can be done to "clean" it up and abide with some general conventions. I am working on them now, sorry to take as long as I have.

Howard