Error control?

G

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: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
 
D

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.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Please help with code? 2
Name look up 0
Auto look up 1
Help with code please? 2
Advice Please? 3
Advice on code 0
Help Please 0
Help with code please 1

Top