SetFocus problem

[WLMPilot]

I have a userform that is used to order supplies. Each time through, the
stock item and max stock level is displayed. User enters "Stock On Hand"
(Textbox1). If valid entry is made, Textbox2 (order quantiy) = Max stock
level - Textbox1.

The following macro (see below) executes on Keycode 9 or 13 (tab or enter)
for Textbox1. I try to catch errors of negative number, character entry, or
blank field.
PROBLEM SO FAR: I enter a null value to test. It catches the error and
then goes to "BADENTRY". However, the setfocus does not occur for Textbox1.
The focus is placed on Textbox2. I need it back in Textbox1 so user can
re-enter the correct numerical data.

Any Ideas?

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
If KeyCode = 13 Or KeyCode = 9 Then
Dim Msg As String
Dim onhand, ErrorNum As Integer
'***********************************************
'Error Check Section
'Correct Entry >= 0
'Check for blank entry
If TextBox1 = "" Then
GoTo BadEntry
End If
'Test for negative number
onhand = Str(TextBox1)
On Error GoTo BadEntry
If onhand < 0 Then
GoTo BadEntry
End If
'***********************************************
'Data is Correct. Proceed
TextBox2 = Str(TextBox4) - Str(TextBox1)
TextBox1.BackColor = vbWhite
CommandButton4.Visible = True
CommandButton1.SetFocus
GoTo Closeout
'***********************************************
BadEntry:
Msg = "INVALID ENTRY" & vbNewLine
Msg = Msg & "Please enter a number" & vbNewLine
Msg = Msg & "greater than or equal to zero."
MsgBox Msg
TextBox1 = ""
TextBox1.SetFocus
Closeout:
End If
End Sub

 

[Geoff K]

Have you tried:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Len(TextBox1.Text) = 0 Then Cancel = True
End Sub

hth

Geoff

 

[Geoff K]

oops!

Try this instead. There are a number of ways but perhaps this approach
might work:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

''' only permit digits and cannot permit a negative entry
Select Case KeyAscii
Case 48 To 57
Case Else
KeyAscii = 0
End Select

End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim Msg As String

''' test value on exit using tab or enter keys
If Len(TextBox1.Text) = 0 Then
Cancel = True
Msg = "INVALID ENTRY" & vbNewLine
Msg = Msg & "Please enter a number" & vbNewLine
Msg = Msg & "greater than or equal to zero."
MsgBox Msg
End If

End Sub

Private Sub TextBox2_Enter()

''' does your stuff
TextBox2 = Str(TextBox4) - Str(TextBox1)
CommandButton4.Visible = True
CommandButton1.SetFocus

End Sub

hth

Geoff

 

[john]

Enter Event of any Control Object occurs before a control actually receives
the focus from a control on the same form. Exit occurs immediately before a
control loses the focus to another control on the same form.

So a possible solution to your problem might be something like this:

Place all code behind your form with this line “Dim MyCancel As Boolean†at
the top outside any procedure.


Dim MyCancel As Boolean

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = MyCancel
MyCancel = False
End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
If KeyCode = 13 Or KeyCode = 9 Then
Dim Msg As String
Dim onhand, ErrorNum As Integer
'***********************************************
'Error Check Section
'Correct Entry >= 0
'Check for blank entry
If TextBox1 = "" Then
GoTo BadEntry
End If
'Test for negative number
onhand = Str(TextBox1)
On Error GoTo BadEntry
If onhand < 0 Then
GoTo BadEntry
End If
'***********************************************
'Data is Correct. Proceed
TextBox2 = Str(TextBox4) - Str(TextBox1)
TextBox1.BackColor = vbWhite
CommandButton4.Visible = True
CommandButton1.SetFocus
GoTo Closeout
'***********************************************
BadEntry:
Msg = "INVALID ENTRY" & vbNewLine
Msg = Msg & "Please enter a number" & vbNewLine
Msg = Msg & "greater than or equal to zero."
MsgBox Msg
MyCancel = True
TextBox1 = vbNullString

Closeout:

End If
End Sub

 

[Rick Rothstein]

This doesn't answer your question directly, but I wanted to point out that
there is a "simpler" way to test that the TextBox contains digits only. Note
the If..Then statement in this sample code to demonstrate it...

If Len(TextBox1) > 0 And Not TextBox1 Like "*[!0-9]*" Then
MsgBox "The TextBox contains only digits"
Else
MsgBox "The TextBox is either empty or contains non-digits"
End If

 

[Rick Rothstein]

Just out of curiosity, does this code do what you want...

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim Msg As String
Dim onhand, ErrorNum As Integer
If KeyCode = 13 Or KeyCode = 9 Then
If Len(TextBox1) > 0 And Not TextBox1 Like "*[!0-9]*" Then
TextBox2 = Str(TextBox4) - Str(TextBox1)
TextBox1.BackColor = vbWhite
CommandButton4.Visible = True
CommandButton1.SetFocus
Else
Msg = "INVALID ENTRY" & vbNewLine
Msg = Msg & "Please enter a number" & vbNewLine
Msg = Msg & "greater than or equal to zero."
MsgBox Msg
TextBox1 = ""
End If
End If
End Sub

--
Rick (MVP - Excel)

This doesn't answer your question directly, but I wanted to point out that
there is a "simpler" way to test that the TextBox contains digits only.
Note the If..Then statement in this sample code to demonstrate it...

If Len(TextBox1) > 0 And Not TextBox1 Like "*[!0-9]*" Then
MsgBox "The TextBox contains only digits"
Else
MsgBox "The TextBox is either empty or contains non-digits"
End If

--
Rick (MVP - Excel)

I have a userform that is used to order supplies. Each time through, the
stock item and max stock level is displayed. User enters "Stock On Hand"
(Textbox1). If valid entry is made, Textbox2 (order quantiy) = Max stock
level - Textbox1.

The following macro (see below) executes on Keycode 9 or 13 (tab or
enter)
for Textbox1. I try to catch errors of negative number, character entry,
or
blank field.
PROBLEM SO FAR: I enter a null value to test. It catches the error and
then goes to "BADENTRY". However, the setfocus does not occur for
Textbox1.
The focus is placed on Textbox2. I need it back in Textbox1 so user can
re-enter the correct numerical data.

Any Ideas?

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger,
ByVal
Shift As Integer)
If KeyCode = 13 Or KeyCode = 9 Then
Dim Msg As String
Dim onhand, ErrorNum As Integer
'***********************************************
'Error Check Section
'Correct Entry >= 0
'Check for blank entry
If TextBox1 = "" Then
GoTo BadEntry
End If
'Test for negative number
onhand = Str(TextBox1)
On Error GoTo BadEntry
If onhand < 0 Then
GoTo BadEntry
End If
'***********************************************
'Data is Correct. Proceed
TextBox2 = Str(TextBox4) - Str(TextBox1)
TextBox1.BackColor = vbWhite
CommandButton4.Visible = True
CommandButton1.SetFocus
GoTo Closeout
'***********************************************
BadEntry:
Msg = "INVALID ENTRY" & vbNewLine
Msg = Msg & "Please enter a number" & vbNewLine
Msg = Msg & "greater than or equal to zero."
MsgBox Msg
TextBox1 = ""
TextBox1.SetFocus
Closeout:
End If
End Sub

 

[WLMPilot]

Thanks John. This worked great! I just had a chance to try it yesterday.

I would like to ask you questions concerning the code so I can understand
what is happening a little better.

1) Why is "Dim MyCancel As Boolean" outside any subroutine and at the
beginning of all code having to do with userform?

2) What does "MyCancel = True" trigger?

3) I know what "TextBox1 = vbNullString" does (or at least I think I do).
Could I have kept Textbox1 = ""?

Thanks,
Les

 

[WLMPilot]

Thanks John. This worked great! I just had a chance to try it yesterday.

I would like to ask you questions concerning the code so I can understand
what is happening a little better.

1) Why is "Dim MyCancel As Boolean" outside any subroutine and at the
beginning of all code having to do with userform?

2) What does "MyCancel = True" trigger?

3) I know what "TextBox1 = vbNullString" does (or at least I think I do).
Could I have kept Textbox1 = ""?

Thanks,
Les