ExcelBanter - Vlookup Problem

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Vlookup Problem (https://www.excelbanter.com/excel-discussion-misc-queries/210373-vlookup-problem.html)

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

You will get mismatch error if Vlookup does not find a match as it will then
return #N/A...

Use
If IsError(varDN) = True Then
MsgBox TextBox3.Value & " not found"
Else
MsgBox varDN
End If

In fact you do not need this message as you have already covered it in the
next IF statement...
"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

Hi,

Try this. I've left setfocus in but doubt it will work AFAIK it's vey buggy.
so you'll have to re-select manually.

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim varDN As Variant
If TextBox3.Value < "" Then
varDN = Application.VLookup(TextBox3.Value,
Worksheets("CETables").Range("O:O"), 1, False)
End If
If IsError(varDN) And TextBox3.Value < "" Then
MsgBox "Non-Standard DN Value"
TextBox3.Value = ""
TextBox3.SetFocus
End If
End Sub

Mike
"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

I receive another error, "Subscript out of range," on the code line: varDN =
Application.VLookup(TextBox3.Value, Worksheets("CETables").Range("O:O"), 1,
False)

"Mike H" wrote:

Hi,

Try this. I've left setfocus in but doubt it will work AFAIK it's vey buggy.
so you'll have to re-select manually.

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim varDN As Variant
If TextBox3.Value < "" Then
varDN = Application.VLookup(TextBox3.Value,
Worksheets("CETables").Range("O:O"), 1, False)
End If
If IsError(varDN) And TextBox3.Value < "" Then
MsgBox "Non-Standard DN Value"
TextBox3.Value = ""
TextBox3.SetFocus
End If
End Sub

Mike
"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

My mistake, I forgot to put the space between CE and Tables. But now I get
the message box, even though I input a number that I know exists in the table.

"aftamath77" wrote:

I receive another error, "Subscript out of range," on the code line: varDN =
Application.VLookup(TextBox3.Value, Worksheets("CETables").Range("O:O"), 1,
False)

"Mike H" wrote:

Hi,

Try this. I've left setfocus in but doubt it will work AFAIK it's vey buggy.
so you'll have to re-select manually.

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim varDN As Variant
If TextBox3.Value < "" Then
varDN = Application.VLookup(TextBox3.Value,
Worksheets("CETables").Range("O:O"), 1, False)
End If
If IsError(varDN) And TextBox3.Value < "" Then
MsgBox "Non-Standard DN Value"
TextBox3.Value = ""
TextBox3.SetFocus
End If
End Sub

Mike
"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

If that's happening the error is in the data because it works perfectly for
me. the usual suspects are rougue spaces in the O:O range

Mike

"aftamath77" wrote:

My mistake, I forgot to put the space between CE and Tables. But now I get
the message box, even though I input a number that I know exists in the table.

"aftamath77" wrote:

I receive another error, "Subscript out of range," on the code line: varDN =
Application.VLookup(TextBox3.Value, Worksheets("CETables").Range("O:O"), 1,
False)

"Mike H" wrote:

Hi,

Try this. I've left setfocus in but doubt it will work AFAIK it's vey buggy.
so you'll have to re-select manually.

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim varDN As Variant
If TextBox3.Value < "" Then
varDN = Application.VLookup(TextBox3.Value,
Worksheets("CETables").Range("O:O"), 1, False)
End If
If IsError(varDN) And TextBox3.Value < "" Then
MsgBox "Non-Standard DN Value"
TextBox3.Value = ""
TextBox3.SetFocus
End If
End Sub

Mike
"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

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

Thanks Dave, these were all great suggestions. I used the .Match method.
Keep up the great work you do on this site.

"Dave Peterson" wrote:

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