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
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