Verifying Input

[WLMPilot]

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

 

[Tom Ogilvy]

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).

 

[Tom Ogilvy]

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.

 

[WLMPilot]

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]

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

 

[WLMPilot]

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]

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.

 

[WLMPilot]

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