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


Board Regular
Apr 25, 2012
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.



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)
        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)
            .AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("H1" _
            ), Unique:=True
        End With
        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
        If ScannedItem Is Nothing Then
            msgbox ("Drug was not found")
        End If
    End If
End Sub


Board Regular
Apr 25, 2012
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.

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...