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

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

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

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

You can put it in the exit event of the txtItemNum textbox.

--
Regards,
Tom Ogilvy


"WLMPilot" wrote:

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

Sorry it has taken me a while to get back with you. I was getting ready to
put the verification part that you added into the exit event for txtItemNum
and then came up with a couple of questions/concerns:

1) The match routine needs to take the value of txtItemNum and compare it
to the list (item numbers) in column A (A4:A1000) of worksheet "Items". I
did not see a reference to that worksheet.

2) I have a click event for an option button that places "NSI" in the
txtItemNum field and does not allow the user to actually tab through or enter
anything in that field if option button is TRUE. Would this trigger anything
that would execute the EXIT event?

3) Once a match is found, I also need to pull the description of the item
from the "Items" worksheet (B4:B1000) and place into the appropriate cell,
which is done using OFFSET(0,1) in the code you provided. How is this
accomplished? EXAMPLE: if the item number entered by user is matched to an
item number on worksheet(Items) - cell A50, then the description will be on
same sheet in cell B50.

Thanks again for all your help!!
Les


"Tom Ogilvy" wrote:

You can put it in the exit event of the txtItemNum textbox.

--
Regards,
Tom Ogilvy


"WLMPilot" wrote:

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