Userform textbox input with barcode scanner. How to do a do while loop?

[szita2000]

Hi All.

So I am knee deep in VBA, trying my best to make it work.
I am building a userform that will tally up barcodes with a scanner.
My scanner is set to append an enter at the end of a read.
Hence the keydown event.

Just a quick rundown for all the funny business my code trying to do.
(I know that I should use variables to save the current scan and the growing range. Unfortunately I couldn't figure that one out yet)


On my userform I have a textbox to scan into
This barcode then copied in to cell A1 on sheet "Stock counting" going downwards.
This growing list of scanned barcodes in column A then picked up and copy unique values to cell H1. (I know...)

Long story short, the unique values are part of an index, match formula that is outputting into two column B and C. This is the rowsource of a multi column listbox on the userform that displays the name and number of boxes scanned in to the next column.
So when you scan each item you can see the list growing.


My question.
After the code runs once gets to the end. How can I make the code below to loop back (maybe a do while loop?)
to start again, until the user clicks a "finish" button on my userform?

Any help much appreciated.

Thanks

Thomas

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)




    If KeyCode = vbKeyReturn Then
        currentscan = TextBox1.Value
        TextBox1.SelStart = 0
        TextBox1.SelLength = Len(TextBox1.Value)
        TextBox1.SetFocus
        
        Worksheets("List of Medication").Activate
    currentscan = TextBox1.Value
    Dim lastrow As Long
    lastrow = Worksheets("Stock Counting").Range("A" & Rows.Count).End(xlUp).Row + 1
    
        
    Set SearchRange = Worksheets("List of Medication").Range("B2", Range("B2").End(xlDown))
    Set ScannedItem = SearchRange.Find(What:=currentscan, Lookat:=xlWhole)
    
    Worksheets("Stock Counting").Activate
    Range("A" & lastrow).Value = ScannedItem.Value
    
    lastrow = Worksheets("Stock Counting").Range("A" & Rows.Count).End(xlUp).Row
    If lastrow >= 3 Then
        With Range("A2:A" & lastrow)
            .Select
            .AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("H1" _
            ), Unique:=True
        End With
    Else
        GoTo continue 'Not sure if this is the right way of doing this. But the advanced filter requires at least two rows
    End If
    
'    Application.CutCopyMode = xlCopy
'    Worksheets("List of Medication").Activate
continue:
        If ScannedItem Is Nothing Then
            msgbox ("Drug was not found")
            Else
        End If
    End If
    
    
End Sub

 

[szita2000]

Hi All.

Day two and I was thinking.
Do I really need a Do while loop?
Would it not work if at the end of the code I would just set the focus to the textbox again and highlight the last scanned barcode?
Essentially the next input with the barcode scanner would trigger the keydown event and the code would run again.

Let me test it and report back.

 

[ManIdk]

This would help me so much if you told me how you figured it out. I've sat here at my desk for two days with bloodshot eyes trying to make this work and I just can't.

 

[szita2000]

This would help me so much if you told me how you figured it out. I've sat here at my desk for two days with bloodshot eyes trying to make this work and I just can't.

Hi There.
I am sorry but this is an old thread of mine and I don't have the file anymore to refer to it.

But at the end I made it work by using the setfocus property to set the focus back in to the textbox. (Essentially like you putting the cursor back in to the textbox)
Then when the new scan happens the keycode Enter triggers the macro again.

Hope this helps.