Search code examples
excelvbafocususerformsetfocus

TextBox SetFocus not working on VBA Form Excel

I have simple form which i am planning to use on audit the sorting station at my work. Its pretty simple and looks like this:

.

The Problem: I'm using a handheld scanner (Symbol LI4278) to scan bar codes of every SKU contained in a certain bulk. The procedure is simple:

  1. Scan the bulk Code (Label: Etiqueta de Bulto)
  2. Then, the focus drops on SKU text Label
  3. Scan every SKU Barcode
  4. Send information to an access data base.

My problem lies in that after i scan a SKU Barcode, focus does not return to text label (T4) in order to keep scanning (SKU TEXT LABEL = T4), unless i hit TAB once. I need this to be automatic, and setfocus property is not working.

Here's my code:

Private Sub txtSKU_Change()

            Application.EnableEvents = False
            txtBulto.Locked = True

            If Len(Me.txtSKU.Value) = 13 Then

                Me.L1.ColumnCount = 3
                Me.L1.AddItem Me.txtBulto.Value
                Me.L1.List(L1.ListCount - 1, 1) = Me.txtSKU.Value
                Me.L1.List(L1.ListCount - 1, 2) = Me.txtAuditor2.Value
                End If

              txtSKU.SetFocus  
            Application.EnableEvents = True
     End Sub

I would really appreciate your help on this. I need this application to work perfect for operation purposes and reduce mistakes.

Regards

enter image description here

Solution

  • Assuming the barcode scanner automatically appends an Enter on each successfull scan, you just need to trap that Enter at KeyDown event and replace with KeyCode 0.

    Try comment your txtSKU_Change Sub and append below to test:

    Private Sub txtSKU_Change()
    Dim sValue As String
    With Me.txtSKU
        sValue = WorksheetFunction.Trim(.Value)
        If Len(sValue) = 13 Then
            With Me.L1
                .AddItem Me.txtBulto.Value
                .List(.ListCount - 1, 1) = sValue
                .List(.ListCount - 1, 2) = Me.txtAuditor2.Value
            End With
            .Value = vbNullString
        End If
    End With
End Sub

Private Sub txtSKU_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 13 Then KeyCode = 0 ' Rejects Enter Key
End Sub