I have a userform for user to input a item number (3 digits) and quantity.
When enter is pressed to advance to next textbox, the following macro
executes to see if the item number is a valid entry. As far as I can tell,
it works fine except for the SetFocus if entry is INVALID. You will see the
msgboxes so I can determine if FIND is working properly.

However, when it goes to the INVALID section, the focus is set on txtQty. I
even had txtitemnum = "TEST" to see if it would place TEST in textbox and it
did. If the entry is invalid, then the textbox is cleared and suppose to set
focus back to the textbox for user to try a different item number.

Private Sub txtitemnum_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
Dim c1, c2 As Range
If KeyCode = 13 Then
Set c1 = Sheets("Items").Range("A3")
Set c2 = c1.End(xlDown)
If Not c1.Find(What:=(txtitemnum), LookAt:=xlWhole,
Searchorder:=xlByRows, _
SearchDirection:=xlNext) Is Nothing Then
MsgBox "Valid Entry"
txtQty.SetFocus
Else
MsgBox "Invalid Entry -- Retry"
txtitemnum.Value = ""
txtitemnum.SetFocus <---Does not happen
End If
End If
End Sub


Thanks for your help.
Les

Just a thought...


Try to add the form.


UserForm1.txtitemnum.SetFocus 'or whatever your form name is.

Maybe even this...

Me.txtitemnum.SetFocus


Mark Ivey



"WLMPilot" wrote in message
...
I have a userform for user to input a item number (3 digits) and quantity.
When enter is pressed to advance to next textbox, the following macro
executes to see if the item number is a valid entry. As far as I can
tell,
it works fine except for the SetFocus if entry is INVALID. You will see
the
msgboxes so I can determine if FIND is working properly.

However, when it goes to the INVALID section, the focus is set on txtQty.
I
even had txtitemnum = "TEST" to see if it would place TEST in textbox and
it
did. If the entry is invalid, then the textbox is cleared and suppose to
set
focus back to the textbox for user to try a different item number.

Private Sub txtitemnum_KeyDown(ByVal KeyCode As MSForms.ReturnInteger,
ByVal
Shift As Integer)
Dim c1, c2 As Range
If KeyCode = 13 Then
Set c1 = Sheets("Items").Range("A3")
Set c2 = c1.End(xlDown)
If Not c1.Find(What:=(txtitemnum), LookAt:=xlWhole,
Searchorder:=xlByRows, _
SearchDirection:=xlNext) Is Nothing Then
MsgBox "Valid Entry"
txtQty.SetFocus
Else
MsgBox "Invalid Entry -- Retry"
txtitemnum.Value = ""
txtitemnum.SetFocus <---Does not happen
End If
End If
End Sub


Thanks for your help.
Les

Instead of using the _KeyDown event, try the _beforeupdate event. It has a
Cancel parm that will keep you in the textbox.

Option Explicit
Private Sub txtitemnum_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim c1 As Range, c2 As Range

Set c1 = Sheets("Items").Range("A3")
Set c2 = c1.End(xlDown)
If Not c1.Find(What:=txtitemnum, LookAt:=xlWhole, Searchorder:=xlByRows, _
SearchDirection:=xlNext) Is Nothing Then
MsgBox "Valid Entry"
txtQty.SetFocus
Else
MsgBox "Invalid Entry -- Retry"
txtitemnum.Value = ""
Cancel = True
End If
End Sub

If you have a Cancel button on your userform, make sure its .takefocusonclick is
false. Otherwise, you'll have trouble.

Ps. If I were you, I'd add a label (red font) that I could populate with those
warning messages instead of using msgboxes.

pps. Did you want to search from A3 down--or the whole worksheet?

And I'd specify all the arguments for that .find line. Those parms are shared
between VBA and the userinterface. If you don't specify all of these parms,
then you may find that your command doesn't work the way you would expect.

WLMPilot wrote:

I have a userform for user to input a item number (3 digits) and quantity.
When enter is pressed to advance to next textbox, the following macro
executes to see if the item number is a valid entry. As far as I can tell,
it works fine except for the SetFocus if entry is INVALID. You will see the
msgboxes so I can determine if FIND is working properly.

However, when it goes to the INVALID section, the focus is set on txtQty. I
even had txtitemnum = "TEST" to see if it would place TEST in textbox and it
did. If the entry is invalid, then the textbox is cleared and suppose to set
focus back to the textbox for user to try a different item number.

Private Sub txtitemnum_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
Dim c1, c2 As Range
If KeyCode = 13 Then
Set c1 = Sheets("Items").Range("A3")
Set c2 = c1.End(xlDown)
If Not c1.Find(What:=(txtitemnum), LookAt:=xlWhole,
Searchorder:=xlByRows, _
SearchDirection:=xlNext) Is Nothing Then
MsgBox "Valid Entry"
txtQty.SetFocus
Else
MsgBox "Invalid Entry -- Retry"
txtitemnum.Value = ""
txtitemnum.SetFocus <---Does not happen
End If
End If
End Sub

Thanks for your help.
Les

--

Dave Peterson

What triggers the "beforeupdate" in order to execute this code?

Les



"Dave Peterson" wrote:

Instead of using the _KeyDown event, try the _beforeupdate event. It has a
Cancel parm that will keep you in the textbox.

Option Explicit
Private Sub txtitemnum_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim c1 As Range, c2 As Range

Set c1 = Sheets("Items").Range("A3")
Set c2 = c1.End(xlDown)
If Not c1.Find(What:=txtitemnum, LookAt:=xlWhole, Searchorder:=xlByRows, _
SearchDirection:=xlNext) Is Nothing Then
MsgBox "Valid Entry"
txtQty.SetFocus
Else
MsgBox "Invalid Entry -- Retry"
txtitemnum.Value = ""
Cancel = True
End If
End Sub

If you have a Cancel button on your userform, make sure its .takefocusonclick is
false. Otherwise, you'll have trouble.

Ps. If I were you, I'd add a label (red font) that I could populate with those
warning messages instead of using msgboxes.

pps. Did you want to search from A3 down--or the whole worksheet?

And I'd specify all the arguments for that .find line. Those parms are shared
between VBA and the userinterface. If you don't specify all of these parms,
then you may find that your command doesn't work the way you would expect.

WLMPilot wrote:

I have a userform for user to input a item number (3 digits) and quantity.
When enter is pressed to advance to next textbox, the following macro
executes to see if the item number is a valid entry. As far as I can tell,
it works fine except for the SetFocus if entry is INVALID. You will see the
msgboxes so I can determine if FIND is working properly.

However, when it goes to the INVALID section, the focus is set on txtQty. I
even had txtitemnum = "TEST" to see if it would place TEST in textbox and it
did. If the entry is invalid, then the textbox is cleared and suppose to set
focus back to the textbox for user to try a different item number.

Private Sub txtitemnum_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
Dim c1, c2 As Range
If KeyCode = 13 Then
Set c1 = Sheets("Items").Range("A3")
Set c2 = c1.End(xlDown)
If Not c1.Find(What:=(txtitemnum), LookAt:=xlWhole,
Searchorder:=xlByRows, _
SearchDirection:=xlNext) Is Nothing Then
MsgBox "Valid Entry"
txtQty.SetFocus
Else
MsgBox "Invalid Entry -- Retry"
txtitemnum.Value = ""
txtitemnum.SetFocus <---Does not happen
End If
End If
End Sub

Thanks for your help.
Les

--

Dave Peterson

When you try to leave the control, tab away, click away, hit enter...

Try it with a test userform and a simple msgbox to see when it fires.

WLMPilot wrote:

What triggers the "beforeupdate" in order to execute this code?

Les

"Dave Peterson" wrote:

Instead of using the _KeyDown event, try the _beforeupdate event. It has a
Cancel parm that will keep you in the textbox.

Option Explicit
Private Sub txtitemnum_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim c1 As Range, c2 As Range

Set c1 = Sheets("Items").Range("A3")
Set c2 = c1.End(xlDown)
If Not c1.Find(What:=txtitemnum, LookAt:=xlWhole, Searchorder:=xlByRows, _
SearchDirection:=xlNext) Is Nothing Then
MsgBox "Valid Entry"
txtQty.SetFocus
Else
MsgBox "Invalid Entry -- Retry"
txtitemnum.Value = ""
Cancel = True
End If
End Sub

If you have a Cancel button on your userform, make sure its .takefocusonclick is
false. Otherwise, you'll have trouble.

Ps. If I were you, I'd add a label (red font) that I could populate with those
warning messages instead of using msgboxes.

pps. Did you want to search from A3 down--or the whole worksheet?

And I'd specify all the arguments for that .find line. Those parms are shared
between VBA and the userinterface. If you don't specify all of these parms,
then you may find that your command doesn't work the way you would expect.

WLMPilot wrote:

I have a userform for user to input a item number (3 digits) and quantity.
When enter is pressed to advance to next textbox, the following macro
executes to see if the item number is a valid entry. As far as I can tell,
it works fine except for the SetFocus if entry is INVALID. You will see the
msgboxes so I can determine if FIND is working properly.

However, when it goes to the INVALID section, the focus is set on txtQty. I
even had txtitemnum = "TEST" to see if it would place TEST in textbox and it
did. If the entry is invalid, then the textbox is cleared and suppose to set
focus back to the textbox for user to try a different item number.

Private Sub txtitemnum_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
Dim c1, c2 As Range
If KeyCode = 13 Then
Set c1 = Sheets("Items").Range("A3")
Set c2 = c1.End(xlDown)
If Not c1.Find(What:=(txtitemnum), LookAt:=xlWhole,
Searchorder:=xlByRows, _
SearchDirection:=xlNext) Is Nothing Then
MsgBox "Valid Entry"
txtQty.SetFocus
Else
MsgBox "Invalid Entry -- Retry"
txtitemnum.Value = ""
txtitemnum.SetFocus <---Does not happen
End If
End If
End Sub

Thanks for your help.
Les

--

Dave Peterson


--

Dave Peterson