Results 1 to 12 of 12
  1. #1
    Lounger Lexi's Avatar
    Join Date
    Aug 2013
    Posts
    25
    Thanks
    20
    Thanked 1 Time in 1 Post

    Scanning into Excel

    Hi guys,

    A while back a member had provided me with some marvelous vba code I needed to generate custom barcodes for patient ID account numbers. It works great in that the barcodes are printed to sheet labels and applied to the patients belongings, equipment, and documents. I would like to use it in the reverse manner where I can now scan the barcodes back into excel to verify and track the items. I have written the code to do this which works fine.

    The problem I am having is that while I can scan the barcode into a cell, I cannot create an event until I press enter to run additional code. The result is like typing last character into a cell and having the cursor sit there still flashing waiting for more input. I am looking for a way to just scan an item following with the code running automatically.

    TIA.
    Lexi

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,179
    Thanks
    47
    Thanked 983 Times in 913 Posts
    You mean this post?
    Can you post the code so we can work out what is happening?

    cheers, Paul

  3. #3
    Lounger Lexi's Avatar
    Join Date
    Aug 2013
    Posts
    25
    Thanks
    20
    Thanked 1 Time in 1 Post
    Hi Paul,

    A sample would be something like this
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        If Target.Value = "455687889095" Then
            Range("B1") = "John Doe"
        End If
    End If
    End Sub
    The worksheet_Change never runs because the cursor remains flashing after the last number in the cell. If I press enter than the event runs. I am attempting to run the code with the press of only the scan button.

    Thanks,
    Lexi

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,179
    Thanks
    47
    Thanked 983 Times in 913 Posts
    Seems the scan doesn't result in a worksheet change. Is the scan button in Excel?

    cheers, Paul

  5. #5
    Lounger Lexi's Avatar
    Join Date
    Aug 2013
    Posts
    25
    Thanks
    20
    Thanked 1 Time in 1 Post
    Hi Paul,

    It is the button on the scan gun.

  6. #6
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,179
    Thanks
    47
    Thanked 983 Times in 913 Posts
    So your scanner is a keyboard impersonator. Are you able to tell your scanner to add an Enter after the barcode? If not you will need to find another way to enter the data, maybe by running a macro that accepts the barcode then adds the data to the spreadsheet and does any post processing required.

    cheers, Paul

  7. The Following User Says Thank You to Paul T For This Useful Post:

    Lexi (2015-02-14)

  8. #7
    Lounger Lexi's Avatar
    Join Date
    Aug 2013
    Posts
    25
    Thanks
    20
    Thanked 1 Time in 1 Post
    Paul,
    Your are correct in that the scanner is the input device. There are no buttons on the scanner and there is no scanner configuration to add the enter after the scan. I could manually type in the barcode numbers (Patient Account Number) but that is the purpose of the scan

    Thanks,
    Lexi

  9. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 648 Times in 591 Posts
    Lexi,

    The easiest way to do what you are looking for is to use a form with a textbox that accepts the barcode scan as an input. The textbox has the event trapping you need to perform an action after the scan.

    When you scan a barcode the read numbers are placed sequentially into the textbox as if you were typing them in using a keyboard and not all pasted at the same time as you may think. Each entered number triggers the TextBox1_KeyDown event. All the code has to do is monitor the length of the input. When the textbox value reaches the length of the number of characters in your barcode then you can run code or a macro via a conditional statement.

    Below is a sample form named Scanform with a textbox named textbox1 and the needed code. It assumes a 12 character barcode is read.
    Scanform.png

    Place the following code in the forms module:
    Code:
    Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If Len(TextBox1) = 11 Then  'CHANGE 11 TO NUMBER ONE LESS THAN TOTAL CHARACTER LENGHT
        'RUN SOME CODE OR MACRO    
        MsgBox "Macro has just run" 'REMOVE FROM YOUR CODE
        Scanform.Hide
    End If
    End Sub
    
    
    Private Sub UserForm_Activate()
        TextBox1.Value = ""
        TextBox1.SetFocus
    End Sub
    HTH,
    Maud

  10. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    Lexi (2015-02-14),Paul T (2015-02-15)

  11. #9
    Lounger Lexi's Avatar
    Join Date
    Aug 2013
    Posts
    25
    Thanks
    20
    Thanked 1 Time in 1 Post
    Paul and Maud,

    Thank you guys for your input(s). I was hoping for a way and this makes perfect sense. I was wondering how the input was interpreted in the cell as it quickly scrolls across as if the numbers were being typed when I do a scan.

    I will give it a try back at work. This is such a great forum!

    Lexi

  12. #10
    Lounger Lexi's Avatar
    Join Date
    Aug 2013
    Posts
    25
    Thanks
    20
    Thanked 1 Time in 1 Post
    Paul and Maud,

    I created a form as you indicated, placed the code in the form module, and added the line in the conditional statement to run my macro. My code statements were in the worksheet change sheet module so I had to move it to a regular module which does not seem to work now. I am getting many object errors. I will have to play with it.

    Thanks for all your help
    Lexi

  13. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 648 Times in 591 Posts
    Lexi,

    When you moved your code to a standard module, you may have to add an object identifier (ex. Activesheet.cells(1,2)). I think I would put the code back in the Worksheet_Change event and trigger it by placing something like Worksheets("Sheet1").Range("A1")= TextBox1.value in place of calling your code in the conditional statement on the form.

    Form Module:
    Code:
    Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If Len(TextBox1) = 11 Then  'CHANGE 11 TO NUMBER ONE LESS THAN TOTAL CHARACTER LENGHT
        Worksheets("Sheet1").Range("A1")= TextBox1.value 
        Scanform.Hide
    End If
    End Sub
    
    
    Private Sub UserForm_Activate()
        TextBox1.Value = ""
        TextBox1.SetFocus
    End Sub
    This will place the contents of the completed textbox into a cell on your sheet and trigger the Worksheet_Change event hence your code.

    HTH,
    Maud

  14. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    generalsamad (2015-12-28),Lexi (2015-02-18)

  15. #12
    Lounger Lexi's Avatar
    Join Date
    Aug 2013
    Posts
    25
    Thanks
    20
    Thanked 1 Time in 1 Post
    Very logical. That makes so much sense and it worked! You guys are the greatest!!!!

Posting Permissions

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