Error control?

[Greg]

Hi all, I have put below the code I use to look a number that has been
inputted into textbox1. The problem is when there is an illegal code put in
it comes up with the invalid code message box as expected but goes onto the
next textbox. I need it to highlight where the mistake was made.

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

Dim ans

On Error Resume Next


ans = Application.Match(CLng(TextBox1.Text), Range("A:A"), 0)
If Not IsError(ans) Then
TextBox2.Text = Application.Index(Range("B:B"), ans)
TextBox3.Text = Application.Index(Range("C:C"), ans)
TextBox4.Text = Application.Index(Range("D"), ans)
TextBox5.Text = Application.Index(Range("E:E"), ans)

Else
MsgBox "Invalid code"
End If
On Error GoTo 0
End Sub

How do I do this?

Thanks in advance

Greg

 

[Dave Peterson]

You can get to those other values (in column B, C, D, and E) via VBA techniques
and I added that check for isnumeric() just in case.

And I used the _exit event--"cancel = true" if there's an error:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub Textbox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim ans As Variant

With Worksheets("sheet1")
If IsNumeric(Me.TextBox1) Then
ans = Application.Match(CLng(Me.TextBox1.Text), .Range("A:A"), 0)
Else
ans = Application.Match(Me.TextBox1.Text, .Range("a:a"), 0)
End If
If Not IsError(ans) Then
Me.TextBox2.Text = .Range("B:B")(ans)
Me.TextBox3.Text = .Range("C:C")(ans)
Me.TextBox4.Text = .Range("d:d")(ans)
Me.TextBox5.Text = .Range("e:e")(ans)
Me.Label1.Caption = ""
Else
Me.TextBox2.Text = ""
Me.TextBox3.Text = ""
Me.TextBox4.Text = ""
Me.TextBox5.Text = ""
Me.Label1.Caption = "Invalid Code"
Cancel = True
End If
End With
End Sub
Private Sub UserForm_Initialize()
Me.Label1.Caption = ""
End Sub

(I also added a label right above textbox1 to show any error messages. It makes
life a little simpler.)

 

[Greg]

Thank you Dave

Greg