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

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.



With worksheets("Items")
set rng1 = .Range(.cells(2,1),.cells(rows.count,1).End(xlup))
End with

See it now?

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?

I don't know, guess you would have to try it.



How is this
accomplished?

What do you mean how is it accomplished - it is accomplished using Offset,
just like you said.

--
Regards,
Tom Ogilvy



"WLMPilot" wrote in message
...
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

Hey Tom,

Sorry if there was any confusion. I will try to clarify.

REFERENCE #1) Doesn't the code you provided below locate the last row on
the "Items" sheet? If so, what I wanted to know is how to MATCH the user
input in txtItemNum to a list of item numbers on the worksheet("Items").

REFERENCE #3 - How is this accomplished?
I wanted to know how to pull the item description from worksheet(Items) into
a variable, once the match is made. After I have pulled the description into
a variable, I know how to use the offset to place it in the appropriate cell
on the worksheet(Orders), which is where the user is using the userform to
enter the items.

Hope that helps,
Thanks again,
Les

"Tom Ogilvy" wrote:

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.



With worksheets("Items")
set rng1 = .Range(.cells(2,1),.cells(rows.count,1).End(xlup))
End with

See it now?

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?

I don't know, guess you would have to try it.



How is this
accomplished?

What do you mean how is it accomplished - it is accomplished using Offset,
just like you said.

--
Regards,
Tom Ogilvy



"WLMPilot" wrote in message
...
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