Search code examples
vbaexceluserform

VBA SetFocus on Keydown not working if checkbox true?


I have the following form in excel. It is part of a simple inventory worksheet, and is used to update the status of items. It was functioning as intended, however when I tried to add in a checkbox to make the selected status persist (allowing me to only type in the serial rather than serial and status every time when working with a batch of items) I noticed that the focus after submitting cycles forward as if I pressed tab rather than being where I set it with SetFocus.

I'm presuming this is an oversight related to either the event cycle for KeyDown or nested If/Else logic, but I've had no luck actually diagnosing it. I've only recently started using VBA, and there are a lot of quirks I'm trying to understand.

Private Sub clear()
    Me.txtSerial = ""
    If cbPersist.Object.Value = False Then
        Me.cboxStatus = ""
        Me.cboxStatus.SetFocus
    Else
        Me.txtSerial.SetFocus
    End If
End Sub

Private Sub submit()
    Dim loc As Range
    Dim ws As Worksheet
    Set ws = Worksheets("Full Inventory")
    Set loc = ws.Columns("B").Find(what:=Me.txtSerial.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not loc Is Nothing Then
        ActiveWindow.ScrollRow = loc.Row
        ws.Cells(loc.Row, 10).Value = Me.cboxStatus.Value
    Else
        MsgBox "Serial not found."
    End If
    clear
End Sub

Private Sub txtSerial_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
        submit
    ElseIf KeyCode = vbKeyEscape Then
        clear
    End If
End Sub

Private Sub UserForm_Initialize()
    cboxStatus.List = Array("Stock", "Shipped", "Research", "Sent Back", "Return")
End Sub

Solution

  • Suggest the following:

    Code snippet of UserForm module

    Private Sub txtSerial_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
      If KeyCode = vbKeyReturn Then
        submit
        KeyCode = vbKeyPageDown     ' << modify Enter key value to prevent from tab hopping
      ElseIf KeyCode = vbKeyEscape Then
        clear
      End If
    End Sub