Cell Values On User Form
Gregor,
I assume that you are getting that problem when nothing is entered in
Textbox1? If so, this should take care of it
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim nMatch As Long
With TextBox1
If .Text < "" Then
On Error Resume Next
nMatch = WorksheetFunction.Match(.Text, Range("A1:A10"), 0)
On Error GoTo 0
If nMatch < 0 Then
TextBox2.Text = WorksheetFunction.VLookup(.Text, _
Worksheets("Sheet2").Range("A1:C10"), 2, False)
TextBox3.Text = WorksheetFunction.VLookup(.Text, _
Worksheets("Sheet2").Range("A1:C10"), 3, False)
Else
MsgBox "Value Not Found"
.SelLength = Len(.Text)
.SelStart = 0
.SetFocus
Cancel = True
End If
End If
End With
End Sub
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
|