MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Problem with simple error trap

  Asked By: Hayfa    Date: Sep 12    Category: MS Office    Views: 523

Could someone help me understand what I'm missing here...

I have a Userform with a TextBox (tbT_Code1) and I'm trying to trap
for non-integer entries OR integer entries outside established
limits (iRetCodeMin < value < iRetCodeMax).

My code (with iRetcode1, iRetCodeMin & iRetCodeMax all declared as
integers) is as follows:

Private Sub tbT_Code1_Change()
If tbT_Code1 <> "" Then
On Error Resume Next ' Trap a non-integer entry
iRetCode1 = tbT_Code1.Value
If iRetCode1 > iRetCodeMax Or iRetCode1 < iRetCodeMin Then
MsgBox "You have entered an invalid 'Return Code'..." &
Chr$(10) & Chr$(10) _
& "Please enter a valid code from the list above."
tbT_Code1 = ""
' Exit Sub ' Force the error trap to reset
sReason1 = Application.WorksheetFunction.VLookup _
(iRetCode1, Range("RetCode_Lookup"), 2, False)
tbT_Reason1 = sReason1
End If
End If
End Sub

The trap & test seems to work as desired if a non-integer or an out
of range value is entered INITIALLY (displays the message then
clears the offending value).

Problem I'm having is if a "valid" code is entered and then the user
highlights/edits the code to an "invalid" code neither the "Error
Trap" nor the Vlookup are activated. Shouldn't the "changed" value
re-run the Sub tbT_Code1_Change code?



2 Answers Found

Answer #1    Answered By: Janis Hart     Answered On: Sep 12

Never mind.... I found the following code  corrects my problems (it only accepts
"Valid Codes" eliminating the need for the message):

Private Sub tbT_Code1_Change()
If tbT_Code1.Value <= iRetCodeMax And tbT_Code1.Value >= iRetCodeMin Then
iRetCode1 = tbT_Code1.Value
iRetCode1 = 0
sReason1 = ""
tbT_Reason1 = sReason1
tbT_Code1 = ""
Exit Sub ' Force the error  trap to reset
End If
If iRetCode1 <> 0 Then
sReason1 = Application.WorksheetFunction.VLookup(iRetCode1,
Range("RetCode_Lookup"), 2, False)
tbT_Reason1 = sReason1
End If
End Sub

Answer #2    Answered By: Shiv Patel     Answered On: Sep 12

Avoid using "On error  Resume Next" to trap  non-integers. That looks
like trouble. Instead try something like an If statement and using
the Int() function to determine if it is an integer. Also try using
a breakpoint and stepping through your code  to see what is happening.

Didn't find what you were looking for? Find more on Problem with simple error trap Or get search suggestion and latest updates.