Can I put the "verification" part in a separate procedure that executes on a
"change" event of txtItemNum? I would like to verify the number before the
user clicks ENTER and the data is placed in the cells.

Thanks,
Les

"Tom Ogilvy" wrote:

looks like there was a typo:

Private Sub CommandButton1_Click()
Dim rng As Range, res as Variant
Dim rng1 as Range, res1 as Variant
With worksheets("Items")
set rng1 = .Range(.cells(2,1),.cells(rows.count,1).End(xlup))
End with
With Worksheets("Order")
Set rng = .Cells(Rows.Count, 1).End(xlUp)(2)
If rng.Row < 13 Then
Set rng = .Cells(13, 1)
End If
End With
res = application.Match(txtItemNum,rng1,0)
res1 = application.match(cdbl(txtItemNum),rng1,0) '< corrected
if not iserror(res) or not iserror(res1) then
rng.Value = txtItemNum
rng.Offset(0, 1).Value = txtQty
elseIf txtItemNum = "NSI" Then
rng.Offset(0, 2).Value = txtNSIDesc
else
msgbox "Not a valid item number"
End If
txtItemNum = ""
txtQty = ""
txtNSIDesc = ""
Label5.Visible = False
txtNSIDesc.Visible = False
optStandard.Value = True
txtItemNum.SetFocus
End Sub


this assumes the item numbers are numbers like 1234 and not alpha numeric
like A1234 although it should work with those as well, but would not need
the second check with match.

If cdbl doesn't work, then try clng instead.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

Private Sub CommandButton1_Click()
Dim rng As Range, res as Variant
Dim rng1 as Range, res1 as Variant
With worksheets("Items")
set rng1 = .Range(.cells(2,1),.cells(rows.count,1).End(xlup))
End with
With Worksheets("Order")
Set rng = .Cells(Rows.Count, 1).End(xlUp)(2)
If rng.Row < 13 Then
Set rng = .Cells(13, 1)
End If
End With
res = application.Match(txtItemNum,rng1,0)
res1 = application.match(cdbl(txtItemNum,rng1,0)
if not iserror(res) or not iserror(res1) then
rng.Value = txtItemNum
rng.Offset(0, 1).Value = txtQty
elseIf txtItemNum = "NSI" Then
rng.Offset(0, 2).Value = txtNSIDesc
else
msgbox "Not a valid item number"
End If
txtItemNum = ""
txtQty = ""
txtNSIDesc = ""
Label5.Visible = False
txtNSIDesc.Visible = False
optStandard.Value = True
txtItemNum.SetFocus
End Sub

for a combobox, instead of using a textbox for txtItemNum, put a combobox on
your sheet and set its rowsource property to Items!A1:A200 (use the
actual range).

--
Regards,
Tom Ogilvy


"WLMPilot" wrote:

I use a userform for order entry. The two textboxes used are txtItemNum and
txtqty. The answer I got the last time I submitted this question did not
work, but provided a different way to go about doing what I needed.

Initially, I wanted to take the value of ItemNum and verify it against a
list of numbers on worksheet "Items", column A to be sure the number was a
valid item number. Someone suggested using a listbox and/or combobox so that
the user could only choose a valid item number.

The actual order entry takes place in worksheet "Order". The list of item
numbers and the item descriptions are located on worksheet "Items". Column A
= Item Number. Column B = Item Description. I

1) What is the difference between Listbox and Combobox?

2) How do I accomplish using the listbox/combobox for the userform input?

Here is the code that is currently used to place the user's input into the
cells without verification. FYI -- The reference to "txtItemNum = NSI" is
if the user clicks option button for "Non-Standard Item", then the macro for
that event places "NSI" in the txtItemNum field and the user enters a qty and
description. That part allows the user to enter an item not found on the
regular supply list.

'ROUTINE FOR ORDER ENTRY - EXECUTES WHEN "ENTER" CLICKED
Private Sub CommandButton1_Click()
Dim rng As Range
With Worksheets("Order")
Set rng = .Cells(Rows.Count, 1).End(xlUp)(2)
If rng.Row < 13 Then
Set rng = .Cells(13, 1)
End If
End With
rng.Value = txtItemNum
rng.Offset(0, 1).Value = txtQty
If txtItemNum = "NSI" Then
rng.Offset(0, 2).Value = txtNSIDesc
End If
txtItemNum = ""
txtQty = ""
txtNSIDesc = ""
Label5.Visible = False
txtNSIDesc.Visible = False
optStandard.Value = True
txtItemNum.SetFocus
End Sub

Any help is greatly appreciated!!!
Les