Userform Listbox For User Entry?

I have an application I am trying to help a coworker complete. We use 150 to 200 "bulk item codes" for process paramater specifications.

What she would like to do is to create a userform to pick the 10 to 20 bulk item codes produced for the day and enter the production units.

Say the spreadsheet looks like:

Please Login or Register  to view this content.

she would like to be able to enter the bulk item code (not let her enter an item that is not in the above list) and the corresponding production units into a userform that would look like:

Please Login or Register  to view this content.

I have been trying to read the VBA Help file on using the ListBox without much success.

Is the ListBox the correct tool, and if so, how do I use it in this application?

She wants to be able to (1) type, or pick, the bulk item code and then (2) enter the production units. This would (3) then be entered into the spreadsheet for the corresponding day.

In the original example, replace Prdtcn Units with a date for column B, column C would have the next production date, column D would have the next, and so forth.

She would like to simplify her data entry. Right now she has to find the row for the Bulk Code, scroll to the right and find the next production date and enter the production units for the 10-30 bulk codes produced each day.

One thing I already mentioned to her is that from a database point of view she would be better served having the Bulk Codes in the first row (field names) and using the first column for the dates (records)

We will work on correcting that part once I understand a better way to set up a userform for her data entry. The built-in data form is not large enough to handle the 150 to 200 bulk codes she uses.

I could write code for 20-30 text boxes to enter the bulk code and another 20-30 text boxes for the production units. But I thought maybe the list box was a better choice? I am not so sure at this point.

Let me see if I can give a better explanation.

The worksheet template already exists with the row and column headings (Dates and Bulk Item Codes). For the most part the production data does not exist in the spreadsheet, except for dates already in the past.

The production units for new dates needs to be entered into the spreadsheet. I was hoping to use a UserForm to facilitate the data entry into the spreadsheet for her.

I have attached the spreadsheets in question (in abbreviated form). There are two versions of the spreadsheet, the first tab (PROD_INPUT) is the sheet my co-worker created. The second tab (ALT_PROD_INPUT) is the sheet I created to try to simplify her life. All of the MTD & YTD sub-totals in the new sheet are located at the bottom, instead of inserted after the last day of each month as they were on her sheet. One of the two versions will eventually be a piece of a much larger workbook that she will use.

I created a simple UserForm for her to enter data. It has text boxes for her to enter the production date, the Bulk Item Codes, and the corresponding Production Units for each Bulk Code. There will be several Bulk Items and Production Units each day for several production lines.

My hope was to try to simplify the data entry by using a ListBox or ComboBox for Bulk Item Codes, but I can not figure out how to do it.

My original question was: Is a ListBox the correct tool to facilitate her data entry. The more I discuss the situation, the less I feel a ListBox is appropriate.

If you could also have a look at the notes in the cbtnEnterData_Click event, would you be so kind as to let me know of the most efficient method of doing what I outline.

Yes, my intention is to transfer data from the UserForm to the "ALT_PROD_INPUT" worksheet when the Enter Data button is clicked.

I am looking for the most effective routine to do this transfer. My less-than-skilled approach would be to go through each text box on the userform and, if it has data, look for the collumn that corresponds to the Bulk Item and enter the production units in the row that corresponds to the date on the form.

I think I outlined my plan in note form in the UserForm code module.