RE: Looping with Userform

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 ***. As the order is entered via item numbers & qty, VBA code
will verify the item number against the list on the ITEMS ***. 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

"Martin Fishlock" wrote:

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
--
Hope this helps
Martin Fishlock


"WLMPilot" wrote:

I am new to userforms. I have developed a spread*** 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
.