I am verifiying data by VBA. When it traps an error, I am trying to go back
to the control that caused the error. It's not working for me.

Code:
Select Case intLoan_Spread 'as entered in user form
Case Is intMaxLoan_Spread 'max is 1000
msgstring3 = "Maximum loan spread is " & intMaxLoan_Spread &
". " & msgstring3
MsgBox (msgstring3)
Me.TXBBasisPoints = intMaxLoan_Spread
Cancel = True
Me.TXBBasisPoints.SetFocus
Case Is < intMinLoan_Spread 'min is 100
msgstring3 = "Minimum loan spread is " & intMinLoan_Spread &
". " & msgstring3
MsgBox (msgstring3)
Me.TXBBasisPoints = intMinLoan_Spread
Cancel = True
Me.TXBBasisPoints.SetFocus
Case Else
'passes validation

Maybe you could put that validation code in the txbBasisPoints_exit routine.
Then just put "cancel = true" and the user won't be able to leave.

Difficult to tell where this code is.

Don Kline wrote:

I am verifiying data by VBA. When it traps an error, I am trying to go back
to the control that caused the error. It's not working for me.

Code:
Select Case intLoan_Spread 'as entered in user form
Case Is intMaxLoan_Spread 'max is 1000
msgstring3 = "Maximum loan spread is " & intMaxLoan_Spread &
". " & msgstring3
MsgBox (msgstring3)
Me.TXBBasisPoints = intMaxLoan_Spread
Cancel = True
Me.TXBBasisPoints.SetFocus
Case Is < intMinLoan_Spread 'min is 100
msgstring3 = "Minimum loan spread is " & intMinLoan_Spread &
". " & msgstring3
MsgBox (msgstring3)
Me.TXBBasisPoints = intMinLoan_Spread
Cancel = True
Me.TXBBasisPoints.SetFocus
Case Else
'passes validation

--

Dave Peterson

Thanks for the reply.

Here is the new complete code. Right now the cursor still moves to the next
field.

Private Sub TXBBasisPoints_AfterUpdate()
'Dim msgstring As TextBox
'Dim formatTXBasisPoints As Variant
intMax = Range("MaxLoan_Spread")
intMin = Range("MinLoan_Spread")
intValue = Range("Loan_Spread")
intDefault = Range("DLoan_Spread")
strMessage = "Enter a loan spread between " & intMin & " and " & intMax
& " basis points."
blnNumeric = IsNumeric(Me.TXBBasisPoints)
If blnNumeric Then
'value is numeric
MsgBox ("Hello")
'value is not numeric
Else
MsgBox ("Value is not numeric. Please enter a number")
Cancel = True
Me.TXBBasisPoints.SetFocus
End If
End Sub

If I rem out these two lines in the FALSE condition
Cancel = True
Me.TXBBasisPoints.SetFocus

and then include this:

Private Sub TXBBasisPoints_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = True
Me.TXBBasisPoints.SetFocus
End Sub

That WILL leave the focus on the current control BUT I can never leave that
control as the Exit even returns the focus to the control.

I know I'm missing something obvious.

I really meant using the _exit event and dropping the _afterupdate altogether:

Option Explicit

Private Sub TXBBasisPoints_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim blnNumeric As Boolean

'Dim msgstring As TextBox
'Dim formatTXBasisPoints As Variant
' intMax = Range("MaxLoan_Spread")
' intMin = Range("MinLoan_Spread")
' intValue = Range("Loan_Spread")
' intDefault = Range("DLoan_Spread")
' strMessage = "Enter a loan spread between " & intMin & " and " & intMax & "
basis points."
blnNumeric = IsNumeric(Me.TXBBasisPoints)
If blnNumeric Then
'value is numeric
MsgBox ("Hello")
'value is not numeric
Else
MsgBox ("Value is not numeric. Please enter a number")
Cancel = True
End If
End Sub

I commented out some of your code. (I didn't want to take the time to set up
that worksheet/range stuff.)

And as a thought, you may want to add a label to the form to show your message.
Then there's one less thing for the user to click on.



Don Kline wrote:

Thanks for the reply.

Here is the new complete code. Right now the cursor still moves to the next
field.

Private Sub TXBBasisPoints_AfterUpdate()
'Dim msgstring As TextBox
'Dim formatTXBasisPoints As Variant
intMax = Range("MaxLoan_Spread")
intMin = Range("MinLoan_Spread")
intValue = Range("Loan_Spread")
intDefault = Range("DLoan_Spread")
strMessage = "Enter a loan spread between " & intMin & " and " & intMax
& " basis points."
blnNumeric = IsNumeric(Me.TXBBasisPoints)
If blnNumeric Then
'value is numeric
MsgBox ("Hello")
'value is not numeric
Else
MsgBox ("Value is not numeric. Please enter a number")
Cancel = True
Me.TXBBasisPoints.SetFocus
End If
End Sub

If I rem out these two lines in the FALSE condition
Cancel = True
Me.TXBBasisPoints.SetFocus

and then include this:

Private Sub TXBBasisPoints_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = True
Me.TXBBasisPoints.SetFocus
End Sub

That WILL leave the focus on the current control BUT I can never leave that
control as the Exit even returns the focus to the control.

I know I'm missing something obvious.

--

Dave Peterson