Looping with Userform

[Guest]

I am new to userforms. I have developed a spreadsheet that users will use to
request supplies. I have a userform that the user will enter an item number
and quantity needed. This userform has two command buttons:
(cmdbutton1)=ENTER and (cmdbutton2)=FINISHED.

I tried to set up a Do-While loop w/ condition Do While CommandButton2 <>
True in an effort to allow user to enter items until FINISHED was clicked.
After the user clicked ENTER, the code will place place the itemnum & qty in
the appropriate cells. There will also be a check that the user entered a
valid item number.

Problem is I cannot get the loop to work for some reason. Right now, I can
enter an item number and qty, then click ENTER. However, nothing happens.
Is there a better way to do this. I may have to go to a For-Next loop in
order to increase the value in the OFFSET command so that the cursor will
advance to the next line. I also realize that I can just have a variable
increase by 1 within the Do-While loop.

Thanks,
Les

 

[Guest]

Les,

Forms work on events like a button click. So you need to put the code for
adding an item on the enter button. (that is in the form designer double
click on the enter button and enter the code for adding the item.

On the finished button double click it and enter the me.hide to hide the form.


You will probably need some global variables to remember where you are and
for set up keep the global variables in a module and define them as public.

Put the initialization code in the form_initialize that can be fould in the
code designer and pulling down one of the combo boxes at the top.

If you need any more help post your code.

Good luck.



On

 

[Guest]

Martin,

Thank you for for suggestions. I know programming but do not know VBA well
enough yet (asking for books for Christmas). I have heard of the Public
declaration and understand most of what you said. However, not exactly sure
how to do it all.
Therefore, I will tell you what I have and want to do.

I have two sheets: ORDER and ITEMS. The user will fill out the request on
the ORDER sheet. As the order is entered via item numbers & qty, VBA code
will verify the item number against the list on the ITEMS sheet. If invalid,
user notified via msgbox, the incorrect item number is erased, and cursor
placed back in the cell (no advancing). Prior to this section the cursor is
placed in the first cell of the order section via RANGE("A13").Select.
Cursor is advanced via TARGET.OFFSET(r,c)
Range A13:B90 is the input area by user with Column A for item number and
Column B for qty. Currently I have code that works for the entire form.
However, I want to utilize a userform for the actual order entry.

With that in mind, this is what my userform has:
OptionButton1 to indicate Standard item (regular EMS supplies)
OptionButton2 to indicate Non-Standard Item (stapler, office supplies, etc)
Textbox1 = itemnum
Textbox2 = itemqty
CommandButton1 = ENTER (enter info into cell and continue)
CommandButton2 = FINISHED (order completed)

Here are my ideas as to what I want to happen:
1) After item number is entered, check against item list. If invalid,
reenter itemnum
2) Also check itemqty to be sure it is not < 1.
3) Enter info into cells when ENTER clicked and advance cursor
4) If OptionButton2 = TRUE, bypass itemnum entry & validation, enter qty and

description of item ordering.

At this point I believe I can handle the code for everything, except:
1) Returning to prompt for item number if invalid.
2) Looping back to enter next item after ENTER clicked.
3) Knowing what global variables I need and how to declare them.

I also want to know if there is a way to disable the arrow keys or tab key
to force user to hit the ENTER button to advance to next prompt.

Thanks for your help,
Les

 

[Guest]

Les,

OK, I think that for the product code a combo box would be better therefore
the user can select the item from a drop down.

The combo box is defined as a multi column with one column for the code and
one column for the description. You then link the combobox to the spreadsheet.

Therefore you do not have to validate the product code (using the limit to
list flag) only make sure there is an item selected.

When the user clicks the enter key,

you have the following code:

Sub additemtoitems(prodcode As String, prodtype As String, qty As Double)
Dim r As Long
r = 1
Do While (Worksheets("Orders").Range("A" & r) <> "")
r = r + 1
Loop
With Worksheets("Orders").Range("A" & r)
.Offset(0, 0) = prodcode
.Offset(0, 1) = prodtype
.Offset(0, 2) = qty
End With
End Sub

Private Sub CommandButton1_Click()
Dim qty As Long ' assume whole numbers
Dim prodtype As String
Dim prodcode As String
If Me.ComboBox1.Value = "" Then
MsgBox "Please select a product", vbOKOnly, "Error..."
ElseIf Not IsNumeric(textbox2.Value) Then
MsgBox "Please select a product", vbOKOnly, "Error..."
Else
prodcode =
Worksheets("ITEMS").Range("A1").Offset(Me.ComboBox1.Value, 0)
qty = CLng(Me.textbox2)
If Me.optionbutton1.Value = True Then
prodtype = "Std"
Else
prodtype = "Special"
'deal with special not quite sure what different
End If
additemtoitems prodcode, prodtype, qty
Me.optionbutton1.Value = True
Me.optionbutton2.Value = Fal
Me.texbox2.Value = 0
Me.ComboBox1.ListIndex = -1 ' may need 0
End If
Me.Hide
End Sub

' this is for the close button
Private Sub CommandButton2_Click()
me.hide
end sub

' and this is for initializing the form

Private Sub UserForm_Initialize()

ComboBox1.ColumnCount = 2
ComboBox1.RowSource = "Items!A:B"
ComboBox1.BoundColumn = 0

End Sub
'-----------------------
You need to clean it up a bit and change the columns but I believe it should
work.

 

[Guest]

Thanks for your help. I looked over it and understand most of what you have
put in code. I will work with it over the next few days and let you know.

Thanks,
Les