Not a bad First try.

Now Johnny, you've been a bad boy, I want you to write, on the blackboard, 100 times,
Function Follows Data Structure
All Names are the Same. Except Prefixes
Forms follow Work Flow


Seriously now:

Function Follows Data Structure
You don't know what to do or how to do it until you have your Data Structured.

Data must be structured in a neat, concise, unduplicated way. The Structure can and should be as identical as possible when one Table (Sheet) is used for Records (Rows of Data) with different statuses than another. See sheets "Outstanding POs" and "Completed POs" in the Archangel attachment in my post above. Get those two arranged and formatted the way you prefer/need and fill in some dummy data on "Outstanding POs" with several statuses. First see next para, this post.

The Sheet "vbaLists" should hold lists of every possible value for the value types shown in the header row. These lists will be used to populate Drop Downs in the Forms, so Users cannot enter wrong values. Also add any columns you think you may want as validations or dropdowns.

All Names are the Same
After you've designed the Data Storage sheets and the Lists sheet, make a list of all the Unique header names from all three sheets.

Using this list make some Range names that are very similar to the sheet header names, except that all spaces are removed or replaced with underscores. You can use common abbreviations and memnomics such as PrtNo for "Part Number" and PrtDesc for "Part Description," "MfgPrtDesc," etc.

Now choose prefixes that represent each sheet name. For example "open," comp," and "list" or "vba_." No Caps Please. Note that all sheet prefixes should have the same number of characters.

Using the Sheet appropriate prefix create defined names for each column on each sheet. Example openPrtNo, vba_PrtNo, and compPrtNo. Note pattern of CapLettersInNames.

UserForms generally consist of Label/input box pairs. The labels usually don't need to be named, but the input box names should be identical to the Range names without the prefix. Input boxes can be drop down ListBoxes or type-in TextBoxes. You can use any vbaLists Validation lists to validate TextBox entries and will use vbaLists dropdown lists to fill the ListBoxes. the ListBox code might look like this, where lbx is a standard prefix for a ListBox name:[vba]lbxPrtNo.RowSource = vbaLists.Range("listPrtNo")[/vba] and the code to record that part number might be[vba]Sheets("Completed POs").Range("compPrtNo").Cells(NextRow) = lbxPrtNo[/vba]
See the pattern? All Names are the Same. Except Prefixes. Make all Form prefixes the same number of characters. Makes coding a breeze. Just by looking at the input box name, you know exactly where to get, and where to put, its' data. If you study the code in the UserForm App attachment I gave you you will find examples of how to load and store the entire form with one loop.

Forms follow Work Flow
If all PO numbers are unique, when updating a record, you can have the Form itself fill in most input boxes by merely finding the row of an existing PO. Make the PO number input box the first one on the form. If its a new PO number, well its not that hard on the user compared to the value an auto fill gives. About a third of the time the only changes the User makes is to update the Status, maybe that box should be next. OR, what is the first item on the (paper?) form the user is looking at to complete the UserForm? Maybe that should be the next input box, etc.

Creating the Form
Looking at all the example available it looks like the only input boxes that cannot be ListBoxes are quantities , dates, and the PO number itself.

To a blank Form Add one Label and one TextBox. Align them, make them appear to be the same height, set their properties. Add no text or names at this time. Use the Ctrl key and the mouse to select both of them. Right Click and drag them to just below where they are and select Copy Here. Repeat until you have a pair for each needed TextBox type entry.

Drag the mouse (left button) over all the labels and Text Boxes, Left grab a cross hair handle and drag them over out of the way.

Repeat the above for the number of ListBox types you need.

Remember that Forms Follow Workflow. Grab a TextBox/Label pair and drag them to the top of the Form, about the center area or exactly where you think they'll go. Type in the Labels text, "P.O. Number", and expand it to the left to show all the text. Name the inputbox "txbPONum" or whatever, and set its TabIndexNumber to 0. When the Form starts, TabIndex 0 will be selected for input.

What is the next input in the workflow? Grab that pair, move them as desired, name the box and set its TabIndex to 1. When the User presses Tab or Enter while in the tbxPONum box (TabIndex 0), the box with TabIndex = 1 will be selected for input.
Repeat for all Label/Input Box pairs. If you rearrange the input boxes, select them by Work flow and renumber their TabIndexes.

Coding the Form
You will need to refer to the App attachment I posted in a previous post.

Copy all the declarations, (What is above the first sub,) in the Form code in the App to your Form code.

Right click on the Form and select View Code. At the top of the Code Pane. in the box labeld "General,"and in reverse Work Flow, select a lbx* control. VBA will automatically insert a Click Event Sub for that control. In the right hand box at the top of the code pane, select "enter" and it will insert an Enter event sub for the control. Delete the Click event sub and select the reverse Work Flow's next listbox, and repeat. When done you should have a series of ListBox Enter subs in WorkFlow order.

Click on UserForm in the "General" box and insert the Initialize sub. Paste
"InitializeControls
LoadLocalCollections" in it.

Find an Enter sub in the App example and paste that code line into all your Enter subs.

Paste this in[vba]Private Function LoadLocalCollections()
'Collections used for various Form activities

Dim Ctrl As Object
For Each Ctrl In Me.Controls
If TypeName(Ctrl) = "ListBox" Then ListBoxes.Add Ctrl
If TypeName(Ctrl) = "TextBox" Then TextBoxes.Add Ctrl
IntakeBoxes.Add Ctrl
Next Ctrl
End Function[/vba]
There are many more subs and functions in the App that you can paste into your code. Be sure to look at the workbook and worksheets code too.

See ya soon.