Just some suggestions...

First, since you're looking to see if the value is in a column, you could use
application.match() instead of =vlookup() (and returning 1).

Second, instead of showing a msgbox for errors, you could add a label to the
userform. Then the user can read your error message there and not have to
dismiss any dialog box.

Third, varDN won't have a .value property.

If you use application.vlookup(), it'll match the textbox3 value (if there is a
match) or an error. You could just use: msgbox varDN

If you application.match(), it'll return a number (the row index of the lookup
range, if there is a match) or an error. You could use: msgbox varDN

In fact, you'd want to check to see if there's an error:

if iserror(vardn) then
msgbox "no match"
else
msgbox vardn
end if

if vardn is an error, then this: msgbox vardn
won't work at all.


Option Explicit
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim varDN As Variant

Me.Label1.Caption = ""

If Me.TextBox3.Value = "" Then
Me.Label1.Caption = "DN Entry is blank!"
Cancel = True
End If

'textbox3.value is text--not a number
'so if column O can contain either text or numbers
'we can look twice.
'if it only contains numbers, you could drop the first
'application.match()
'And if you only have integers, you can use
'clng() instead of cdbl().
varDN = Application.Match(TextBox3.Value, _
Worksheets("CE Tables").Range("O:O"), 0)
If IsError(varDN) Then
If IsNumeric(Me.TextBox3.Value) Then
varDN = Application.Match(CDbl(TextBox3.Value), _
Worksheets("CE Tables").Range("O:O"), 0)
End If
End If

If IsError(varDN) Then
Me.Label1.Caption = "Non-Standard DN Value"
Me.TextBox3.Value = ""
Cancel = True
End If

End Sub
Private Sub UserForm_Initialize()
With Me.CommandButton1
.Caption = "Ok"
.Default = True
End With
With Me.CommandButton2
.Caption = "Cancel"
.Cancel = True
'allow you to click cancel if you're in textbox3.
.TakeFocusOnClick = False
End With
With Me.Label1
.Caption = ""
.ForeColor = vbRed
End With
End Sub


aftamath77 wrote:

I have a userform with textbox3. The user enters a value into textbox3.
When the user moves to the next userform control, I want VBA to check the
value entered in textbox3 against a table I have on a worksheet (CE Tables).
If the value exists in the table, the focus will move to the next control,
but if the value doesn't exist, I want a msgbox to give a warning, then clear
the value and return to textbox3. Currently, I get a "Type Mismatch" with
MsgBox varDN.Value.

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim varDN As Variant

varDN = Application.VLookup(TextBox3.Value, Worksheets("CE
Tables").Range("O:O"), 1, False)
MsgBox varDN.Value

If IsError(varDN) = True And TextBox3.Value < "" Then
MsgBox "Non-Standard DN Value"
TextBox3.Value = ""
Textbox3.SetFocus
End If

End Sub

--

Dave Peterson